Lesson 20: Get data from database
Lesson 20: Get data from database
Now it's time to retrieve data from our database to our PHP pages.
This is really one of the most important lessons in this tutorial. Once you have read and understood this lesson, you will realize why database-driven web solutions are so powerful, and your views on web development will be expanded dramatically.
SQL queries
To retrieve data from a database, you use queries. An example of a query could be: "get all data from the table 'people' sorted alphabetically" or "get names from the table 'people'".
Again, the language Structured Query Language (SQL) is used to communicate with the database. Try looking at this simple example:
Get all data from the table 'people'
Will be written like this in SQL:
SELECT * FROM people
The syntax is pretty self-explanatory. Just read on and see how SQL statements are used in the examples below.
Example 1: Retrieve data from a table
This example uses the database and table from lesson 19 and lesson 18. Therefore, it is important that you read these lessons first.
The example shows how data in the table "people" is retrieved with an SQL query.
The SQL query returns a result in the form of a series of records. These records are stored in a so-called recordset. A recordset can be described as a kind of table in the server's memory, containing rows of data (records), and each record is subdivided into individual fields (or columns).
A recordset can be compared to a table where each record could be compared to a row in the table. In PHP, we can run through a recordset with a loop and the function 
mysql_fetch_array, which returns each row as an array.
The code below shows how to use mysql_fetch_array to loop through a recordset:
<html> <head> <title>Retrieve data from database </title> </head> <body> <?php // Connect to database server mysql_connect("mysql.myhost.com", "user", "sesame") or die (mysql_error ()); // Select database mysql_select_db("mydatabase") or die(mysql_error()); // SQL query $strSQL = "SELECT * FROM people"; // Execute the query (the recordset $rs contains the result) $rs = mysql_query($strSQL); // Loop the recordset $rs // Each row will be made into an array ($row) using mysql_fetch_array while($row = mysql_fetch_array($rs)) { // Write the value of the column FirstName (which is now in the array $row) echo $row['FirstName'] . "<br />"; } // Close the database connection mysql_close(); ?> </body> </html>
Show exampleNotice that for every record how we get the content of the column "FirstName" by typing $row['FirstName']. Similarly, we can get the content of the column "Phone" by writing $row['Phone'], for example.
The order of the recordset is exactly the same as in the table in the database. But in the next example, it will be shown how to sort recordset.
Example 2: Sort the data alphabetically, chronologically or numerically
Often it can be helpful if a list or table of data is presented alphabetically, chronologically or numerically. Such sorting is very easy to do with SQL, where the syntax Order By ColumnName is used to sort according to the column contents.
Look at the SQL statement from the example above:
strSQL = "SELECT * FROM people"
The records can, for example, be sorted alphabetically by the first name of the people this way:
strSQL = "SELECT * FROM people ORDER BY FirstName"
Or chronologically by date of birth like this:
strSQL = "SELECT * FROM people ORDER BY BirthDate"
The sorting can be charged from ascending to descending by adding DESC:
strSQL = "SELECT * FROM people ORDER BY BirthDate DESC"
In the following example, the people are sorted by age:
<html> <head> <title>Retrieve data from database </title> </head> <body> <?php // Connect to database server mysql_connect("mysql.myhost.com", "user", "sesame") or die (mysql_error ()); // Select database mysql_select_db("mydatabase") or die(mysql_error()); // SQL query $strSQL = "SELECT * FROM people ORDER BY BirthDate DESC"; // Execute the query (the recordset $rs contains the result) $rs = mysql_query($strSQL); // Loop the recordset $rs while($row = mysql_fetch_array($rs)) { // Write the value of the column FirstName and BirthDate echo $row['FirstName'] . " " . $row['BirthDate'] . "<br />"; } // Close the database connection mysql_close(); ?> </body> </html>
Show exampleTry to change the SQL statement yourself and sort the records by first name, last name or phone number.
Retrieve selected data
Until now, our SQL statement retrieves all rows from the table. But often you need to set criteria in the SQL query for the data to be retrieved, for instance, if we only want the rows for those who have a particular phone number or a certain last name.
Say, we only want to retrieve people from the database who have the phone number "66554433". That could be done like this:
strSQL = "SELECT * FROM people WHERE Phone = '66554433 '"
There are six relational operators in SQL:
= Equals
< Less than
> Greater Than
<= Less than or equal to
>= Greater than or equal to
!= Not equal to
In addition, there are some logical operators:
AND
OR
NOT
See lesson 6 for more information on how to set up conditions.
In the next example, we use conditions to set up an address book.
Example 3: Address book
In this example, we will try to combine many of the things you have just learned. We will make a list of the names from the database where each name is a link to further details about the person.
For this, we need two files - list.php and person.php - with the following code:
The code of list.php
<html> <head> <title>Retrieve data from the database</title> </head> <body> <ul> <?php // Connect to database server mysql_connect("mysql.myhost.com", "user", "sesame") or die (mysql_error ()); // Select database mysql_select_db("mydatabase") or die(mysql_error()); // SQL query $strSQL = "SELECT * FROM people ORDER BY FirstName DESC"; // Execute the query (the recordset $rs contains the result) $rs = mysql_query($strSQL); // Loop the recordset $rs while($row = mysql_fetch_array($rs)) { // Name of the person $strName = $row['FirstName'] . " " . $row['LastName']; // Create a link to person.php with the id-value in the URL $strLink = "<a href = 'person.php?id = " . $row['id'] . "'>" . $strNavn . "</a>"; // List link echo "<li>" . $strLink . "</li>"; } // Close the database connection mysql_close(); ?> </ul> </body> </html>
The code for person.php
<html> <head> <title>Retrieve data from database</title> </head> <body> <dl> <?php // Connect to database server mysql_connect("mysql.myhost.com", "user", "sesame") or die (mysql_error ()); // Select database mysql_select_db("mydatabase") or die(mysql_error()); // Get data from the database depending on the value of the id in the URL $strSQL = "SELECT * FROM people WHERE id=" . $_GET["id"]; $rs = mysql_query($strSQL); // Loop the recordset $rs while($row = mysql_fetch_array($rs)) { // Write the data of the person echo "<dt>Name:</dt><dd>" . $row["FirstName"] . " " . $row["LastName"] . "</dd>"; echo "<dt>Phone:</dt><dd>" . $row["Phone"] . "</dd>"; echo "<dt>Birthdate:</dt><dd>" . $row["BirthDate"] . "</dd>"; } // Close the database connection mysql_close(); ?> </dl> <p><a href="list.php">Return to the list</a></p> </body> </html>
Show exampleThe address book example is rather simple, but it shows the potential of working with PHP and databases.
Imagine that the database had contained 10,000 products with detailed descriptions. By making a few changes in the above files, you could easily create a product catalogue with more than 10,000 pages with only one database and two PHP files.
Welcome to a world with extensive websites that are easy to develop and maintain! Once you've learned to work with databases, your web solutions will never be the same again.
