well, i don't think your host lets you run backups through queries.
I worked on a database related project before, http://nhassan.net/dd . Feel free to check out the source code.
I took out pieces of the code in that project, and put them together, so its easier for you. what the script does is basically connects to the mysql server, and then the function backup_create() generates the CREATE query for the table , and backup_insert() generates the INSERT queries for the table. and then at the end, i execute the functions, and use the table 'blog'
<?php
mysql_connect('localhost', 'root', '');
mysql_select_db('blog');
function backup_create($table) {
$sql_backup .= '--
-- Table structure for ' . $table . '
--
';
$create = 'CREATE TABLE `' . $table . '` (';
// The following is from SimpleMachines Forum Software, with a tweaks to work, with me
$sql = mysql_query('SHOW FIELDS FROM `' . $table . '` ;');
while ($row = mysql_fetch_assoc($sql)) {
$create .= '`' . $row['Field'] . '` ' . $row['Type'];
$create .= ($row['Null'] != 'YES' ? ' NOT NULL ' : '');
if($row['Default'] != 'CURRENT_TIMESTAMP') {
$create .= ($row['Default'] == '' ? '' : ' default \'' . $row['Default'] . '\' ');
}
$create .= ($row['Extra'] == '' ? '' : $row['Extra']);
$create .= ',';
}
// Take away the last ',' since, well, that's the rules
$create = substr($create, 0, - 1);
// Find the keys of the table
$result = mysql_query("SHOW KEYS FROM `" . $table . "`");
$indexes = array();
while ($row = mysql_fetch_assoc($result))
{
// IS this a primary key, unique index, or regular index?
$row['Key_name'] = $row['Key_name'] == 'PRIMARY' ? 'PRIMARY KEY' : (empty($row['Non_unique']) ? 'UNIQUE ' : ($row['Comment'] == 'FULLTEXT' || (isset($row['Index_type']) && $row['Index_type'] == 'FULLTEXT') ? 'FULLTEXT ' : 'KEY ')) . $row['Key_name'];
// Is this the first column in the index?
if (empty($indexes[$row['Key_name']]))
$indexes[$row['Key_name']] = array();
// A sub part, like only indexing 15 characters of a varchar.
if (!empty($row['Sub_part']))
$indexes[$row['Key_name']][$row['Seq_in_index']] = '`' . $row['Column_name'] . '`(' . $row['Sub_part'] . ')';
else
$indexes[$row['Key_name']][$row['Seq_in_index']] = $row['Column_name'];
}
// Build the CREATEs for the keys.
foreach ($indexes as $keyname => $columns)
{
// Ensure the columns are in proper order.
ksort($columns);
$create .= ', ' . $keyname . ' (' . implode($columns, ', ') . ')';
}
// Get the COMMENTS, and TABLE type
$row = mysql_fetch_assoc(mysql_query("SHOW TABLE STATUS LIKE '" . $table . "';"));
// TABLE Type
$create .= ') TYPE=' . (isset($row['Type']) ? $row['Type'] : $row['Engine']);
// TABLE Comment
$create .= ($row['Comment'] != '' ? ' COMMENT="' . $row['Comment'] . '"' : '');
// Any AUTO INCREMENT that might be reguired
$create .= ($row['Auto_increment'] != '' ? ' AUTO_INCREMENT=' . $row['Auto_increment'] : '');
// Close the query with ';'
$create .= ';';
// Add in the CREATE TABLE syntax into the whole SQL backup
return $create . "\n";
}
function backup_insert($table) {
$sql_backup .= '--
-- Data in ' . $table . '
--
';
// Select all rows in the table
$sql = mysql_query("SELECT * FROM `" . $table . "`;");
// Figure out the number of fields
$num_field = mysql_num_fields($sql);
// Figure out all the field names
for ($i = 0; $i <= $num_field-1; $i++) {
$field[$i] = mysql_field_name($sql, $i);
}
// Number of rows
$sql_backup .= "-- Number of Rows: " . mysql_num_rows($sql) . "\n";
// Layout the tables
while ($row = mysql_fetch_assoc($sql))
{
// Construct INSERT query
$var = "INSERT INTO `" . $table . "` ( ) VALUES ( );";
// Foreach field there is ..
foreach($field as $id => $data) {
// Add in the field name
$var = str_replace(") VALUES (", "`" . $data . "` , ) VALUES (", $var);
// Check what type of data it is
/////////////////////////////////
// If its empty, then NULL
if(!isset($row[$data])) {
$row[$data] = 'NULL';
// If its a number coded, then just add in the ''
} elseif (is_numeric($row[$data])) {
$row[$data] = "'" . $row[$data] . "'";
// If its data, then escape it of all
// things that might cause errors later on
} else {
$row[$data] = "'" . mysql_escape_string($row[$data]) . "'";
}
// And, now, add in the data
$var = str_replace(" );", " " . $row[$data] . ", );", $var);
}
// Remove the last ',' on both the field part, and
// the data part
$var = str_replace(", ) VALUES (", ") VALUES (", $var);
$var = str_replace(", );", " );", $var);
// Now add in the sql_backup data
$sql_backup .= $var . "\n";
}
return $sql_backup;
}
echo backup_create('blog');
echo backup_insert('blog');
?>
