Showing data – MySQL with PHP

So you have built your MySQL database table and added plenty of data (see Adding data – MySQL with PHP tutorial). Now you will want to be able to present your data via your web browser. The following tutorials will demonstrate a single method for showing MySQL data using PHP.

The concept is quite simple. You connect to the MySQL database and table, ask it to show you your data and then loop through each entry within your database, echoing out your data, row by row.

Connecting to your MySQL database

<?php
// Connection to MySQL database table
$my_db = mysql_connect(‘local_host’, ‘my_username’, ‘my_password’);
$sel3Q = mysql_select_db(‘my_database_name’, $my_db);
//

Building a MySQL query

Remember, think of your MySQL query as nothing more than a question/request that you ask of your database. The query will be sent within a string via the mysql_query() function.

$query=” SELECT * FROM my_table “;  /// Select ‘*’ (all columns) from the table ‘my_table.
$result=mysql_query($query); /// Send $query to MySQL via the mysql_query function.
$num = mysql_num_rows ($result); /// Count the amount of rows found within this query.

Now we need to extract each row from the mysql_query() and loop through each row.

if ($num > 0 )
{

$i=0;
while ($i < $num) {
$name = mysql_result($result,$i,”name”);
$email = mysql_result($result,$i,”email”);

echo “<li>Name: $name – E-mail: $email”;

++$i; }

} else { echo “The database is empty”;  }
?>

We use the function mysql_result()to extract the data for each row into a string, indexed by the variable ‘$i’ – which, you can see start with ‘0’ ($i=0;) and increases by 1 each loop (++$i;). The loop is conditioned by a simple if else statement (if($num >0){…}else{…}) – if the count of results is more than ‘0’, then loop, else echo empty message.

The Finished MySQL – PHP code

<?php
// Connection to MySQL database table
$my_db = mysql_connect(‘local_host’, ‘my_username’, ‘my_password’);
$sel3Q = mysql_select_db(‘my_database_name’, $my_db);
//

$query=” SELECT * FROM my_table “;  /// Select ‘*’ (all columns) from the table ‘my_table.
$result=mysql_query($query); /// Send $query to MySQL via the mysql_query function.
$num = mysql_num_rows ($result); /// Count the amount of rows found within this query.

if ($num > 0 )
{

$i=0;
while ($i < $num) {
$name = mysql_result($result,$i,”name”);
$email = mysql_result($result,$i,”email”);

echo “<li>Name: $name – E-mail: $email”;

++$i; }

} else { echo “The database is empty”;  }

mysql_close();
?>