Navigation

Export SQL database (all tables) in PHP to html

Export all tables to HTML:


<?php
//  apt-get install php5-sybase
//  /etc/init.d/apache2 restart
$myServer = "myServer";
$myUser = "myUser";
$myPass = "myPass";
$myDB = "myDB";

//connection to the database
  $dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer");
  print "Connection to the Server opened";
  
  //select a database to work with
  $selected = mssql_select_db($myDB, $dbhandle)
    or die("Couldn't open database $myDB");
    
  //declare the SQL statement that will query the database
  $query = "select name from sysobjects where xtype = 'U'";
  $all = MSSQL_Query("select TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS order by TABLE_NAME, ORDINAL_POSITION");
  $tables = array();
 
while($fet_tbl = MSSQL_Fetch_Assoc($all))
{ // PUSH ALL TABLES AND COLUMNS INTO THE ARRAY
  $table_name = $fet_tbl[TABLE_NAME];  
 
  if (!isset($tables[$table_name]))
  { $tables[$table_name] = array(); }
 
  $tables[$table_name][] = $fet_tbl[COLUMN_NAME];
}


$withoutArray = array('without','this','tables');

/*
foreach($tables as $table_name => $columns) 
{
  if ( && !in_array($table_name, $withoutArray)) 
  echo "'". $table_name . "', "; 
}
*/
mssql_close($dbhandle); 

foreach($tables as $table_name => $columns) 
{ 
//  if ($table_name<>"assetstatus" && !in_array($table_name, $withoutArray))
  if (!file_exists($table_name . ".html") && !in_array($table_name, $withoutArray))
  { 
  $dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer");
  
  //select a database to work with
  $selected = mssql_select_db($myDB, $dbhandle)
    or die("Couldn't open database $myDB");

  //echo "<h2>". $table_name . "</h2><ol>";
  $table = MSSQL_Query("select * from " . $table_name);
  $columnList = "";
  mkdir($myDB);
  $myFile = $myDB . "/" . $table_name . ".html";
  $fh = fopen($myFile, 'w') or die("can't open file");
  fwrite($fh, "<table>");
  $stringData = "<tr>";
  echo $table_name . "</br>";

  foreach($columns as $column ) 
  { 
    $stringData = $stringData . "<td>" . $column . "</td>";
  }
  $stringData = $stringData . "</tr>";
  fwrite($fh, $stringData);
  
  while($row = mssql_fetch_array($table))
  {
    $stringData = "<tr>";
    foreach($columns as $column ) 
    { 
      $stringData = $stringData . "<td>" . $row[$column] . "</td>";
    }
    echo ".";
    $stringData = $stringData . "</tr>";
    fwrite($fh, $stringData);
  } 
  mssql_close($dbhandle); 
  fwrite($fh, "</table>");
  fclose($fh);
  }
}


  //close the connection
  mssql_close($dbhandle);
echo "ok";
?>
      

...