PHP/MySQL - a command to write an SQL database dump file?

[MTKilpatrick]

I'm really confused by the various backup methods for MySQL. I'm only interested in having a manually generated backup in the form of an SQL file containing a series of "DROP TABLE...CREATE TABLE...INSERT" statements, which I can reload if necessary. I can do this from within phpMyAdmin, but I'd really like to know how to do this myself from within my Apache/PHP-based website code on my home PC so that I can create the text file at the push of a button. I'm writing some PHP code to manage some data via a web interface. I can't seem to find in the docs exactly how to do this except from the MySQL server command line using mysqldump.

But how do I do this from within a PHP program, please?

Michael

[RussellC]

You can run the mysqldump command in PHP via the system() function.

[hakre]

checkout the sources of phpmyadmin, it's gpl and freely available. one part of phpmyadmin is creating such kind of data, myabe by using mysqldump for this - i don't exactly know. but in this code you will find much more information.

[frobo]

How did you go?

did you come up with a good solution?

[jatar_k]

Welcome to WebmasterWorld frobo

[frobo]

Thanks,

I found a good one:

function get_structure()
{
$server = $DBADDRESS;
$user = $DBUSERNAME;
$pass = $DBPASSWORD;
$db = $DB;

mysql_connect($server, $user, $pass);
mysql_select_db($db);
$tables = mysql_list_tables($db);
while ($td = mysql_fetch_array($tables))
{
$table = $td[0];
$r = mysql_query("SHOW CREATE TABLE `$table`");
if ($r)
{
$insert_sql = "";
$d = mysql_fetch_array($r);
$d[1] .= ";";
$SQL[] = str_replace("\n", "", $d[1]);
$table_query = mysql_query("SELECT * FROM `$table`");
$num_fields = mysql_num_fields($table_query);
while ($fetch_row = mysql_fetch_array($table_query))
{
$insert_sql .= "INSERT INTO $table VALUES(";
for ($n=1;$n<=$num_fields;$n++)
{
$m = $n - 1;
$insert_sql .= "'".mysql_real_escape_string($fetch_row[$m])."', ";
}
$insert_sql = substr($insert_sql,0,-2);
$insert_sql .= ");\n";
}
if ($insert_sql!= "")
{
$SQL[] = $insert_sql;
}
}
}
return implode("\r", $SQL);
}