Select and Display Data

The most difficult part of selecting and displaying data is deciding the order in which you want to see it! Remember, the SELECT syntax is

 SELECT [field names] FROM [table] WHERE [expression] ORDER BY [fields]; 

To view all the records in the MY_PRODUCTS table ordered by ITEM_ID, the SQL statement would look something like this:

 $sql = "SELECT ITEM_ID, ITEM_TITLE, ITEM_DESC, ITEM_PRICE FROM MY_PRODUCTS ORDER BY ITEM_ID ASC"; 

To view the records in MY_PRODUCTS ordered by ITEM_PRICE, from highest price to lowest, use this SQL statement:

 $sql = "SELECT ITEM_ID, ITEM_TITLE, ITEM_DESC, ITEM_PRICE FROM MY_PRODUCTS ORDER BY ITEM_PRICE DESC"; 

To view all the records in MY_PRODUCTS that have a price greater than $10.00, use a WHERE clause in your SQL statement:

 $sql = "SELECT ITEM_ID, ITEM_TITLE, ITEM_DESC, ITEM_PRICE FROM MY_PRODUCTS WHERE ITEM_PRICE > 10.00 ORDER BY ITEM_ID ASC "; 

Using the basic connection code found earlier in this chapter, create a PHP script called display_products.php and use any of the sample SQL statements just shown as the value of the variable $sql.

Here's a complete sample script, using a SQL statement that shows all records in the MY_PRODUCTS table ordered by ITEM_ID:

 <?php // create connection; substitute your own information $conn = mysql_connect("localhost","joeuser","34Nhjp") or die(mysql_error())"; // select database; substitute your own database name $db = mysql_select_db("MyDB", $conn) or die(mysql_error()); // create SQL statement $sql = "SELECT ITEM_ID, ITEM_TITLE, ITEM_DESC, ITEM_PRICE FROM MY_PRODUCTS ORDER BY ITEM_ID ASC"; // execute SQL query and get result $sql_result = mysql_query($sql,$conn) or die(mysql_error()); // start results formatting echo "<TABLE BORDER=1> <TR> <TH>Item ID</TH> <TH>Item Title</TH> <TH>Item Description</TH> <TH>Item Price</TH> </TR>"; // format results by row while ($row = mysql_fetch_array($sql_result)) {              $item_id = $row["ITEM_ID"];              $item_title = $row["ITEM_TITLE"];              $item_desc = $row["ITEM_DESC"];              $item_price = $row["ITEM_PRICE"];              echo "<TR>              <TD>$item_id</TD>              <TD>$item_title</TD>              <TD>$item_desc</TD>              <TD align=right>$item_price</TD>              </TR>"; } echo "</TABLE>"; ?> 

Some sample results are shown in Figures 4.8 through 4.10. Your results will differ, depending on the products you inserted into your table.

Figure 4.8: View records ordered by ITEM_ID (ascending)

Figure 4.9: View records ordered by ITEM_PRICE (descending)

Figure 4.10: View records with ITEM_PRICE greater than $10.00

This chapter has provided you with the basics of database connectivity, table and record creation, and record display. Part of Chapter 7, "Advanced PHP Techniques: e-Commerce," is devoted to taking these processes one step further. In that chapter, you'll learn to add record update and deletion functions in the context of creating your own database administration system.