Saturday, September 20, 2008

get database structure using PHP

I was facing problem to connect a mysql host, as it was only accepting connection from a specific server. But I need to know the database structure to design some report.

At last I have written a simple php code to print the table name along with the structure.


<?php

mysql_connect("host_name","user","password") or die(mysql_error());
mysql_select_db("database_name") or die(mysql_error());


$result = mysql_query("SHOW TABLES;") or die(mysql_error());

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

echo $row[0]; //print the table name

$result2 = mysql_query("DESCRIBE ".$row[0].";") or die(mysql_error()); //get details schema for each table

echo "<table border='1' width='70%'>";
echo "<tr><td>Field</td><td>Type</td><td>Null</td><td>Key</td><td>Default</td><td>Extra</td>";

while($row2 = mysql_fetch_array($result2)){

for($i=0; $i<6; $i++){
if($row2[$i] == "" || $row2[$i] == NULL){
$row2[$i] = " ";
}
}

echo "<tr>";
echo "<td>".$row2[0]."</td><td>".$row2[1]."</td><td>".$row2[2]."</td><td>".$row2[3]."</td><td>".$row2[4]."</td><td>".$row2[5];
echo "</tr>";
}

echo "</table>";
echo "<br/>";
}

?>


Just change your database configuration then upload in your server. Browse the url and you must delete the file from server after get the structure. Be safe...