Chapter 2:
Retrieving Data

The Structured Query Language (SQL) is a language for interacting with relational database systems that's been around since the early 1970s. Over the years, other database vendors have attempted to mimic or replace SQL with their own query languages. But despite their attempts, SQL still provides the easiest interface for both users and administrators to interact with any type of database system.

In 1986, the American National Standards Institute (ANSI) created the first SQL standards. The United States government adopted them as a federal standard and named it ANSI SQL89. Most commercial database vendors now use this SQL standard to interface with their products.

As you can probably guess, additional updates have been made to the ANSI SQL standard over the years, resulting in SQL92 (also called SQL2) and SQL99 (also called SQL3) versions. The latest approved version of SQL is ANSI SQL 2006 (which doesn't have a nickname yet), which, of course, MySQL fully supports.

SQL Command Format

The SQL language provides a standard way of sending commands to a database. SQL commands consist of three elements:

• a keyword
• an identifier
• optionally one or more literals

SQL keywords define the action the database server takes based on the SQL statement. The SQL language provides many keywords for performing lots of actions. We'll only be using a few keywords in the SQL statements within our application, so don't worry about having to know and understand the entire SQL library. The table below shows the four most common SQL keywords and what they do.

SQL command identifiers define the database objects used in a command. This is most often either a database name, table name, or the names of data fields. The SQL identifiers help us select which data elements to retrieve from the database and which table to select them from.

Finally, SQL command literals define specific data values referenced by the keyword. These are constant values, such as data values to insert into a table or data values used to search within the table data. You must enclose string literals in quotes (either double or single quotes), but you can use numerical values without quotes.

Creating a Database Query

A query is an SQL SELECT statement that searches the database for specific data records. The basic format of a SELECT statement is:

SELECT datafields FROM table

The datafields parameter is a comma separated list of the data field names you want the query to return. When MySQL returns records that match the query, you can determine which data fields are returned. If you want to receive all of the data field values, you can use an asterisk as a wildcard character.

You must also specify the specific table you want the query to search. It obviously wouldn't do any good to look for recipe data in the comments table. To get meaningful results, you must match your query data fields with the proper table.

By default, the SELECT statement returns all of the data records in the specified table. You can use one or more modifiers to define how MySQL returns the data requested by the query. Here's a table of the modifiers we'll be using in our SQL statements:

The WHERE clause is the most common SELECT statement modifier. It allows you to specify conditions to filter data from the result set. Here's an example of using the WHERE clause:

SELECT recipeid, title FROM recipes WHERE recipeid = 3

This SQL statement returns the recipeid and title data values from the recipes table records where the recipeid data field is equal to the numeric value 3. We'll be using the WHERE clause throughout the application to restrict which data elements MySQL returns to our application (such as only returning comments pertinent to a specific recipeid).

The ORDER BY clause allows you to specify the data field (or fields) by which you want MySQL to sort returned data. You can also specify ASC or DESC for ascending or descending order.

SELECT commentid, comment FROM comments ORDER BY commentid DESC

The LIMIT clause allows you to specify a starting record number and the number of records to return. The starting record number is an offset within the returned result set, not the actual data records. The offset value starts at 0 and goes to however many records are returned in the result set. For example the SQL statement:

SELECT recipeid, title FROM recipes ORDER BY recipeid DESC LIMIT 0, 5

returns the last five recipeids and titles from the table.

Testing Your Queries

Before diving in and creating PHP code to send your SQL queries to the MySQL server, it's best to manually test your queries against your database data. This helps prevent query errors from within the application code, which are often hard to debug.

The WAMP5 server provides two tools that you can use to send SQL queries to the MySQL database server:

• The MySQL Console tool
• The phpMyAdmin tool

The MySQL Console provides a text-based interface to the MySQL server. You can manually enter SQL commands at the command prompt and view the results in the command window. The phpMyAdmin tool provides a graphical tool that allows you to enter SQL commands, view the returned records, and also edit any data contained in the returned records.

Here's how to submit SQL queries using phpMyAdmin:

1. Start the WAMP5 server (if it's not already running).
2. Left-click the control icon in the system tray, and select phpMyAdmin.
3. Select the database to use from the left-side drop-down box. Select the recipe(4) entry to get to the recipe database.
4. Select the SQL tab at the top of the window.
5. Enter this SQL query in the text box at the top of the window:

   SELECT recipeid, title, shortdesc FROM recipes WHERE recipeid = 1

6. Click the Go button to submit your query.

After selecting the Go button, phpMyAdmin sends the query to the MySQL database server, then returns to the database window and displays the results, as shown.

The phpMyAdmin SQL results window

Obviously, your results should reflect the sample data you placed in your recipes table. If you didn't put any sample data into the table, you'll get a message that the query returned no rows of data.

Once you become familiar with SQL, you're ready to use it in your PHP programs to return data to your Web page. That's what we'll talk about in Chapter 3.

Chapter 3:
Processing SQL
Queries using PHP

The PHP programming language includes specific modules to accommodate retrieving information from database servers. There are different modules for different database servers. The WAMP5 server includes modules for communicating with the MySQL server and a product called SQLite, a simple file-based database system. If you're working with an ISP hosted system, you'll have to ensure that their PHP implementation includes the MySQL server modules.

The PHP database modules include special functions for connecting to a database server, sending SQL statements, and processing the returned results. The process of retrieving data from a MySQL server involves four separate functions:

1. mysql_connect() to connect to the server
2. mysql_select_db() to select a specific database to access
3. mysql_query() to send an SQL statement to the server
4. mysql_fetch_array() to retrieve the result set returned by the server

The Recipe Center application uses the main.inc.php include file to retrieve a list of recipe titles, posters, and short descriptions to list on the home Web page, as shown in the image below.

The Recipe Center home page listing recipes in the database

The recipe title is formatted as an HTML link, which links to the index.php Web page, but references the showrecipe include file, which displays the full version of the recipe. When a visitor clicks the link, the full recipe text will appear on the Web page.

Here are the steps for creating the main.inc.php file:

1. Open the main.inc.php template file that you created in the last lesson. It should be in the recipe folder under your WAMP5 www folder.
2. Type the following code into the file:

   Print code

   <h2 align="center">The Latest Recipes</h2><br>
   <?php
   $con = mysql_connect("localhost", "test", "test") or die('Sorry, could not connect to database server');
   mysql_select_db("recipe", $con) or die('Sorry, could not connect to database');
   $query = "SELECT recipeid,title,poster,shortdesc from recipes order by recipeid desc limit 0,5";
   $result = mysql_query($query) or die('Sorry, could not get recipes at this time ');
   if (mysql_num_rows($result) == 0)
   {
      echo "<h3>Sorry, there are no recipes posted at this time, please try back later.</h3>";
   } else
   {
      while($row=mysql_fetch_array($result, MYSQL_ASSOC))
      {
          $recipeid = $row['recipeid'];
          $title = $row['title'];
          $poster = $row['poster'];
          $shortdesc = $row['shortdesc'];
          echo "<a href=\"index.php?content=showrecipe&id=$recipeid\">$title</a> submitted by $poster<br>\n";
          echo"$shortdesc<br><br>\n";
      }
   }
   ?>

3. Save the main.inc.php text file.

Let's walk through the main.inc.php code and see what's going on. First, you may be wondering why it doesn't start out with an <html> or <body> tag. Remember, the index.php code uses the include() function to include the main.inc.php code, so you don't need to worry about using standard HTML tags in the main.inc.php code. It starts off with a simple <h2> tag to display the heading for the section, then dives into the PHP code.

After the opening PHP tag to indicate that we're using PHP code, the program uses the mysql_connect PHP function to connect to the MySQL server:

$con = mysql_connect("localhost", "test", "test") or die('Sorry, could not connect to database server');

The three parameters specified are the hostname of the MySQL server, the userid used to log in to the server, and the password required for the userid.

The hostname value is set to the special hostname localhost if the Apache Web server and the MySQL database server are running on the same server (this is the case if you're using the WAMP5 server). The userid specified is the test user account you created earlier in the course.

PHP includes a nifty little feature called or die. While this sounds ominous, it's really a lifesaver. The die() function provides a way to stop the program and produce a message if a function fails. If the connection attempt fails, rather than displaying an ugly MySQL error message to your visitors, the or die feature allows you to specify a much nicer message that PHP displays instead (also, the PHP program stops processing statements if the or die feature is activated). We'll be using this feature throughout the application to help make things a little more user-friendly.

The result of the mysql_connect function is assigned to a PHP variable called $con. That variable represents the connection, which PHP uses to reference the connection throughout the program.

The next step is to request access to the recipe database through the connection. MySQL allows you to access only one database at a time per connection. Use the mysql_select_db function to specify the database name, along with the PHP variable representing the opened connection. This result doesn't need to be saved in a variable, as PHP remembers the connected database for all future database functions.

After creating the connection and specifying a database, you'll need to create your SQL query. Then you'll assign the query to a PHP variable as a text string.

$query = "SELECT recipeid,title,poster,shortdesc FROM recipes ORDER BY recipeid DESC LIMIT 0,5";


Since we're not looking for specific recipes to display on the home page, we won't use a WHERE clause to restrict the returned rows. We will, however, use the ORDER BY clause with the DESC parameter so recipes are listed as the most recent first. I also threw in the LIMIT clause so that the Web page only displays links for the last 5 posted recipes.

Once you create the query string, it's time to send it to the MySQL server. You do this with the mysql_query function:

$result = mysql_query($query) or die('Sorry, could not get recipes at this time ');

Again, we use the or die function to display a simple error message if the query fails. If the query is successful, the $result variable contains information about the result set returned by the query (but not the actual data yet).

You can perform several functions with the information contained in the $result variable. The program uses the mysql_num_rows() PHP function to check how many rows of data the server will return from the query. If no rows are returned, then there's no data in the query result.

If there's data in the result set, you need to perform another step to extract it. The mysql_fetch_array() function retrieves the result set from the query and places it into an array variable.

This function uses two parameters: the variable that contains the result set information, and a constant value that defines how the array is referenced:

$row = mysql_fetch_array($result, MYSQL_ASSOC);

The constant values are:

• MYSQL_ASSOC references data fields by name
• MYSQL_NUM references data fields by number, starting with 0

It's much easier to use the MYSQL_ASSOC method, which allows you to reference array elements by their data field name. Thus, the variable $row ['recipeid'] contains the value of the recipeid data field for the record.

The mysql_fetch_array function steps through the result set one record at a time each time the program calls it. When it reaches the end of the returned data, it produces a False value. This is a perfect scenario for using the while() function.

We use the while() function to loop through all of the data records in the result set. When you've reached the end of the result set, the mysql_fetch_array function returns a False value, which ends the while loop.

Within the loop, each data field from the record is assigned to a PHP variable (this isn't required, but it helps make handling data easier within the program). Each PHP variable is named according to the data field value it holds.

The program displays data in the Web page using two echo statements:

echo "<a href=\"index.php?content=showrecipe&id=$recipeid\">$title</a> submitted by $poster<br>\n";
echo"$shortdesc<br><br>\n";


The first echo statement creates an HTML link tag on the Web page. It uses the content HTML variable from the index.php page to point to the showrecipe.inc.php page as the next page to display in the main cell. It also adds an HTML variable called id, which points to the recipeid value of the data record. This trick allows us to pass the recipeid value to the showrecipe Web page. It uses the title value of the data record as the actual link text.

The first echo statement also displays the poster value in the data record to identify who posted the recipe. The second echo statement displays the shortdesc value of the data record, so a brief description appears under the HTML link.

The while() loop that contains the echo statements iterates through the data records until the mysql_fetch_array function returns a False value, displaying the information within the main cell of the index.php Web page. If visitors click any of these links, they are taken to the showrecipe.inc.php page, which we'll work on next.