simple mysql select problem

[jasonindus]

just a small select problem.. here's the full query

$s_sql = "SELECT *, COUNT(item_id)
FROM 04_items
WHERE item_type = '$item_type'
AND post_code LIKE '$location%' OR address5 LIKE '$location%' OR town_city LIKE '$location%'
AND price BETWEEN '$pricelow' AND '$pricehigh'
AND beds >= '$bedrooms'
AND building_type = '$building_type'
AND supplier_id = '1004'
AND deleted <> 'remove'
GROUP BY item_id having COUNT(item_id)>=1
ORDER by price
";

AND building_type = '$building_type' ...is the part of the query thats giving me trouble... i would like to include a SELECTED option 'any' so that if someone doesnt select an option in the drop down then the query searches for all building_type's in the database...

hope i'm making sense... can anyone help?

 

[Itshim]

Instead of creating the sql statement all in one shot, split it up so you can add an if statement. Then in the if statement check the building type and only add the 'AND building_type' line if needed. See example below:

$s_sql = "SELECT *, COUNT(item_id)
FROM 04_items
WHERE item_type = '$item_type'
AND post_code LIKE '$location%' OR address5 LIKE '$location%' OR town_city LIKE '$location%'
AND price BETWEEN '$pricelow' AND '$pricehigh'
AND beds >= '$bedrooms' ";

if ('any' != $building_type) {
    $s_sql .= "AND building_type = '$building_type' ";
}

$s_sql .= "AND supplier_id = '1004'
AND deleted <> 'remove'
GROUP BY item_id having COUNT(item_id)>=1
ORDER by price
";

 

[jasonindus]

thanks Itshim... it worked and explains alot for some of my other queries!!