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:
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
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.
<html>
<head>
<title>Retrieve data from database </title>
</head>
<body>
<?php
mysql_connect("mysql.myhost.com", "user", "sesame") or die (mysql_error ());
mysql_select_db("mydatabase") or die(mysql_error());
$strSQL = "SELECT * FROM people";
$rs = mysql_query($strSQL);
while($row = mysql_fetch_array($rs)) {
echo $row['FirstName'] . "<br />";
}
mysql_close();
?>
</body>
</html>
Notice 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
mysql_connect("mysql.myhost.com", "user", "sesame") or die (mysql_error ());
mysql_select_db("mydatabase") or die(mysql_error());
$strSQL = "SELECT * FROM people ORDER BY BirthDate DESC";
$rs = mysql_query($strSQL);
while($row = mysql_fetch_array($rs)) {
echo $row['FirstName'] . " " . $row['BirthDate'] . "<br />";
}
mysql_close();
?>
</body>
</html>
Try 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
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
mysql_connect("mysql.myhost.com", "user", "sesame") or die (mysql_error ());
mysql_select_db("mydatabase") or die(mysql_error());
$strSQL = "SELECT * FROM people ORDER BY FirstName DESC";
$rs = mysql_query($strSQL);
while($row = mysql_fetch_array($rs)) {
$strName = $row['FirstName'] . " " . $row['LastName'];
$strLink = "<a href = 'person.php?id = " . $row['id'] . "'>" . $strNavn . "</a>";
echo "<li>" . $strLink . "</li>";
}
mysql_close();
?>
</ul>
</body>
</html>
The code for person.php
<html>
<head>
<title>Retrieve data from database</title>
</head>
<body>
<dl>
<?php
mysql_connect("mysql.myhost.com", "user", "sesame") or die (mysql_error ());
mysql_select_db("mydatabase") or die(mysql_error());
$strSQL = "SELECT * FROM people WHERE id=" . $_GET["id"];
$rs = mysql_query($strSQL);
while($row = mysql_fetch_array($rs)) {
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>";
}
mysql_close();
?>
</dl>
<p><a href="list.php">Return to the list</a></p>
</body>
</html>
The 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.
