Type Here to Get Search Results !

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 documentationmysql_fetch_array, which returns each row as an array.

The code below shows how to use documentationmysql_fetch_array to loop through a recordset:


show example 

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:


show example

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


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

 



The code for person.php


show example

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.

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.

Post a Comment

0 Comments