Displaying the Products in each Department


Introduction.  Getting the products in each department to display is also not likely as easy as you might think.  We are creating a site where things like the products, if not the departments, are constantly changing.  We do not want to create a page that lists the products for each department.  We need a page that will display the products in a department dynamically, that is as the information within our database changes.

We also have some other major things to consider.

  • make sure that the department selected is where the shopper goes next
  • make sure that the products in the department selected will display as they go
    • remembering that these products and their department assignments can be changing all the time

Now you need to copy the following code and name it department_products.php.  This code is somewhat more involved because we need to make it depend on the department the client selected and display all of the products within the department.

After this code we give a brief discussion of its overall structure and content.


<!-- department_products.php - Displays the products in each department -->
<head><title>The Firefox Products within a Department Page</title></head>
<table align = "center" border = 0 cellpadding="5">

// obtaining the id of the department
// that was selected on departments.php

$id_department = $_REQUEST["id_dept"];

// assign the table for database access
$table_name = "department";

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

// obtaining the name of the department
while ($row = mysql_fetch_array($result_set))

<tr >
<td align=center colspan=2><font size=6 color="A66838"><i><b><?php echo $row[department_name]; ?></i></b></font><br></td>
<td align=center colspan=2><font size=4 color="c0c0c0"><?php echo $row[department_description]; ?></font><br></td>


// constructing the query string to retrieve
// the products within this department

$query_prod_string = "SELECT * FROM products, department_products WHERE products.id_product = department_products.id_product AND department_products.id_department = $id_department";
$result_set = mysql_db_query($db_name, $query_prod_string, $link);
// looping through the resultset appending a new row for each record
while ($row = mysql_fetch_array($result_set))

<td align=right valign=middle><font size=4> <a href="product.php?id_prod=<?php echo $row[id_product]; ?>"><?php echo $row[product_name]; ?> </a></font></td>
<td align=center valign=middle><font size=4> <a href="product.php?id_prod=<?php echo $row[id_product]; ?>"><img src="images_prod/sm_<?php echo $row[product_image]; ?>" border=0> </a></font></td>



As usual 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 start the table used for display.
  • Now we make use of the builtin $_REQUEST class/object to obtain the id_department that was selected in the previous page listing all the departments.  This is assigned to $id_department.
  • Then we establish all the characteristics required to connect to our database.
  • We create the query string and execute it to create our resultset consisting of the single department selected by the shopper in department.php.
  • Then we display the name of this department in large letters in the first row of our table.
  • Then we construct another query string to determine which products are in this department by making use of the department_products table and then using this to query the products table.
    • notice the fairly complicated WHERE clause in the query string
    • notice we didn't need to create another link to the database.
  • We then loop through this resultset while it has more entries appending a new row to the table for each product in the department.
    • within this loop we
    • create links to product.php that will display the product next selected by the shopper carried within the department based on
      • product_name
      • product_image
    • but the actual product will be identified in the subsequent product.php by using the id_prod that is passed in the URL.
      • this id_prod is obtained from the products table using the id_product field
    • notice how the path to the images is constructed
  • Then we close the link to the database.

More will be said about this after you upload this and get it to work.  Passing extra arguments in the URL is making use of a GET approach rather than the POST we have used so far to pass information to a subsequent PHP.

If we were clicking on product numbered 4 in department 2, the URL will look like the following on my computer.


The 4 is obtained from the products table in your database and appended to the URL in the <a> anchor tag by echoing it within a PHP scriptlet.

You will get used to such trickery!