mySql : Select into file

[fomalhaut]

Hello

I want to make a copy of a table into a file in order to make a backup.

$db  = db_name;
$dbh = new PDO("mysql:host=localhost;dbname=$db", 'user', 'pw');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$TB = db_name.table_name;
$backupFile = '../Backup/' . $TB . '.sql';
$query = "SELECT * FROM ". $TB . " INTO OUTFILE '".$backupFile."' ";
$result = mysql_query($query) or die ("pb sur le ".$query); 

and that does not work ! It says :

pb sur le SELECT * FROM db_name.table_name INTO OUTFILE '../Backup/db_name.table_name.sql'

I don't understand what happens ! And the file is not created.

Thank you for help

The directory ../Backup exists before.

[as4s1n]

$TB = db_name.table_name;
$backupFile = '../Backup/' . $TB . '.sql';
$query = "SELECT * FROM ". $TB . " INTO OUTFILE '".$backupFile."' ";
$result = mysql_query($query) or die ("pb sur le ".$query); 

The directory ../Backup exists before.

It may be out of order:

$string = '';
$tableName  = 'tableName';
$backupFile = 'backupFile';
try
    $query =$dbh->query("SELECT * INTO OUTFILE '$backupFile' FROM $tableName");
catch(PDOException $e) 
    print("Error: ".$e->getMessage()."\n"); 

Also try reading Using PHP to Backup MySQL Databases. Although, I am not sure of the format in which the information is shown on the page.

Or you could use the fwrite() function to create a new file if it does not exist or empty and write to it if it does exist.

$errors = array();
$tableName  = 'tableName';
$backupFile = 'backupFile';
$query =$dbh->query("SELECT * FROM tableName");
if($query->columncount() === 0)
   $errors[] = "No rows in table";
if(!$query)
    $errors[] = "Query Error: ".mysql_error();
if(sizeof($errors) == 0) {
  while($row=$query->fetch()) {
   $column1 = $row['0'];
   $column2 = $row['1'];
   $column3 = $row['2'];

// Etc.

// Format any way you wish
$string .= "$column1 $column2 $column3"; // Etc.
}

$fp = fopen("$backupFile","w"); // the W parameter truncates the file, use A if you want to apply it to the end of the file which DOES NOT truncate the file.
fwrite($fp,$string);
fclose($fp);
} else {
 echo "<ul><li>";
  implode("</li><li>",$errors);
 echo $errors."</li>";
echo "</ul>";
} 

Also if you use PDO, instead of the original:

$query = "SELECT fields FROM tableName";
$result = mysql_query($query); 

You can use the PDO version:

$query = $dbh->prepare("SELECT fields FROM tableName");
$query->execute(); 

Refer to Writing PDO in PHP & MySQL

[zenadix]

Your error message is in french. Why?

[lemon-tree]

Dumping of SQL databases is not available on X10 hosting judging from the tests I have tried. I would suggest PHPMyBackupPro as a better alternative that does essentially the same thing.

[as4s1n]

Dumping really wasn't my intent, that was just a bonus for formalhaut.

@formalhaut: BTW is this like a scheduled backup thing or just an occasional backup you want just in case?

[fomalhaut]

Hello.

Your error message is in french. Why?

Of course, there are, I'm french !

BTW is this like a scheduled backup thing or just an occasional backup you want just in case?

You're right, that's just an occasional backup, because, the tables will not move a lot.
Please, what the exact meaning of "BTW" ?

In facts:
Having changed some things, I've now a more precise message:

Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1086 File '../Backup/db_name.table_name.sql' already exists'

And alas, I don't find it in any directory !!!!

I'm searching !

[as4s1n]

'BTW' means 'By The Way'.

If it says it already exists, I'm not quite sure what to tell you, you looked through the folder? You could always try the fwrite method I suggested, which writes to a file whether or not it exists, which would probably mean the server thinks it has that but doesn't. I have noticed before that the cPanel file manager does not like certain names, although that may be just me. try 'db_tableName.sql', It's worth a shot.

One more question: Are you going to input the file directly into the database (import) or copy the data in it to the table?

[descalzo]

And alas, I don't find it in any directory !!!!

I'm searching !

Try using absolute paths. Not sure if the mySQL server treats '../Backup/db_name.table_name.sql' as relative to the server rather than to the script.

[fomalhaut]

OK, I've found !! Select Into file writes in a directory relative to the mysql directory. And that doesn't arrange me because this is not the same place on my test server than on the x10 server.

So I've tried your "fwrite method", as4s1n. And it works, putting the file where I expect it.

although... I've an other pb that I'm searching for: each column is written twice in my file !

I've no time now, but if I don't found, I will explain you. Probably an idioty of my small mind !

Thanks again for having help me.

[as4s1n]

Is the duplicate columns result from the fwrite method or the OUTPUT clause in the query?