PHP Tutorial

The search.php page is called upon to perform searches of the Products table using one of two methods. A search of a product category locates all software titles within that category, given by a matching ItemType value in its record; a keyword search locates all software titles containing that keyword in one of its fields. In both cases a listing is produced of the ItemNumber, ItemTitle, and ItemPrice fields from the matching product records. The item listings are also links to the detail.php page where full information about the product is presented.

When the search.php page is loaded, it has available to it a query string from the page on which the category or criterion search request was made. One of two query strings is available:

One of the first items of business on this page, therefore, is to capture this value from the Request.QueryString Collection that is created when the name/value pair arrives at the server. Normally, one of the two values is available in the Collection. However, in one instance neither value is provided. This happens when the search form is submitted and the criterion field is empty. In this case there is no category name or keyword value available to this page. All of these situations are covered in the following script that appears at the top of the search.asp page.

<?php
$Category = $_GET[Category];
$Criterion = $_GET[Criterion];

if ($Category == "" && $Criterion == "")

{
 header("Location:home.php");
  
}

?>

The query string values are captured as variables Category and Criterion for ease of handling. Also note that if both of these values are null, in the situation described above, then the visitor is immediately redirected to the home.asp page. There are no values to search for.

The main content of this page is coded within the third division of the page (remembering that the first division INCLUDEs the header.inc file and the second division INCLUDEs the menu.inc file). It is within this division that the two search routines appear. The page, therefore, is structured thus:

<?php
$Category = $_GET[Category];
$Criterion = $_GET[Criterion];

if ($Category == "" && $Criterion == "")

{
 header("Location:home.php");
  
}
  
?>


<html>
<head>
	<title>eCommerce Site</title>
	<link href="stylesheetEC.css" rel="stylesheet">
	<<?php require("jscript.inc") ?>
</head>
<body>


<div style="position:absolute; top:0px; left:0px; width:780px; 
  background-color:seagreen; color:white; padding:5px">
  
  <?php
  require("header.inc")
  ?>
  
</div>

<div style="position:absolute; top:75px; left:10px; width:175px">
  
 <?php
  require("menu.inc")
  ?>
   
</div>

<div style="position:absolute; top:75px; left:200px; width:550px">

 if ($Category != "") { 
	  ...code for category search

}

if ($Criterion != "") {
 	  ...code for keyword search
}
</div>
</body>
</html>

If there is a Category value, then the value has been passed to this page and should be used for a category search. If, on the other hand, a Criterion value has been passed to the page, then that type of search should take place.

On product matches to the Category value passed to this page, the matching items are presented in a table. The three fields displayed from the matching records are ItemNumber, ItemTitle, and ItemPrice. The coding begins, then, with the output heading and the column headings over the table values. In the following listing, the Category variable is displayed in the heading to help identify the search results.


if ($Category != "") {
	  <span class="head3">Search</span>results for category 
	  <span class="head3"><?php echo $Category ?></span>:
	  <br>
	  <br>
	  <table border="0" cellpadding="3">
	  <tr>
		    <th>Item Number</th>
		    <th>Item Title</th>
		    <th>Item Price</th>
	  </tr>

The next items to appear on the page are the search results. Each Product record that has an ItemType value matching the Category variable has its ItemNumber, ItemTitle, and ItemPrice fields selected for display. Each product record appears in a separate row of the display table. The code to accomplish this is shown below.



  <?php
    $conn = odbc_connect
  ('Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\PHPTutorial\Ecommerce\databases\ecommerce.mdb','','');
  
  //Issue SQL SELECT Statement
$sql = "SELECT ItemNumber,ItemTitle,ItemPrice FROM Products WHERE ItemType = '$Category' ORDER BY ItemNumber";
  
//Execute the SQL Statement and create a recordset
$rs = odbc_exec($conn, $sql);
  
//Loop through the recordset and display the necessary records
while($row = odbc_fetch_array($rs)) 
	
	{
	$ItemNumber = $row[ItemNumber];
	$ItemTitle = $row[ItemTitle];
	$ItemPrice = number_format($row[ItemPrice],2);
	
echo " <tr style=\"color:seagreen; line-height:8pt\"
        onMouseOver=\"this.style.backgroundColor='lightgreen';this.style.color='darkgreen';
          this.style.cursor='hand'\"
        onMouseOut=\"this.style.backgroundColor='white';this.style.color='seagreen'\"
        onClick=\"location.href='detail.php?ItemNumber=$row[ItemNumber]\" .
          \"&Category=$Category'\">
        <td>$ItemNumber</td>
        <td>$ItemTitle</td>
        <td align=\"right\">$$ItemPrice</td>
      </tr>";
	
	
  	}
	
  	odbc_close($conn);
  
  ?>
	  </table>
<?php

}

