Help with "INTO OUTFILE"

[eliasthecrimson]

I'm trying write a code snippet that saves a specific table in my database as a CSV file. The purpose of this is to automatically back up that specific table just before changes are made do it (that I can access through the File Manager and use to restore if needed). Some googling led me to using the INTO OUTFILE statement.

This is what I'm trying to do:

/*
** Write tables to CSV file
*/
$backup_query = "SELECT * FROM stock INTO OUTFILE 'stock_$today.csv' FIELDS TERMINATED BY ';' ENCLOSED BY '\"' LINES TERMINATED BY '\n'";
$backup = mysql_query($backup_query, $db);
if($backup)
    echo "Backup OK";
else 
    echo "Backup failed";

The backup always fails and no file is written. I might be doing this completely wrong; any help is appreciated!

 

[descalzo]

Are you doing this on your computer or on x10hosting?

The command runs on the MySQL server, which may or may not be the server where you account is located.

Even then, you have to give the full path to the file, ie '/home/igor/public_html/backups/stock-table-dumpMay182011.txt'

 

[eliasthecrimson]

Doing this on the MySQL server on x10hosting (free). My MYSQL database server is "localhost", which is why I assumed this would work.

 

[descalzo]

Not sure if they are keeping the MySQL server on or off the machine for your accounts.

1. You would still need the full path to the backup file, not just a relative path.
2. Use mysql_error to test for error messages.

 

[eliasthecrimson]

Alright, thanks. Here is the code as it looks now:

/*
** Write tables to CSV file
*/
$backup_query = "SELECT * FROM stock INTO OUTFILE '/public_html/restricted/merchants/backup/stock_$today.csv' FIELDS TERMINATED BY ';' ENCLOSED BY '\"' LINES TERMINATED BY '\n'";
$backup = mysql_query($backup_query, $db) or die('Error: ' . mysql_error());

... and here's the error it gives when run:

Error: Access denied for user '*********'@'10.33.248.%' (using password: YES)

 

[misson]

The INTO OUTFILE clause requires the FILE privilege, which can't be granted to any user you create through cPanel since it can only be given globally.