So far we have seen a couple different uses of PHP's mysql_query function and we'll be seeing more of it
as nearly all MySQL in PHP is done through the MySQL Query function. We have already created a new table and inserted data into that table. In this lesson we will
cover the most common MySQL Query that is used to retrieve information from a database.
Retrieving Data With PHP & MySQL
Usually most of the work done with MySQL involves pulling down data
from a MySQL database. In MySQL, data is retrieved with the "SELECT"
keyword. Think of SELECT as working the same way as it does on your computer. If
you wanted to copy some information in a document, you would first select the desired information, then copy and paste.
Using MySQL SELECT & FROM
Before attempting this lesson, be sure that you have created a table that contains
some data, preferably the same data that we had in the MySQL Insert lesson. In this example, we will output the first entry of our MySQL "examples" table to the web browser.
PHP & MySQL Code:
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());
// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM example")
// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry
echo "Name: ".$row['name'];
echo " Age: ".$row['age'];
Name: Tim Mellowman Age: 23
This is an example of how to use MySQL's SELECT statement in PHP. Although the MySQL code is simple, printing out the information with PHP is somewhat more involved.
Below is a step-by-step walkthrough of the code.
'$result = mysql_query("SELECT * FROM example")'
When you perform a SELECT query on the database it will return a MySQL Resource that holds everything from your MySQL table, "example". We want to use this Resource in our PHP code, so we need to store it in a variable, $result.
'SELECT * FROM example'
Yes, this is a partial repeat of the same line of code, but we wanted to explain this MySQL statement in greater detail again!
In English, this line of code reads "Select every entry from the table example". The asterisk is the wild card in MySQL which just tells MySQL to include every single column for that table.
'$row = mysql_fetch_array( $result );'
$result is now a MySQL Resource containing data from your MySQL table, "example". Data is being stored in $result, but it is not yet visible to visitors of your website. When we pass $result into the mysql_fetch_array function -- mysql_fetch_array($result) -- an associative array (name, age) is returned.
In our MySQL table "example," there are only
two fields that we care about: name and age. These names 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'].
PHP is case sensitive when you reference MySQL column names, so be sure to use capitalization
in your PHP code that matches the MySQL column names!
Continuing the Example
In this lesson, we learned how to get the first entry from a MySQL table and output to the browser using PHP. In the next lesson we will see how to retrieve every entry of a table and put it into a nicely formatted table.
However, we recommend that you first understand the PHP and MySQL code
in this lesson before proceeding.
Download Tizag.com's MySQL Book
If you would rather download the PDF of this tutorial, check out our
MySQL eBook from the Tizag.com store.
You may also be interested in getting the PHP eBook
Found Something Wrong in this Lesson?
Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!