Basic database export script

from the Artful MySQL Tips List


For occasions where the server offers access to neither mysqldump nor phpMyAdmin, here is a basic PHP script for exporting a database and its data to an HTML page, from which you can copy & paste the result to anywhere you like. You may have to touch up the script (we've used it only occasionally). To use it, fill in the four variable values at the top of the page, and run it as you would any PHP browser script.

<?php
$host
="...";
$user "...";
$pswd "...";
$db "...";

$conn mysqli_connect($host,$user,$pswd$db) or die( mysqli_connect_error());
$res mysqli_query$conn"show tables" ) or die( mysqli_error($conn) );
echo 
"set @fkeychecks=@@foreign_key_checks;<br/>set foreign_key_checks=0<br/>;";
while( 
$row mysqli_fetch_row$res )) {
  
$tbl $row[0];
  
$resddl mysqli_query$conn"SHOW CREATE TABLE $tbl) or die( mysqli_error($conn) );
  
$ddl mysqli_fetch_row$resddl ) or die( mysqli_error($conn) );
  echo 
"<br/>-- Structure & data of $db.$tbl<br/>" nl2br($ddl[1]) . ";<br/>";
  
$resdata mysqli_query$conn"select * from $tbl") or die( mysqli_error($conn) );
  
$rows mysqli_num_rows$resdata ); 
  if( 
$rows ) continue;
  
$cols mysqli_num_fields$resdata );
  
$coltypes=array();
  for( 
$i=0$i<$cols;$i++ ) {
    
$obj mysqli_fetch_field$resdata );
    
$coltypes[] = $obj->type;
  }
  while( 
$data mysqli_fetch_row$resdata )) {
    
$ins "INSERT INTO $tbl VALUES(";
    for( 
$i=0$i $cols$i++ ) {
      
$val $data[$i];
      if( 
stripos"datetime|timestamp|string|blob|char"$coltypes[$i] ) != false $val "'" $val "'";
      
$ins .= $val;
      if( 
$i+$cols $ins .= ",";
    }
    
$ins .= ");<br/>";
  }
  if( !empty( 
$ins )) echo $ins;
}
echo 
"set foreign_key_checks=@fkeychecks;<br/>";
mysqli_close$conn );
?>

Return to the Artful MySQL Tips page