Connect to server, not getting any visual tables

Question

I'm doing a simple get table from server request. But when trying it out on my local machine i don't see anything at all. I've checked that the local MySQL server is running as it should be and the credentials i provide should be correct. I do not get any errors but as I said i cant see anything from the database, but i can see the other elements on the page. I appreciate all the help I can get, i'm kind of new to the MySQL side of things.

Here's my code:

     <html>
<style>
table.db-table      { border-right:1px solid #ccc; border-bottom:1px solid #ccc; }
table.db-table th   { background:#eee; padding:5px; border-left:1px solid #ccc; border-top:1px solid #ccc; }
table.db-table td   { padding:5px; border-left:1px solid #ccc; border-top:1px solid #ccc; }
</style>
    </html>


<script>
$connection = mysql_connect('localhost','user','password');
mysql_select_db('my_db',$connection);

/* show tables */
$result = mysql_query('SHOW TABLES',$connection) or die('cannot show tables');
while($tableName = mysql_fetch_row($result)) {

    $table = $tableName[0];

    echo '<h3>',$table,'</h3>';
    $result2 = mysql_query('SHOW COLUMNS FROM '.$table) or die('cannot show columns from '.$table);
    if(mysql_num_rows($result2)) {
        echo '<table cellpadding="0" cellspacing="0" class="db-table">';
        echo '<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default<th>Extra</th></tr>';
        while($row2 = mysql_fetch_row($result2)) {
            echo '<tr>';
            foreach($row2 as $key=>$value) {
                echo '<td>',$value,'</td>';
            }
            echo '</tr>';
        }
        echo '</table><br />';
    }
}
    </script>

See: http://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php Configure your server to see errors reported. It will help the ones answering your question, but above all, it will help you spotting the next error. — GolezTrol, May 12 '14 at 13:53
Welcome to MySQL! The `mysql_` functions are old and insecure, so as you're just starting out please consider using [MySQLi](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php). See http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection. — Raad, May 12 '14 at 13:59

score 0 · Answer 1 · answered May 12 '14 at 14:17

I'm taking a punt on this, but I suspect you are not seeing any data returned because commands like "SHOW TABLES" and "SHOW COLUMNS" are not actual queries. They are internal MySQL commands, so I doubt they would be executed by mysql_query.

You need to use the data dictionary tables instead, which for MySQL is the INFORMATION_SCHEMA database. So your first query would be:

SELECT table_name
FROM   information_schema.tables
WHERE  table_schema = 'my_db'

and your subsequent queries would be:

SELECT column_name
FROM  information_schema.columns
WHERE  table_schema = 'my_db'
AND    table_name = [table_name]

Connect to server, not getting any visual tables

1 Answers1