Displaying each Product

 

Introduction.  Getting each product tp display correctly is somewhat more complicated than getting the links for the products in each department to display.  We have some additional considerations.
  • make sure we access the information for the particular product the shopper selected
    • name
    • image
    • description
    • price
  • make sure that we have some input forms for the shopper to select things like
    • quantity to purchase
    • appropriate attributes such as size, color or flavor

In order to get the attributes to display appropriately for each product we slightly modified our table structure from the purely normalized set of tables by adding product_attribute_category.  Adding this table eases the overall structure of our PHP so that we can use two different SQL statements to generate resultsets that can be accessed via nested while loops.

So this PHP will have three SQL strings to run generating three separate resultsets

  • one for basic product information
    • based on products tables
  • one for finding the appropriate attribute categories
    • based on joining product_attribute_category and attribute_category
  • one more for finding the actual attributes within this category for the product
    • based on joining product_attribute and attribute

Now you need to copy the following code and name it product.php.  After the code we give a brief discussion of its overall structure and content.

 

<HTML>
<!-- product.php - Displays a particular product selected by the shopper -->
<head><title>The Firefox Products</title></head>
<?php
include("includes/header.php");
include("includes/connection_config.php");
?>
<br><br>
<table align = "center" border = 0 cellpadding=5 width=550>

<?php
// obtaining the id of the product
// that was selected on department_products.php
$id_product = $_REQUEST["id_prod"];

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string to retrieve information about the product
$query_prod_string = "SELECT * FROM products WHERE id_product = $id_product";
$result_set = mysql_db_query($db_name, $query_prod_string, $link);

// obtaining the information on the product
while ($row = mysql_fetch_array($result_set))
{

// need to put in name, image, price and whatever
// also need to make sure of they put the product in their shopping basket
// we have the id_product
?>
<tr>
<td align=center colspan=2><font size=6 color="A66838"><b><i><?php echo $row[product_name]; ?></i></b></font></td>
</tr>
<tr>
<td align=right valign=middle width=250> <img src="images_prod/<?php echo $row[product_image]; ?>" border=0> </td>
<td align=left valign=middle><br><font size=4 color="c0c0c0"> <?php echo $row[product_description]; ?>
<br><br>$ <?php echo $row[price]; ?> </font></td>
</tr>

<?php
} // ending loop for display of basic product info
?>
<form method="post" action="add_item.php">
<?php
// now we need to display the attribute category
// and attributes for the selected product if they exist
$query_attribute_category = "SELECT * FROM attribute_category JOIN product_attribute_category ON product_attribute_category.id_attribute_category = attribute_category.id_attribute_category WHERE product_attribute_category.id_product = $id_product";
// creating the resultset from the query
$result_set_attribute_category = mysql_db_query($db_name, $query_attribute_category, $link);
// if this query returns a non-empty resultset
// we want to cycle through it to display the attribute categories
// and then the specific attributes in that category for that product
while ($row = mysql_fetch_array($result_set_attribute_category))
{

// now we want to display the attribute category in our table
?>
<tr>
<td align=right><font size=4 color="c0c0c0"><?php echo $row[category_name]; ?>:</font></td>
<td align=left><select style="font-size: 14pt" name="sel_<?php echo $row[category_name]; ?>">
<?php
// we also need to construct a SELECT box
// in HTML containing the appropriate attributes
// first we need to establish the current id_attribute_category
$id_attribute_category = $row[id_attribute_category];
// now we need to construct the SELECT containing all
// the attributes associated with this product in this attribute_category
$query_attribute = "SELECT * FROM product_attribute JOIN attribute ON product_attribute.id_attribute = attribute.id_attribute WHERE (product_attribute.id_product = $id_product AND attribute.id_attribute_category = $id_attribute_category)";
// creating the resultset from the query
$result_set_attribute = mysql_db_query($db_name, $query_attribute, $link);
// if this query returns a non-empty resultset
// we want to cylce through it to display the attribute categories
// and then the specific attributes in that category for that product
while ($row_attribute = mysql_fetch_array($result_set_attribute))
{
?>

<option value="<?php echo $row_attribute[attribute_name]; ?>"><?php echo $row_attribute[attribute_name]; ?></option>

<?php
} // end the loop that creates the select for the actual attributes
?>
</select>
</td>
</tr>

<?php
} // end the loop that displays the attribute categories
mysql_close($link);
?>
<tr>
<td align=right valign=middle><font size=4 color="c0c0c0">quantity:</font></td>
<td align=left><input type="text" size="2" name="txt_quantity" style="font-size: 14pt" value="1">
<input type="hidden" name="id_product" value="<?php echo $id_product; ?>">
</td>
</tr>
<tr>
<td align=center colspan=2><input type="submit" value="Buy" style="font-size: 14pt"></td>
</tr>
</form>
</table>
</BODY>
</HTML>
 
Notice that I have included an HTML comment line in addition to the PHP comments.  You also want to make sure you distinguish our PHP scriptlets from the HTML.  We move back and forth quite a bit.
  • We start by including the header.php. 
  • Notice how HTML is used to give feedback to the user and start the table used for display.
  • Then we obtain the id_product that the user selected using $_REQUEST
  • Then we establish all the characteristics required to connect to our database.
  • We create the query string to get our product information and execute it to create our resultset.
  • We then loop through this resultset while it has more entries appending a new row to the table for each department.
    • within this loop we
    • display the
      • product_name
      • product_image
      • product_description
      • product_price
  • Next we start the form used to collect information from the shopper.
  • We create the query that obtains all the attribute categories associated with this product and obtain the resultset.
  • We loop through these attributes while
    • displaying the name of the attribute_category
    • Create the query to obtain all of the attributes in this category for this product and obtain the resultset.
    • We initialize a <select> control.
    • We loop through this resultset to
      • append these attribute names into the <select> using <option> tags.
  • Once all or none of these are displayed as appropriate, we create a submit button to post the form data to the shopper's basket.
  • Then we close the link to the database.

Again, we have made use of some new tricks that you need to make sure you understand.  You will get used to such trickery!