Help with "INTO OUTFILE"

eliasthecrimson

New Member
Prime Account
Messages
8
Reaction score
0
Points
0
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:

Code:
/*
** 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! :)
 
Last edited:

descalzo

Grim Squeaker
Community Support
Messages
9,373
Reaction score
326
Points
83
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

New Member
Prime Account
Messages
8
Reaction score
0
Points
0
Doing this on the MySQL server on x10hosting (free). My MYSQL database server is "localhost", which is why I assumed this would work.
 

descalzo

Grim Squeaker
Community Support
Messages
9,373
Reaction score
326
Points
83
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

New Member
Prime Account
Messages
8
Reaction score
0
Points
0
Alright, thanks. Here is the code as it looks now:

Code:
/*
** 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:

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

misson

Community Paragon
Community Support
Messages
2,572
Reaction score
72
Points
48
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.
 
Last edited:
Top