I have created a prepared statement to output a file and add a date to the filename. That part works if I select the entire database. I want to select specific columns to include in the query, not all of them.
I'm a newbie to SQL syntax, so please be aware of that when answering. :)
Here is what I started with before the date-in-filename was a requirement:
USE dbx; SELECT custtype.custtype, custtype.custtypedesc, USER.jobtitle, USER.branch, log.custid, log.username, log.actualuser, log.timestamp, log.action, log.data INTO outfile '/inetpub/wwwroot/reports/Activity_Log.csv' fields terminated BY ',' optionally enclosed BY '"' lines terminated BY '\n' FROM log LEFT OUTER JOIN USER ON log.custid = USER.custid AND log.username = USER.username LEFT OUTER JOIN custtype ON custtype.custtype = USER.custtype WHERE USER.email NOT LIKE " % @sample.com" AND USER.email NOT LIKE " % @google.com" AND log.username NOT LIKE "Guest % "
You can see that I am selecting individual columns as per my requirement. That works well. However, I can't figure out how to select individual columns like this in the next query...
Here is what I have now after the date requirement was added:
SET @TS = DATE_FORMAT(NOW(), '_%m_%d_%Y'); SET @FOLDER = '/inetpub/wwwroot/reports/'; SET @PREFIX = 'Activity_Report_'; SET @EXT = '.csv'; USE webcat_336; SET @CMD = concat(" SELECT custtype.CustType, custtype.CustTypeDesc FROM log INTO OUTFILE", @FOLDER , @TS, @EXT); "' FIELDS ENCLOSED BY '\"' TERMINATED BY '; ' ESCAPED BY '\"'", " LINES TERMINATED BY '\r\n'; "
If I replace "custtype.CustType, custtype.CustTypeDesc" in the 2nd example with *, the file generates properly and it's named "Activity_Log_m_d_Y.csv" as expected.
I can now select all the columns I originally intended with the following code. However, the WHERE clause breaks it.
USE db_name; SELECT @cmd : = concat( " SELECT custtype.CustType , custtype.CustTypeDesc , user.JobTitle , user.Branch , log.CustID , log.Username , log.ActualUser , log.Timestamp , log.Action , log.Data into OUTFILE '/inetpub/wwwroot/reports/Activity_Report_", DATE_FORMAT(now(), '%m_%d_%Y'), ".csv' FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' FROM log LEFT OUTER JOIN user ON log.CustID = user.CustID AND log.Username = user.Username LEFT OUTER JOIN custtype ON custtype.CustType = user.CustType WHERE USER.email NOT LIKE " % @sample.com" AND USER.email NOT LIKE " % @google.com" AND log.username NOT LIKE "Guest % " "); PREPARE stmt FROM @cmd; EXECUTE stmt;
How can I select only specific columns as opposed to all columns in the example above?