Search code examples
mysqlphpmyadmindate-formatdatabase-backupsselect-into-outfile

Automated database backup using PHP event

I'm assigned with a task of creating an automated backup for a database. I used a php event and I want to save my all my backups by a unique name. So I used a date_format and following is my code.

SELECT * FROM redcap_data ;
OUTFILE ' ", DATE_FORMAT(now(),'%Y%m%d_%H%i'),".csv';
FIELDS TERMINATED BY ',' ;
OPTIONALLY ENCLOSED BY '"';
LINES TERMINATED BY "\n";

But it gives an error saying that there is an error in your SQL syntax; near 'OUTFILE ', DATE_FORMAT(now(),'%Y%m%d_%H%i'), .csv''

please help me to find where my error is.

Solution

  • Try as below

        SET @sql_text = 
   CONCAT (
       "SELECT * FROM `tbl_user` into outfile '/xampp/htdocs/mysite/reports-"
       , DATE_FORMAT( NOW(), '%Y%m%d')
       , ".csv'"
    );

PREPARE s1 FROM @sql_text;
EXECUTE s1;
DROP PREPARE s1;

    Refer from Rename outfile with date in mysql