Using data to refine search

[squigs]

Hello,

I'm creating an inventory search page and after the primary search a list of categories with a count is displayed which the user can click to refine the search. For this I use a query like this:

 

$refineBrandSQL = mysql_query("SELECT a.*, COUNT(*) AS numbers
                                    FROM inventory a
                                    WHERE item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' OR a.sub_category_b 
                                    LIKE  '%$searchTermDB%'
                                    GROUP BY brand");		
	$refineBrand = $refineBrandSQL or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$refineSQL}") ;
                if (mysql_num_rows($searchResult) > 0) {
                 echo "<div class='headerClass3'>Brand</div>";
	 while ($make = mysql_fetch_array($refineBrand)){
	$brand = "";
	$brand .= ''. $make['brand'] .' ('. $make['numbers'] .')<br />'; 
	echo "<div class='left_pad'>" .$brand. '</div>';
	}
                }

 

This outputs results such as:

Nike(12)

Addidas(7)

 

What I'm looking for now is to create a link perhaps out of these results that will select the counted results. so if the user clicks Nike(12) all the 12 results for Nike will be displayed. are there any ideas on how to accomplish this? Thank you

[joel24]

this should probably be in the php section,

you'll need something like

 

search.php

if (isset($_GET['brand']) {
$brand = mysql_real_escape_string($_GET['brand']);
$whereClauseXtra= "AND brand='$brand'";
} else {
$whereClauseXtra = '';
}

$refineBrandSQL = mysql_query("SELECT a.*, COUNT(*) AS numbers
                                    FROM inventory a
                                    WHERE item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' OR a.sub_category_b $whereClauseXtra
                                    LIKE  '%$searchTermDB%'
                                    GROUP BY brand");		
	$refineBrand = $refineBrandSQL or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$refineSQL}") ;
                if (mysql_num_rows($searchResult) > 0) {
                 echo "<div class='headerClass3'>Brand</div>";
	 while ($make = mysql_fetch_array($refineBrand)){
	$brand = "";
	$brand .= "<a href='search.php?brand={$make['brand']}'>". $make['brand'] .' ('. $make['numbers'] .')</a><br />'; 
	echo "<div class='left_pad'>" .$brand. '</div>';
	}
                }

 

[squigs]

I was playing with that sort of thing last night. Unfortunately I can't seem to get the WHERE clause doing what I expect. When I add that line to the query it seems to ignore it completely even though $_GET['brand'] is definitely set.

[joel24]

Sorry, I left in your original where condition, and I should've enclosed the OR terms in brackets.

have a play around with it - you should get the general idea now

and echo the search query so you can have a good look at whats executing.

if (isset($_GET['brand']) {
$brand = mysql_real_escape_string($_GET['brand']);
$whereClauseXtra= "AND brand='$brand'";
} else {
$whereClauseXtra = '';
}

$refineBrandSQL = mysql_query("SELECT a.*, COUNT(*) AS numbers
                                    FROM inventory a
                                    WHERE (item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' OR a.sub_category_b LIKE  '%$searchTermDB%')
$whereClauseXtra                         
                                    GROUP BY brand");


//echo query for debug
echo "SELECT a.*, COUNT(*) AS numbers
                                    FROM inventory a
                                    WHERE (item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' OR a.sub_category_b LIKE  '%$searchTermDB%')
$whereClauseXtra                         
                                    GROUP BY brand";









$refineBrand = $refineBrandSQL or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$refineSQL}") ;
                if (mysql_num_rows($searchResult) > 0) {
                 echo "<div class='headerClass3'>Brand</div>";





while ($make = mysql_fetch_array($refineBrand)){





$brand = "";





$brand .= "<a href='search.php?brand={$make['brand']}'>". $make['brand'] .' ('. $make['numbers'] .')</a><br />'; 





echo "<div class='left_pad'>" .$brand. '</div>';





}
                }

[squigs]

Sorry, I left in your original where condition, and I should've enclosed the OR terms in brackets.

have a play around with it - you should get the general idea now

and echo the search query so you can have a good look at whats executing.

 

Thanks Joel, useful info there. I'm one step further along with it now.

Cheers!