Login | Register | FAQ
Anonymous

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:


	<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>
	
	

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
	// 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>
	
	

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


	<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>
	
	

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.


Related topics in the PHP ForumRepliesViews
Passing form data to a cookie using php2344
mysql syntax is not secure11131
exporting my database21363
Retrieving value from database & displayed in select button23173
Joins with Like Statement Retrieving from MYSQL11468

+ Post a new topic


<< Lesson 19: Insert data into a database

Lesson 21: Delete data from a database >>