I wanted a piece of code to dump / backup my sql tables.
I has a look and modified it very slightly.
Couple of things. SQL mode dumps the entire database. But CSV mode only does named tables and you forget to put in the table name, so I added a simple check and warning.
<?php
set_time_limit(0);
/*---------------------------------------------------
| mysqldump.php
----------------------------------------------------
| Copyright 2006 Huang Kai
| hkai@atutility.com
| http://atutility.com/
----------------------------------------------------
| Released under the terms & conditions of v2 of the
| GNU General Public License. For details refer to
| the included gpl.txt file or visit http://gnu.org
----------------------------------------------------*/
/*
change log:
2006-10-16 Huang Kai
---------------------------------
initial release
2006-10-18 Huang Kai
---------------------------------
fixed bugs with delimiter
add paramter header to add field name as CSV file header.
2006-11-11 Huang Kia
Tested with IE and fixed the <button> to <input>
2013-04-29 ReetP
Small mods to reporting and check for CSV Table
*/
$mysqldump_version="1.02";
$print_form=1;
$output_messages=array();
//test mysql connection
if( isset($_REQUEST['action']) )
{
$mysql_host=$_REQUEST['mysql_host'];
$mysql_database=$_REQUEST['mysql_database'];
$mysql_username=$_REQUEST['mysql_username'];
$mysql_password=$_REQUEST['mysql_password'];
if( 'Test Connection' == $_REQUEST['action'])
{
_mysql_test($mysql_host,$mysql_database, $mysql_username, $mysql_password);
// echo $mysql_host,$mysql_database, $mysql_username, $mysql_password. '<br />';
}
else if( 'Export' == $_REQUEST['action'])
{
_mysql_test($mysql_host,$mysql_database, $mysql_username, $mysql_password);
if( 'SQL' == $_REQUEST['output_format'] )
{
$print_form=0;
//ob_start("ob_gzhandler");
header('Content-type: text/plain');
header('Content-Disposition: attachment; filename="'.$mysql_host."_".$mysql_database."_".date('YmdHis').'.sql"');
echo "/*mysqldump.php version $mysqldump_version */\n";
_mysqldump($mysql_database);
//header("Content-Length: ".ob_get_length());
//ob_end_flush();
}
else if( 'CSV' == $_REQUEST['output_format'] && isset($_REQUEST['mysql_table']) && ($_REQUEST['mysql_table'] != null))
{
$print_form=0;
ob_start("ob_gzhandler");
header('Content-type: text/comma-separated-values');
header('Content-Disposition: attachment; filename="'.$mysql_host."_".$mysql_database."_".$mysql_table."_".date('YmdHis').'.csv"');
//header('Content-type: text/plain');
_mysqldump_csv($_REQUEST['mysql_table']);
header("Content-Length: ".ob_get_length());
ob_end_flush();
}
else echo "<H3>CSV Mode - No Table entered !</H3>";
}
}
function _mysqldump_csv($table)
{
$delimiter= ",";
if( isset($_REQUEST['csv_delimiter']))
$delimiter= $_REQUEST['csv_delimiter'];
if( 'Tab' == $delimiter)
$delimiter="\t";
$sql="select * from `$table`;";
$result=mysql_query($sql);
if( $result)
{
$num_rows= mysql_num_rows($result);
$num_fields= mysql_num_fields($result);
$i=0;
while( $i < $num_fields)
{
$meta= mysql_fetch_field($result, $i);
echo($meta->name);
if( $i < $num_fields-1)
echo "$delimiter";
$i ;
}
echo "\n";
if( $num_rows > 0)
{
while( $row= mysql_fetch_row($result))
{
for( $i=0; $i < $num_fields; $i )
{
echo mysql_real_escape_string($row[$i]);
if( $i < $num_fields-1)
echo "$delimiter";
}
echo "\n";
}
}
}
mysql_free_result($result);
}
function _mysqldump($mysql_database)
{
$sql="show tables;";
$result= mysql_query($sql);
if( $result)
{
while( $row= mysql_fetch_row($result))
{
_mysqldump_table_structure($row[0]);
if( isset($_REQUEST['sql_table_data']))
{
_mysqldump_table_data($row[0]);
}
}
}
else
{
echo "/* no tables in $mysql_database */\n";
}
mysql_free_result($result);
}
function _mysqldump_table_structure($table)
{
echo "/* Table structure for table `$table` */\n";
if( isset($_REQUEST['sql_drop_table']))
{
echo "DROP TABLE IF EXISTS `$table`;\n\n";
}
if( isset($_REQUEST['sql_create_table']))
{
$sql="show create table `$table`; ";
$result=mysql_query($sql);
if( $result)
{
if($row= mysql_fetch_assoc($result))
{
echo $row['Create Table'].";\n\n";
}
}
mysql_free_result($result);
}
}
function _mysqldump_table_data($table)
{
$sql="select * from `$table`;";
$result=mysql_query($sql);
if( $result)
{
$num_rows= mysql_num_rows($result);
$num_fields= mysql_num_fields($result);
if( $num_rows > 0)
{
echo "/* dumping data for table `$table` */\n";
$field_type=array();
$i=0;
while( $i < $num_fields)
{
$meta= mysql_fetch_field($result, $i);
array_push($field_type, $meta->type);
$i ;
}
//print_r( $field_type);
echo "insert into `$table` values\n";
$index=0;
while( $row= mysql_fetch_row($result))
{
echo "(";
for( $i=0; $i < $num_fields; $i )
{
if( is_null( $row[$i]))
echo "null";
else
{
switch( $field_type[$i])
{
case 'int':
echo $row[$i];
break;
case 'string':
case 'blob' :
default:
echo "'".mysql_real_escape_string($row[$i])."'";
}
}
if( $i < $num_fields-1)
echo ",";
}
echo ")";
if( $index < $num_rows-1)
echo ",";
else
echo ";";
echo "\n";
$index ;
}
}
}
mysql_free_result($result);
echo "\n";
}
function _mysql_test($mysql_host,$mysql_database, $mysql_username, $mysql_password)
{
global $output_messages;
$link = mysql_connect($mysql_host, $mysql_username, $mysql_password);
if (!$link)
{
array_push($output_messages, 'Could not connect: ' . mysql_error());
}
else
{
array_push ($output_messages,"Connected with MySQL server: $mysql_username@$mysql_host successfully <br />");
$db_selected = mysql_select_db($mysql_database, $link);
if (!$db_selected)
{
array_push ($output_messages,'Can\'t use $mysql_database : ' . mysql_error());
}
else
array_push ($output_messages,"Connected with MySQL database: $mysql_database successfully <br />");
}
}
if( $print_form >0 )
{
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>mysqldump.php version <?php echo $mysqldump_version; ?></title>
</head>
<body>
<?php
foreach ($output_messages as $message)
{
echo $message."<br />";
}
?>
<form action="" method="post">
MySQL connection parameters:
<table border="0">
<tr>
<td>Host:</td>
<td><input name="mysql_host" value="<?php if(isset($_REQUEST['mysql_host']))echo $_REQUEST['mysql_host']; else echo 'localhost';?>" /></td>
</tr>
<tr>
<td>Database:</td>
<td><input name="mysql_database" value="<?php echo $_REQUEST['mysql_database']; ?>" /></td>
</tr>
<tr>
<td>Username:</td>
<td><input name="mysql_username" value="<?php echo $_REQUEST['mysql_username']; ?>" /></td>
</tr>
<tr>
<td>Password:</td>
<td><input type="password" name="mysql_password" value="<?php echo $_REQUEST['mysql_password']; ?>" /></td>
</tr>
<tr>
<td>Output format: </td>
<td>
<select name="output_format" >
<option value="SQL" <?php if( isset($_REQUEST['output_format']) && 'SQL' == $_REQUEST['output_format']) echo "selected";?> >SQL</option>
<option value="CSV" <?php if( isset($_REQUEST['output_format']) && 'CSV' == $_REQUEST['output_format']) echo "selected";?> >CSV</option>
</select>
</td>
</tr>
</table>
<input type="submit" name="action" value="Test Connection"><br />
<br>Dump options(SQL):
<table border="0">
<tr>
<td>Drop table statement: </td>
<td><input type="checkbox" name="sql_drop_table" <?php if(isset($_REQUEST['action']) && ! isset($_REQUEST['sql_drop_table'])) ; else echo 'checked' ?> /></td>
</tr>
<tr>
<td>Create table statement: </td>
<td><input type="checkbox" name="sql_create_table" <?php if(isset($_REQUEST['action']) && ! isset($_REQUEST['sql_create_table'])) ; else echo 'checked' ?> /></td>
</tr>
<tr>
<td>Table data: </td>
<td><input type="checkbox" name="sql_table_data" <?php if(isset($_REQUEST['action']) && ! isset($_REQUEST['sql_table_data'])) ; else echo 'checked' ?>/></td>
</tr>
</table>
<br>Dump options(CSV) - Individual tables only - fill in the box :
<table border="0">
<tr>
<td>Delimiter:</td>
<td><select name="csv_delimiter">
<option value="," <?php if( isset($_REQUEST['output_format']) && ',' == $_REQUEST['output_format']) echo "selected";?>>,</option>
<option value="Tab" <?php if( isset($_REQUEST['output_format']) && 'Tab' == $_REQUEST['output_format']) echo "selected";?>>Tab</option>
<option value="|" <?php if( isset($_REQUEST['output_format']) && '|' == $_REQUEST['output_format']) echo "selected";?>>|</option>
</select>
</td>
</tr>
<tr>
<td>Table:</td>
<td><input type="input" name="mysql_table" value="<?php echo $_REQUEST['mysql_table']; ?>" /></td>
</tr>
<tr>
<td>Header: </td>
<td><input type="checkbox" name="csv_header" <?php if(isset($_REQUEST['action']) && ! isset($_REQUEST['csv_header'])) ; else echo 'checked' ?>/></td>
</tr>
</table>
<input type="submit" name="action" value="Export"><br />
</form>
</body>
</html>
<?php
}
?>
