PHP MYSQLi Displaying all tables in a database

Question

How do I display out all the information in a database (All tables and records) using PHP? I read displaying tables as HTML table but how do I do it for all the tables?

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

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

Thanks In Advance

Answer 1

you can use the recursive function to display the tree Structure of your database.

Here is the 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();
?>

Answer 2

Okay got it .. 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

If you want to pull all tables with the 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 am using this and works fine in mysql, for mysqli you need to tweak it a very little.

Hope this helps.