?>

The script begins by linking to the eCommerce.mdb database and creating a Recordset Object named RSCategory for retrieving matching records from the Products table. Records are selected in which their ItemType value matches that of the Category variable holding the query string value:

The script iterates through the set of matching records, displaying each in turn within a row of the table. The recordset values are assigned to variables to make the references easier to work with.

Notice that the $ItemPrice variable has the PHP number_format function applied to it so that the display is in dollars and cents. Also, the value is right-aligned in the table cell.

In this table, as in the table of category links in the menu.inc file, the entire table row is a link to the detail.asp page where full product details are provided. These links and their visual effects are produced by coding each <tr> tag to respond to mouse-overs and mouse clicks.

Here again, an in-line style specification sets the default text color and line height for the row. Then embedded JavaScript event handlers dynamically change the visual styles of the row on mouse-over and mouse-out events. Plus, the onClick event produces a URL request for the detail.asp page. The URL is formatted to transmit a query string to the detail.asp page containing the ItemNumber of the product in this row, along with the Category variable that was passed to this page from the search menu. The use of this latter query string item is taken up when discussing the detail.php page.

After all rows of the table have been displayed, the recordset and connection are closed and the closing </table> tag is written. All products in the requested category are displayed in the table with links to the detail.asp page, where full information about the product is provided.

The keyword search functions in about the same way as the category search. The major difference is in the manner of retrieving product records from the Products table. The first part of the script is identical, save for the display of the Criterion value passed through the query string rather than the Category value.

<% If Criterion <> "" Then %>
  <span class="head3">Search</span>results for criterion
  <span class="head3">echo $Criterion</span>:
  <br>
  <br>
  <table border="0" cellpadding="3">
  <tr>
    <th>Item Number</th>
    <th>Item Title</th>
    <th>Item Price</th>
  </tr>

The following code listing shows the search routine for finding all products that contain a value in one of their fields that matches the Criterion value passed to this page from the search menu.



<?php
  
      $conn = odbc_connect
  ('Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\PHPTutorial\Ecommerce\databases\ecommerce.mdb','','');
  
    $sql = "SELECT ItemNumber,ItemTitle,ItemPrice FROM Products WHERE";
    $sql = $sql . " ItemNumber LIKE '%" . $Criterion . "%'";
    $sql = $sql . " OR ItemType LIKE '%" . $Criterion . "%'";
    $sql = $sql . " OR ItemProducer LIKE '%" . $Criterion . "%'";
    $sql = $sql . " OR ItemTitle LIKE '%" . $Criterion . "%'";
   $sql = $sql . " OR ItemDescription LIKE '%" . $Criterion . "%'";
    $sql = $sql . " ORDER BY ItemNumber";
    
 //Execute the SQL Statement and create a recordset
		
  $rs = odbc_exec($conn, $sql);
  
//Loop through the recordset and display the necessary records

 while($row = odbc_fetch_array($rs)) 
	
	{
	
	$ItemNumber = $row[ItemNumber];
	$ItemTitle = $row[ItemTitle];
	$ItemPrice = number_format($row[ItemPrice],2);
	
	
	echo "<tr style=\"color:seagreen; line-height:8pt\"
      onMouseOver=\"this.style.backgroundColor='lightgreen';this.style.color='darkgreen';
        this.style.cursor='hand'\"
      onMouseOut=\"this.style.backgroundColor='white';this.style.color='seagreen'\"
      onClick=\"location.href='detail.php?ItemNumber=$ItemNumber\" .
        \"&Criterion=$Criterion'\">
      <td>$ItemNumber</td>
      <td>$ItemTitle</td>
      <td align=\"right\">$$ItemPrice</td>
    </tr>";
	
	
	}
	
	odbc_close($conn);
  
  ?>
 
  </table>


<?php

}

?>

As you can see, the only real difference in the two scripts is that the keyword search uses a more elaborate SQL SELECT command to locate matching products:

Since this ends up being a very long SELECT statement, it is put together in pieces. Each subsequent piece of the search string is concatenated to the end of the preceding string to build a complete SELECT statement within the SQL variable.

As before, only the ItemNumber, ItemTitle, and ItemPrice fields are retrieved. The search itself takes place across five fields of the records looking for a match to the Criterion value. The criterion string can appear anywhere in the field since the LIKE operator is used for matching. So, if a match is made to a portion of the contents of the ItemNumber, ItemType, ItemProducer, ItemTitle, or ItemDescription field, then that record is selected.

After this recordset is retrieved, the individual fields are displayed as table rows, just like for the category search. Again, links are made out of these rows to issue a URL to the detail.php page, appending a query string that passes the ItemNumber along with the Criterion value, the use of which is discussed on the detail.php tutorial page.



