This query is kicking my @$$!! Insight please?

Trent_Hatred

Ok, been struggling to make this work all day.

It's a query to search a database for products.

On one page, there is a form with 5 fields:

category
subcategory
priceFrom
priceTo
search

When submitted, it sends to another page which processes the request.

What I've been using thus far is:

$search= $_GET[search];
$accType= $_GET[accType];
$priceFrom = $_GET[priceFrom]; 
$priceTo = $_GET[priceTo]; 
$category = $_GET[category]; 
$manufacturer = $_GET[manufacturer]; 

$query = "SELECT * from products_master WHERE $accType ='on' AND " ;
if ($search)
$query .= "description like '%$search%' AND " ;
if ($category)
$query .= "category = '$category' AND " ;
if ($manufacturer)
$query .= "manufacturer = '$manufacturer' " ;

The intent is to search for products based on any an/or all of the above criteria.

I know just from looking at this that ... it's likely coded improperly.

Could someone here either a) help me pick through this or b) point me towards an example that works similar to this?

Forgot to include, when I echo my query it comes out as:

SELECT * from products_master WHERE rst_BCHydro ='on' AND description like '%printers%' OR partnr like '%printers%' OR category like '%printers%' OR subcategory like '%printers%' AND category = '1300' AND manufacturer = 'HP'

Thanks,
B

mairving

Where are the OR's coming from? I don't see them in your query code. AND's and OR's don't play well together unless you put them in parenthesis.

Trent_Hatred

Sorry about that, missed a piece of code. Here's the updated:

$search= $_GET[search];
$accType= $_GET[accType];
$priceFrom = $_GET[priceFrom]; 
$priceTo = $_GET[priceTo]; 
$category = $_GET[category]; 
$manufacturer = $_GET[manufacturer]; 

$query = "SELECT * from products_master WHERE $accType ='on' AND " ;
if ($search)
$query .= "description like '%$search%' OR partnr like '%$search%' OR category like '%$search%' OR subcategory like '%$search%' AND " ;
if ($category)
$query .= "category = '$category' AND " ;
if ($manufacturer)
$query .= "manufacturer = '$manufacturer' " ;

Neumy

I'm not an expert when it comes to sql statements but I see two problems (I could be wrong - but this is how I'd do it):
1) Make sure each variable can end
2) I don't think LIKE uses % on either side - one or the other (but I'm not 100% sure)
I would do something like:

$query = "SELECT * from products_master WHERE $accType ='on'";
if ($search)
$query .= " AND description like '%$search'";
if ($category)
$query .= " AND category = '$category' AND";
if ($manufacturer)
$query .= " AND manufacturer = '$manufacturer' ";
$query .= ";";