MySQL Select

You have seen two types of MySQL queries thus far: the query which we used to create a table and the query we used to insert data into our newly created table. The query in this lesson is SELECT, which is used to get information from the database, so that its data can be used in our PHP script.

Retrieving Information from MySQL

Finally, we get to use the data in our MySQL database to create a dynamic PHP page. In this example we will select everything in our table "example" and put it into a nicely formatted HTML table. Remember, if you don't understand the HTML or PHP code, be sure to check out the HTML and/or PHP Tutorial(s).

PHP & MySQL Code:

<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM example") 
or die(mysql_error());  

echo "<table border='1'>";
echo "<tr> <th>Name</th> <th>Age</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
 // Print out the contents of each row into a table
 echo "<tr><td>"; 
 echo $row['name'];
 echo "</td><td>"; 
 echo $row['age'];
 echo "</td></tr>"; 
} 

echo "</table>";
?>

Display:

Name	Age
Timmy Mellowman	23
Sandy Smith	21
Bobby Wallace	15

Because we only had three entries in our table, three rows appeared above. If you added more entries to your database's table, then you would see each additional row appear in the above table. If you do not understand the above PHP, you can view our PHP Array Tutorial & PHP Loop Tutorial.

'$result = mysql_query...'

When you select items from a database using mysql_query, the data is returned as a MySQL result. Since we want to use this data in our table we need to store it in a variable. $result now holds the result from our mysql_query.

'SELECT * FROM example'

In English, this line of code reads "Select everything from the table example". The asterisk is the wild card in MySQL which just tells MySQL to retrieve every single field from the table.

'while($row = mysql_fetch_array( $result )'

The mysql_fetch_array function gets the next-in-line associative array from a MySQL result. By putting it in a while loop it will continue to fetch the next array until there is no next array to fetch. This function can be called as many times as you want, but it will return FALSE when the last associative array has already been returned.
By placing this function within the conditional statement of the while loop, we can kill two birds with one stones.

1. We can retrieve the next associative array from our MySQL Resource, $result, so that we can print out the name and age of that person.
2. We can tell the while loop to stop printingn out information when the MySQL Resource has returned the last array, as False is returned when it reaches the end and this will cause the while loop to halt.

In our MySQL table "example" there are only two fields that we care about: name and age. These fields are the keys to extracting the data from our associative array. To get the name we use $row['name'] and to get the age we use $row['age'].

Practice What You Have Learned

Use the query that we have provided or make a new one and try putting it into a formatted HTML table. It might be useful to try out other methods of HTML formatting as well. See which one you like best!
By now you should be starting to understand how powerful PHP and MySQL are when used together. The tasks that you can complete with MySQL and PHP would be nearly impossible to do by hand in HTML. Imagine trying to create an HTML table of 6000 entries without using a MySQL database and a PHP while loop!

Posted in: MySQL