PHP OUTFILE with datestamp

[SalientAnimal]

Hi All,

 

This is part of a PHP script that I am wanting to use to automate some daily data exports. The problem I am having, is adding a datestamp to my export file. The script itself works when executed in MySQL but not under PHP.  Does anyone have any insight into how I can get this working?

The error message I am currently getting is, and the part of php script I am running can be seen below.

 

 

Parse error: syntax error, unexpected '", DATE_FORMAT( NOW(), '%Y%m%d' (T_CONSTANT_ENCAPSED_STRING) in

 

 

$sql1 = "SET @exportfile = 
   CONCAT(SELECT
'FIELD_1'
, 'FIELD_2'
, ....
UNION ALL
SELECT * FROM mydatabasetable INTO OUTFILE 'E:/Data/exports/DailyExport/myexportfile" 
, DATE_FORMAT( NOW(), '%Y%m%d')
, ".csv'
FIELDS TERMINATED BY ','        
        ENCLOSED BY ''''
        LINES TERMINATED BY '\r\n'
    );";


$sql2 = "PREPARE processing FROM @exportfile;";
$sql3 = "EXECUTE processing;";
$sql4 = "DROP PREPARE processing;";

 

Any help here is greatly appreciated.

Edited September 14, 2016 by SalientAnimal

[Barand]

You have a a single quote after OUTFILE which does not appear to have a corresponding closing quote

Edited September 14, 2016 by Barand

[SalientAnimal]

You have a a single quote after OUTFILE which does not appear to have a corresponding closing quote

 

The single quote you are referring to has a corresponding single quote after the .csv

 

Although, this does work when run directly on MySQL Workbench, I'm not sure what needs to change for plugging it into PHP.

 

 

This is what the working script in MySQL Workbench looks like:

SET @exportfile = 
   CONCAT("SELECT  
'FILED_1'
,  'FIELD_2'
        ,  ....
UNION ALL
SELECT * FROM mydatabasetable INTO OUTFILE 'E:/Data/exports/DailyExport/myoutputfile" 
, DATE_FORMAT( NOW(), '%Y%m%d')
, ".csv'
FIELDS TERMINATED BY ','        
        ENCLOSED BY ''''
        LINES TERMINATED BY '\r\n'"
    );


PREPARE processing FROM @exportfile;
EXECUTE processing;
DROP PREPARE processing;

Edited September 14, 2016 by SalientAnimal

[Jacques1]

The error message says your syntax is screwed up. Now look at the syntax highlighting in your post. Hint: Your double quotes are all over the place.

 

The whole approach is odd. Why on earth do you create a prepared statement with no parameters, execute it once and then immediately drop it?

[SalientAnimal]

I can't say I am experienced at all in doing out files when it comes to PHP. Doing it under MySQL Workbench is a lot easier, so my question is really the principle of applying it. My understanding in PHP is that the actual query needs to be within Double quotation marks, however this becomes a challenge when taking the existing file / script directly from MySQL Workbench and trying to plug it into PHP. I'm unsure of how to apply this.

That said, I'm going to attempt a change based on what you have said, let me see what the outcome is.

Edited September 14, 2016 by SalientAnimal

[Jacques1]

Why on earth do you create a prepared statement with no parameters, execute it once and then immediately drop it?

[SalientAnimal]

The prepare statement is to compile the CSV file with a datestamp. I used it this way in MySQL Workbench to compile the export file with the datestamp. From my understanding on the research I did, this was the only way to add a datestamp to a file in MySQL Workbench. 

[Jacques1]

$database_connection->exec("

    SELECT

      'FIELD_1'

      -- ...

    UNION ALL

    SELECT

      *

    FROM

      mydatabasetable

    INTO

      OUTFILE 'E:/Data/exports/DailyExport/myoutputfile".date('Ymd').".csv'

    FIELDS

      TERMINATED BY ',' ENCLOSED BY ''''

    LINES

      TERMINATED BY '\r\n'

");

[SalientAnimal]

Thanks Jacques1, really appreciate the help. And it eliminates having to use the prepared statements and then dropping them after one use.