aandroidtest aandroidtest - 2 years ago 126
MySQL Question

PHP MYSQLi Displaying all tables in a database

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:

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

Thanks In Advance

Answer Source

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

    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>"); 
            echo "<table border='1'> 
            for($i = 0;$i < mysql_num_fields($result);$i++) 
                echo "<th>" . $i . "&nbsp;-&nbsp;" . mysql_field_name($result, $i) . "</th>"; 
            echo "  </tr> 
            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> 
        }  //end else 

I am using this and works fine in mysql, for mysqli you need to tweak it a very little.

Hope this helps.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download