<?php
$Category = $_GET[Category];
$Criterion = $_GET[Criterion];

if ($Category == "" && $Criterion == "")

{
 header("Location:home.php");
  
}
  
?>


<html>
<head>
  <title>eCommerce Site</title>
  <link href="stylesheetEC.css" rel="stylesheet">
  
  <?php
  require("jscript.inc");
  
  ?>
  
</head>
<body>


<div style="position:absolute; top:0px; left:0px; width:780px; 
  background-color:seagreen; color:white; padding:5px">
  
  <?php
  require("header.inc")
  ?>
  
</div>


<div style="position:absolute; top:75px; left:10px; width:175px">
  
 <?php
  require("menu.inc")
  ?>
  
  
</div>


<div style="position:absolute; top:75px; left:200px; width:550px">


<?php If ($Category != "")
{
 ?>
  <span class="head3">Search</span>results for category 
  <span class="head3"><?php echo $Category ?></span>:
  <br>
  <br>
  <table border="0" cellpadding="3">
  <tr>
    <th>Item Number</th>
    <th>Item Title</th>
    <th>Item Price</th>
  </tr>
  
  <?php
  
    $conn = odbc_connect
  ('Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\PHPTutorial\Ecommerce\databases\ecommerce.mdb','','');
  
  //Issue SQL SELECT Statement
		
  $sql = "SELECT ItemNumber,ItemTitle,ItemPrice FROM Products WHERE ItemType = '$Category' ORDER BY ItemNumber";
  
//Execute the SQL Statement and create a recordset
		
  $rs = odbc_exec($conn, $sql);
  
//Loop through the recordset and display the necessary records

 while($row = odbc_fetch_array($rs)) 
	
	{
	$ItemNumber = $row[ItemNumber];
	$ItemTitle = $row[ItemTitle];
	$ItemPrice = number_format($row[ItemPrice],2);
	
	echo "<tr style=\"color:seagreen; line-height:8pt\"
      onMouseOver=\"this.style.backgroundColor='lightgreen';this.style.color='darkgreen';
        this.style.cursor='hand'\"
      onMouseOut=\"this.style.backgroundColor='white';this.style.color='seagreen'\"
      onClick=\"location.href='detail.php?ItemNumber=$row[ItemNumber]\" .
        \"&Category=$Category'\"
      >
      <td>$ItemNumber</td>
      <td>$ItemTitle</td>
      <td align=\"right\">$$ItemPrice</td>
    </tr>";
	
	
	}
	
	odbc_close($conn);
  
  ?>
  
  </table>
  
<?php

}


 if ($Criterion != "")
 
{
?>


  <span class="head3">Search</span>results for criterion 
  <span class="head3"><?php echo $Criterion ?></span>:
  <br>
  <br>
  <table border="0" cellpadding="3">
  <tr>
    <th>Item Number</th>
    <th>Item Title</th>
    <th>Item Price</th>
  </tr>
  
  <?php
  
      $conn = odbc_connect
  ('Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\PHPTutorial\Ecommerce\databases\ecommerce.mdb','','');
  
    $sql = "SELECT ItemNumber,ItemTitle,ItemPrice FROM Products WHERE";
    $sql = $sql . " ItemNumber LIKE '%" . $Criterion . "%'";
    $sql = $sql . " OR ItemType LIKE '%" . $Criterion . "%'";
    $sql = $sql . " OR ItemProducer LIKE '%" . $Criterion . "%'";
    $sql = $sql . " OR ItemTitle LIKE '%" . $Criterion . "%'";
   $sql = $sql . " OR ItemDescription LIKE '%" . $Criterion . "%'";
    $sql = $sql . " ORDER BY ItemNumber";
    
    
    
    //Execute the SQL Statement and create a recordset
		
  $rs = odbc_exec($conn, $sql);
  
//Loop through the recordset and display the necessary records

 while($row = odbc_fetch_array($rs)) 
	
	{
	
	$ItemNumber = $row[ItemNumber];
	$ItemTitle = $row[ItemTitle];
	$ItemPrice = number_format($row[ItemPrice]2);
	
	
	echo "<tr style=\"color:seagreen; line-height:8pt\"
      onMouseOver=\"this.style.backgroundColor='lightgreen';this.style.color='darkgreen';
        this.style.cursor='hand'\"
      onMouseOut=\"this.style.backgroundColor='white';this.style.color='seagreen'\"
      onClick=\"location.href='detail.php?ItemNumber=$ItemNumber\" .
        \"&Criterion=$Criterion'\">
      <td>$ItemNumber</td>
      <td>$ItemTitle</td>
      <td align=\"right\">$$ItemPrice</td>
    </tr>";
	
	
	}
	
	odbc_close($conn);
  
  ?>
 
  </table>


<?php

}

?>


</div>

</body>
</html>