Mysql criterion query

Got question. If i make query to server i use something like this:

 mysql_query("select * FROM test WHERE car = '$car'); 

If i make query to server and use two criterions i use something like this:

mysql_query("select * FROM test WHERE car = '$car' and color ='$color'); 

If i use AND all criterion's must be full filled. But sometimes i dont know second criterion and it wont work. I could use OR but then if i know both criterion's then it wont give me accurate data.

So do i have to make somekind variable between car = '$car' (variable) color ='$color' and use If statement. Something like this- If car = 'all' then variable = OR. So when i dont know car criterion i leave it "All" and i know Color criterion and select it "Blue" then all cars that are blue will be displayed. "All" is like all criterion's.

Any link or tips for this kinda thing would help

Thank you

Hopefully this helps you understand what im trying to do.

Got database where are data about cars. Color column where are car colors. Type column where coupe, suv, sedan. fuel column where are petrol, Diesel, flintstone.

For showing data i made form in html that got 3 dropdown lists and submit button

1. Color-all, blue, green, red.
2. Type- all, coupe, suv, sedan.
3. 开发者_如何学编程Fuel- all, Petrol, Diesel, flintstone.

Now when i select

1.color- blue. 2.type- all. 3.fuel- all.

are showed all cars that are blue, doesn't matter if they are suv, coupe, diesel and so on... (flintstone just example)

but when i select

1.color- blue 2.type- coupe 3.fuel- all

and submit

then all fuel type coupes that are blue will be displayed.

I know how to display all data. I know how to display data that fulfill all criterion (using AND). Like

1. color- blue 2.type- coup 3.fuel- diesel

Now all blue coupes that are running on diesel will be displayed. But sometimes i dont know if i want to see blue coupes that runs on diesels or petrol. So if i dont know i leave it "all"

Maybe it explains something but ty.

This code will add "WHERE" conditions based on the known values. It might not be the fastest solution for this particular problem, but it is easily expandable.

** Updated to "implode" method as suggested by Matt. H **

$sql = "SELECT * FROM test";
$conds = array();
if($car) {
  $conds[] = "car = '$car'";
}
if($color) {
  $conds[] = "color = '$color'";
}
// More conditions can be added if needed 

// Creating a WHERE string by imploding:
if(count($conds) > 0) {
  $where = implode(" AND ",$conds);
  $sql .= " WHERE ".$where; 
}  

mysql_query($sql);

If you don't have any conditions, it will just "SELECT * FROM test".

Combination of And Or will do.

WHERE color = '$color' AND (car ='$car' OR '$car' = 'all')

NOTE: As per my knowledge MySQL does not do any short-circuit, so we should check performance if we are inclined for AND Or Combinations. Another alternative is to use IF, but the permutation of criteria different values, sometimes makes it impossible to work with.

Another approach is to create a dynamic query and include only those filters which are required.