Monday 9 June 2008

MySQL PHP Database Ripper

Recently, I've been working on several projects where direct database access has been an issue.

To get around this, i've made a php mysql Database Ripper. It reads the database schema and then prints out the table structure and information for each table.

Here's the source - Enjoy :)


/************************************

db connection

************************************/

$host = "localhost";

$user = "dbuser";

$pass = "dbpassword";

$dbname = "dbname";


$db = mysql_connect($host, $user, $pass) or die(mysql_error());

mysql_select_db($dbname, $db) or die(mysql_error());


/***********************************************************/


$ic = 0;


$lsql = "show tables;";

if ($result = mysql_query($lsql)){

while ($row = mysql_fetch_array($result, MYSQL_NUM)){

$mySQLArray[$ic++] = $row[0];

}

mysql_free_result($result);

}


foreach ($mySQLArray as $litem){

$lsql = "SHOW COLUMNS FROM ".$litem.";";

$lreturn .=("create table ".$litem."(\r\n");

$lcount = 0;

if ($result = mysql_query($lsql)){

while ($row = mysql_fetch_array($result, MYSQL_NUM)){

if ($lcount > 0) $lreturn .=(",\r\n");

$lreturn .=($row[0]." ".$row[1]);

if ($row[2] == "NO") $lreturn .=(" NOT NULL ");

if ($row[4] != "") $lreturn .=(" default '".$row[4]."' ");

if ($row[3] == "PRI") $lreturn .=(" primary key ");

$lreturn .= (" ".$row[5]." ");

$lcount++;

}

mysql_free_result($result);

}

$lreturn .=(") ENGINE=InnoDB DEFAULT CHARSET=latin1;\r\n\r\n");

$lsql = "select * FROM ".$litem.";";

if ($result = mysql_query($lsql)){

while ($row = mysql_fetch_array($result, MYSQL_NUM)){

$lreturn .= "insert into ".$litem." values (";

$lstr = "";

foreach($row as $lrow){

$lstr .= "'".mysql_real_escape_string($lrow) . "',";

}

$lreturn .= rtrim($lstr ,",").");\r\n";

}

mysql_free_result($result);

}

$lreturn .= ("\r\n\r\n\r\n");

}


echo($lreturn);


?>

No comments: