Give me back my MySQL Command Line!

Displaying Result Sets

Since results sets are displayed as tables on the MySQL command line, it only makes sense to do the same for our Web-based equivalent. First, we must determine the columns that exist in the result set, and put one column for each in our table. We use the mysql_num_fields function to determine the number of columns in our result set, then use mysql_field_name to grab their names:

     <p><b>Result Set:</b></p>  
     <table border="1">  
     <thead>  
     <tr>  
     <?php  
     for ($i=0;$i<mysql_num_fields($result);$i++) {  
       echo('<th>'.mysql_field_name($result,$i).'</th>');  
     }  
     ?>  
     </tr>  
     </thead>

With our row of column names in place, creating the table is a simple matter of looping through the rows of the result set, writing the values in each column into a table cell. Aside from the use of mysql_num_fields again to determine the number of columns, this is pretty standard stuff for experienced PHP coders, so if you're new to PHP don't worry -- it will all become clear as you gain more experience in the language (see Chapter 4 of my PHP/MySQL series for a good tutorial on this stuff).

     <tbody>  
     <?php  
     while ($row = mysql_fetch_row($result)) {  
       echo('<tr>');  
       for ($i=0;$i<mysql_num_fields($result);$i++) {  
         echo('<td>'.$row[$i].'</td>');  
       }  
       echo('</tr>');  
     }  
     ?>  
     </tbody>  
     </table>

Fig. 2 shows what the output of this code looks like for a simple database query.

Fig. 2: Displaying the results of a simple query

That covers the bulk of the code for the script! All that's left is to put it all together.