Export (backup) Mysql table

Hi there !

I searched many different places but did not find my answer. Btw, the search engine on CF.com returned an error when I tried today... so let's post my question.

I have a mysql table (not the whole db) called PLAYERS that I want to backup into a .csv or .sql or anything else (I wish I can add this to my webcron page). This file should be exported to my /web folder using a filename like 2007-01-13.csv so it will be easier to recover and should not replace the previous one (naming the file is not my problem). I should be able then to reload the content of this file into my PLAYERS table at anytime. Of course I want to do all that using a web PHP interface and not use phpmyadmin anymore.

I have tried the SELECT INTO OUTFILE method with no luck. There's obviously something I didn't understand or did wrong.

Can anybody give me a working example or a link to a good and easy tut?

And could someone tell me what would be the best or correct file format I should use (csv / sql / txt ...) to achieve what I want to?

Important, there should be no 'human' input in this operation... no 'save as' popup or anything! Automated.

Thanks in advance!

Ok... I finally managed to save my .sql file where I want.
I end up with a well formatted file that I can import with phpMyAdmin.

Now how can I import this file in my table using PHP? What do u recommend?

Thanks

I am trying to use this:

<?
include("../_conn/config.php");
mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name);

$path = $_SERVER['DOCUMENT_ROOT']."/admin/backup/sql/2008-01-13.txt";
echo $path;
$file = fopen($path,"w");
load_backup_sql($file);
fclose($file);

function load_backup_sql($file) {

while (!feof($file)) {
$query = NULL;
while (!feof($file)) {
$query .= fgets($file);
}
if (NULL != $query) {
mysql_query($query) or die("sql not successful: ".mysql_error()." query: ".$query);
}
}
}
?>

I get no error but my table is not filled by the records of the txt file...
Any idea ?

I still didn't find the good way for this...

Anybody has a working example on how to:

Import a mysql dump file (.sql or .txt) in a mysql table?

My .sql files contain everything needed (INSERT INTO TBL_NAME ... VALUES ...) etc.

Anybody?
Thanks in advance!

<?
include("../_conn/config.php");
mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name);

$path = $_SERVER['DOCUMENT_ROOT']."/admin/backup/sql/2008-01-13.txt";
echo $path;
$file = fopen($path,"w");
load_backup_sql($file);
fclose($file);

function load_backup_sql($file) {

while (!feof($file)) {
$query = NULL;
while (!feof($file)) {
$query .= fgets($file);
}
if (NULL != $query) {
mysql_query($query) or die("sql not successful: ".mysql_error()." query: ".$query);
}
}
}
?>

I get no error but my table is not filled by the records of the txt file...
Any idea ?

opening a file in w mode truncates the file...so, essentially what ur script has been doing is truncating the backup file that u created...since ur not executing any invalid query (or any query for that matter $query is always NULL) you don't get an error either :|

opening a file in w mode truncates the file...so, essentially what ur script has been doing is truncating the backup file that u created...since ur not executing any invalid query (or any query for that matter $query is always NULL) you don't get an error either :|

Aha.. That's what I thought too... do you mean I shall open the file using "r" parameter? I will try that, but I think I tried and it did not anything better. I will report here...

Thanks

OK... looks much better using the "r" parameter...

Now I end up with another error.

When exporting a large table with phpMyAdmin, I noticed that the following line:

INSERT INTO `TABLE_NAME` (`ID_TABLE_NAME`, `USERID`, `TEAMID`, `PLAYERID`, `LINE`) VALUES (...), etc

is repeated every 1800 lines. Knowing that, I built my script so that it would also repeat this line every 1800 lines but it looks like it is generating an error when I import.

sql not successful: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; INSERT INTO `TABLE_NAME` (`ID_TABLE_NAME`, `USERID`, `TEAMID`,' at line 1800

Any idea, why these mutliple INSERT INTO are generated by phpMyAdmin every 1800 lines? (MySql 4.1.20)
Is there a real need to repeat this line ?
Note that if I import that same file using phpMyAdmin, it works fine:

Import has been successfully finished, 3 queries executed.

It says 3 queries, because this INSERT INTO line appears 3 times in my file...

Any advice on the above questions or help on how to correctly create a large mysql dump file would be much appreciated !

Kindly note that removing these 2 additional INSERT INTO lines in my .txt file fixes the problems and the file gets imported correctly!
Thanks!