MySQL, PHP: Display MySQL table fields and data

• inShare

While this may seem simple, but it’s more complicated than it may appear because you have to first decide which data (rows, fields, etc.) to be extracted or selected and in what way the data from the MySQL table are presented.

But to simply read from a MySQL table for the fields and data:

SELECT * FROM sometable

Which would select all records (rows) on all fields (columns) from the table named `sometable`. The asterisk * indicates all fields. To select just some of the fields, you will have to specify them one by one explicitly in the SQL:

SELECT id, title, count FROM sometable

Returns all records (rows) from the table `sometable`, but only 3 fields, namely `id`, `title`, and `count` of each record are included. To get the data or records from table `sometable` with id <= 10:

SELECT * FROM sometable WHERE id <= 10

In PHP, you can use mysql_query(‘SELECT … ‘) to execute a SQL query so that the MySQL database system returns certain results set catering to the demand. The returned subject is of result resource type in PHP and you will further extract the original data by fetching the data rows one by one and possibly put them in a native array:

$results = mysql_query('SELECT * FROM sometable');
$results_array = array();
while ($row = mysql_fetch_array($results)) {
    $results_array[$row['id']] = $row;
}

Now you would have all the data you need in $results_array. You can then display the data in a HTML table:

<table>
<?php foreach ($results_array as $id => $record) { ?>
    <tr>
        <td><?php echo $id;?></td>
        <td><?php echo $record['title'];?></td>
        <td><?php echo $record['count'];?></td>
    </tr>
<?php } ?>
</table>

At last, you have a HTML table containing all the data that was originally in a MySQL database table.