PHP MYSQLi Display all tables in a database

Question

How to display all the information in a database (All tables and records) using PHP? I read table mapping as an HTML table, but how to do this for all tables?

I tried an example here: http://davidwalsh.name/html-mysql-php

But it shows the table names, how can I also display all the values?

Thanks in advance

Answer 1

If you want to print all tables using values in mysql , you can use the following code:

 <?php mysql_connect ("localhost", "DB_USER", "DB_PASSWORD"); //your mysql connection mysql_select_db('DB_NAME') or die( "Unable to select database"); //your db name $tables = mysql_query("SELECT table_name FROM information_schema.tables WHERE table_schema='DB_NAME'"); //pull tables from theh databsase while ($table= mysql_fetch_row($tables)) { $rsFields = mysql_query("SHOW COLUMNS FROM ".$table[0]); while ($field = mysql_fetch_assoc($rsFields)) { echo $table[0].".".$field["Field"].", "; //prints out all columns } echo $table[0]; $query = "SELECT * FROM ".$table[0]; //prints out tables name $result = mysql_query($query); PullValues($result); //call function to get all values } //function to pull all values from tables function PullValues($result) { if($result == 0) { echo "<b>Error ".mysql_errno().": ".mysql_error()."</b>"; } elseif (@mysql_num_rows($result) == 0) { echo("<b>Query completed. No results returned.</b><br>"); } else { echo "<table border='1'> <thead> <tr><th>[Num]</th>"; for($i = 0;$i < mysql_num_fields($result);$i++) { echo "<th>" . $i . "&nbsp;-&nbsp;" . mysql_field_name($result, $i) . "</th>"; } echo " </tr> </thead> <tbody>"; for ($i = 0; $i < mysql_num_rows($result); $i++) { echo "<tr><td>[$i]</td>"; $row = mysql_fetch_row($result); for($j = 0;$j < mysql_num_fields($result);$j++) { echo("<td>" . $row[$j] . "</td>"); } echo "</tr>"; } echo "</tbody> </table>"; } //end else } ?> 

I use this and work fine in mysql , for mysqli you need to tweak it a bit.

Hope this helps.

Answer 2

Ok, it turned out .. try this

 <?php $mysqli = new mysqli("localhost", "username", "password", "DB"); $result = $mysqli->query("SHOW TABLES"); while ( $row = $result->fetch_row() ){ $table = $row[0]; echo '<h3>',$table,'</h3>'; $result1 = $mysqli->query("SELECT * FROM $table"); if($result1) { echo '<table cellpadding="0" cellspacing="0" class="db-table">'; $column = $mysqli->query("SHOW COLUMNS FROM $table"); echo '<tr>'; while($row3 = $column->fetch_row() ) { echo '<th>'.$row3[0].'</th>'; } echo '</tr>'; while($row2 = $result1->fetch_row() ) { echo '<tr>'; foreach($row2 as $key=>$value) { echo '<td>',$value,'</td>'; } echo '</tr>'; } echo '</table><br />'; } } $mysqli->close(); ?> 

Answer 3

You can use the recursive function to display the tree structure of your database.

Here is a sample code from http://webcodingeasy.com/PHP/Simple-recursive-function-to-print-out-database-tree-structure

 <?php function print_menu($id = 0) { // get all records from database whose parent is $id $result = $query->select_result("*", "table", "where parent = '".$id."'"); //check if there are any results if($result != 0) { echo "<ul> n"; while($row = $query->fetch($result)) { //print result and call function to check if it has children echo "<li>".$row['name']."</li> n"; print_menu($row['ID']); } echo "</ul> n"; } } print_menu(); ?>