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 < 1 ) 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+1 < $cols ) $ins .= ",";

    }

    $ins .= ");<br/>";

  }

  if( !empty( $ins )) echo $ins;

}

echo "set foreign_key_checks=@fkeychecks;<br/>";

mysqli_close( $conn );

?>

Last updated 27 Feb 2025

Return to the Artful MySQL Tips page