I searched all over for this information, and never found it in a easy-to-use format:
Here’s the situation. You have php access to a MySQL –or other SQL variant– server. You don’t have phpMyAdmin, you don’t have command line access. You would like to export some of the data, but not necessarily all of it, because perhaps the full database is too large.
The SQL command that makes this simple is: “SHOW CREATE TABLE table_name”
Use that as your query, and the output will be the complete SQL command for creating a table. The only other thing you need is to turn the data output into insert statements, which is trivial.
Here’s a complete script –notice how short it is:
mysql_connect($sql_host, $sql_user, $sql_password) or exit(mysql_error());
mysql_select_db($sql_db) or exit(mysql_error());
$result=mysql_query("SHOW TABLES");
echo mysql_error();
while ($row=mysql_fetch_array($result)){
$tableName=$row[0];
$query2="SHOW CREATE TABLE $tableName";
$result2 = mysql_query($query2);
$row2=mysql_fetch_array($result2);
echo "$row2[1];\n";
$query3 = "SELECT * FROM $tableName";
$result3 = mysql_query($query3);
while ($row3=mysql_fetch_array($result3)){
$header_array=array();
$line_array=array();
foreach ($row3 as $key=>$value){
if (!is_numeric($key)){
$header_array[]="$key";
$line_array[]="'$value'";
}
}
echo "INSERT INTO $tableName (".implode(", ", $header_array).") VALUES (".implode(", ",$line_array).");\n";
}
}
}
mysql_close();