MySQL Query

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:

<?php
// 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")
or die(mysql_error());  

// 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'];

?>

Display:

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.

Posted in: MySQL