Reading Information from a Database

Reading information from a database uses the SELECT statement. The SELECT statement has multiple versions, and is the only command we'll look at which can run on multiple tables simultaneously. The most basic select statement follows the general form:

SELECT column_name(s) FROM table_name

The column_name(s) is a comma separated list of the fields (columns) which you want to get the data from. Ex: first_name, last_name. If you want to grab the entire set of table columns, you can use the * (star) symbol and grab everything.

<?php
include('dbconnect.php');
$sql = 'SELECT `name`, `entry`, `entered` FROM guestbook';
$result = mysql_query($sql, $db_connection) or die('Error: ' .mysql_error());

while($row = mysql_fetch_assoc($result)) {
	echo '<div class="gbentry"><p>' .$row['entry'] .'</p>'
		.'<p class="gbauthor">' .$row['name'] . ' on: ' 
		.$row['entered'] .'</p></div>';
}
?> 

The SQL Code Explained

SELECT - Lets the database know what type of command we are going to be running and `name`, `entry`, `entered` is the list of columns we are requesting.

FROM guestbook - The FROM clause lets us know we are looking for the guestbook table.

The PHP Code Explained

The first few lines, we've seen before, so lets look at the new stuff.

while(<condition>) { } - The while statement allows a set of commands to be repeated multiple times. The statements between the braces ({}) is what will be repeated. It will continue to repeat while the condition is still true.

$row = mysql_fetch_assoc($result) - This command pulls the next row from the database result set which pulled from the SQL command. We need to pass the $result variable so PHP knows which result set to get the information from. (You can have multiple result sets on the same page.) This information is placed into an associative array we've named $row.

echo - We've seen the echo command before. Here we are concatenating multiple strings together to output to the user.

<div class="gbentry"> - You can wrap your output in HTML tags. Often you will need to use CSS classes to help style your output.

$row['entry'] - Because we assigned the database row to a variable called $row. The brackets ([]) define that we are going to access one of the elements of the array. The element we access is enclosed within the single quotes.

$row['entered'] - You will notice that the date, as displayed, doesn't seem "normal". It is formatted in European format. While this is fine for clients in Europe, the clients in the US might have issues with it. To fix this issue we can use the DATE_FORMAT(date,format) command.

$sql = 'SELECT `name`, `entry`, DATE_FORMAT(`entered`, "%M %e, %Y") AS formated_date FROM guestbook';

DATE_FORMAT() - A function used to format the date. Additional information regarding the format can be found at this link.

AS formated_date - The AS command in SQL lets a developer rename a column during the read process. This is only shown in the result set, and is typical in cases where a function, such as DATE_FORMAT(), is used to define a value returned in the result set.

Narrowing our Selection

These previous examples show how to get all of the elements within a table. But what happens if we only want to get some elements. This is where we can use the WHERE clause. The WHERE clause allows a developer to narrow the selection by using 1 or more criteria, generally in the form of:

SELECT column_name(s) FROM table_name WHERE condition

A Simple Example

SELECT * FROM guestbook WHERE entered > '2010-01-20'

The SQL Code Explained

WHERE entered > '2010-01-20' - The WHERE clause is telling the database that it will limit which records are displayed based upon some criteria. The criteria is the entered field, and it has to be after the date '2010-01-20'. (Jan. 20th, 2010 - notice it is listed in European Format).

Providing an Order to our Selection

Generally, the database displays the records in the order they were entered, but we cannot be guaranteed that will be the order, nor may you want that order.

The ORDER BY clause allows us to specify the order of the records in the recordset, based upon a column in the table.