After many hours of coding, and picking the brains of all of your folks here, I've finally got a working version of a multiple search query with results displayed via pagination. The code for this was taken from a variety of posts here as well as my own troubleshooting and testing.

For myself, I am using this to perform a query for products based on category, manufacturer, keywords and price between xxx and xxx.

This is likely fairly sloppy code, so if anyone can offer some tips or suggestions on how to improve it, that would be great too. 😉

I'd like to post it here so that it may help others.

The vars are passed from a page previous to this in a standard GET form.

Note The price between search will not work properly on a text/varchar field. Not sure why (perhaps somebody could explain?), but when I changed the price field to decimal, it works just fine.

<?
$Per_Page=20;
// query database to find total number of records to display 
$keywords= $_GET[keywords];
$priceFrom = $_GET[priceFrom]; 
$priceTo = $_GET[priceTo]; 
$category = $_GET[category]; 
$manufacturer = $_GET[manufacturer]; 

// Run The Query Without a Limit to get Total result
$SQL = "SELECT COUNT(*) AS Total FROM products_master WHERE $accType ='on' AND " ;
if ($category)
$SQL .= "category = '$category' AND " ; 
if ($manufacturer)
$SQL .= "manufacturer = '$manufacturer' AND " ; 
if($priceFrom != "" || $priceTo !="") 
	$SQL .= "(price BETWEEN '$priceFrom' AND '$priceTo') AND ";
if ($search)
$SQL .= "(products_master.description like '%$search%' OR products_master.category like '%$search%' OR products_master.subcategory like '%$search%') AND " ;
$SQL= substr($SQL, 0, strlen($SQL)-4) ;

$SQL_Result = mysql_query($SQL);
$SQL_Result_Array = mysql_fetch_array($SQL_Result);
$Total=$SQL_Result_Array['Total'];

// Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)
$SQL = "SELECT * FROM products_master WHERE $accType ='on' AND " ;
if ($category)
$SQL .= "category = '$category' AND " ; 
if ($manufacturer)
$SQL .= "manufacturer = '$manufacturer' AND " ; 
if($priceFrom != "" || $priceTo !="") 
	$SQL .= "(price BETWEEN '$priceFrom' AND '$priceTo') AND ";
if ($search)
$SQL .= "(products_master.description like '%$search%' OR products_master.category like '%$search%' OR products_master.subcategory like '%$search%') AND " ;
$SQL= substr($SQL, 0, strlen($SQL)-4) ;

// Append a LIMIT clause to the SQL statement
if (empty($_GET['Result_Set']))
   {
   $Result_Set=0;
   $SQL.=" LIMIT $Result_Set, $Per_Page";
   }else
   {
   $Result_Set=$_GET['Result_Set'];
   $SQL.=" LIMIT $Result_Set, $Per_Page";
   }
// Run The Query With a Limit to get result
$SQL_Result=mysql_query($SQL);
$SQL_Rows=mysql_num_rows($SQL_Result);
// Display Results using a for loop
for ($a=0; $a < $SQL_Rows; $a++)
    {
    $SQL_Array=mysql_fetch_array($SQL_Result);
    //$Product=$SQL_Array['partnr'];
    //$Description=$SQL_Array['category'];
	?>
			<tr> 	
    			<td align="center" class="table_cell_alt"><? echo "$SQL_Array[partnr]"; ?></td>
    			<td align="right" class="table_cell_alt"><? echo "$SQL_Array[description]"; ?></td>
				<td align="right" class="table_cell_alt"><? echo "$SQL_Array[manufacturer]"; ?></td>
				<td align="right" class="table_cell_alt"><? echo "$SQL_Array[category]"; ?></td>
				<td align="right" class="table_cell_alt">$<? echo number_format($SQL_Array[price], 2); ?></td>					
				<td align="right" class="table_cell_alt" valign="bottom">					
				<a href="cart.php?action=add_item&id=<?php echo $SQL_Array["id"]; ?>&qty=1">
				<img name="addtocart<?php echo $SQL_Array["id"]; ?>" src="../images/add_to_cart.gif" width="90" height="17" alt="" border="0"></a>
				</td></form>
			</tr>	
			<tr>
				<td height="1" colspan="6"><img src="../images/spacer.gif" width="1" height="1" alt="" border="0"></td>
			</tr>
			<?
			}	
			?>
			<tr>
				<td colspan="6">
<?							
// Create Next / Prev Links and $Result_Set Value
if ($Total>0)
   {
   if ($Result_Set<$Total && $Result_Set>0)
      {
      $Res1=$Result_Set-$Per_Page;
      echo "<A HREF=\"$PHP_SELF?Result_Set=$Res1&search=$search&accType=$accType\">Previous Page</A> ";
      }
   // Calculate and Display Page # Links
   $Pages=$Total / $Per_Page;
   if ($Pages>1)
      {
      for ($b=0,$c=1; $b < $Pages; $b++,$c++)
          {
          $Res1=$Per_Page * $b;
          echo "<A HREF=\"$PHP_SELF?Result_Set=$Res1&search=$search&accType=$accType\">$c</A> \n";
          }
      }
   if ($Result_Set>=0 && $Result_Set<$Total)
      {
      $Res1=$Result_Set+$Per_Page;
      if ($Res1<$Total)
         {
         echo " <A HREF=\"$PHP_SELF?Result_Set=$Res1&search=$search&accType=$accType\">Next Page >></A>";
         }
      }
   }
// Close Database Connection
mysql_close();
?>
	&nbsp;&nbsp;<b><? echo $Total ?> Results found</b></td>
</tr>
  				</table>

Cheers, and thanks again for all the help.

B

    Write a Reply...