[SOLVED] Help with SQL statement or Logic

[dmccabe]

Ok so I have a form, where a user can select 1 or more values to search by.

 

Any 1 or all can be set.

 

so how can I write 1 query that will return the results, based on what the user selects

 

This is what I tried:

 

$query = "SELECT * FROM `tbl_vehicles` WHERE `make_id` = '$make_id' AND `colour_id` = '$colour_id' AND `model_id` = '$model_id' AND `status_id` = '$status_id' AND `location_id` = '$location_id'";

 

But regardless of what the user selects nothing is returned.  If they select 1 or more options, it finds nothing.

[premiso]

You said it in your topic...switch AND to OR

 

=)

[dmccabe]

Well that get's it close but not quite.

 

For example I have 2 records in my database:

 

---Make --- Model ---- Colour

Vauxhall----Corsa-----Black

Chrysler----Voyager---Black

 

so if I use or and Select "Vauxhall" and "Black", both those records appear even through one is Chrysler and not Vauxhall

 

[premiso]

It is because they both have black in them.

 

How exactly do you imagine this working? Only return where both statements are true if they defined them?

 

<?php
$where = "WHERE ";
$where .= (isset($make_id) && !empty($make_id))?"`make_id` = '" . $make_id. "' AND ":'';
$where .= (isset($colour_id) && !empty($colour_id))?"`colour_id` = '" . $colour_id. "' AND ":'';
$where .= (isset($model_id) && !empty($model_id))?"`model_id` = '" . $model_id. "' AND ":'';
$where .= (isset($status_id)  && !empty($status_id))?"`status_id` = '" . $status_id. "' AND ":'';
$where .= (isset($location_id) && !empty($location_id))?"`location_id` = '" . $location_id. "' AND ":'';

$where = substr($where, 0, -4);

$query = "SELECT * FROM `tbl_vehicles` " . $where;
?>

 

Try that out.

 

Edit fixed syntax of single quote.

[ngreenwood6]

I am not really good with this but I think you would have to do something like this:

 

$query = "SELECT * FROM `tbl_vehicles` WHERE `make_id` = '$make_id'";

if(!empty($colour_id))
{
$query .= " AND  'colour_id' = '$colour_id'";

 

Syntax may be wrong. His definitely looks better than mine.

[dmccabe]

Thanks all for your help!

 

Premiso, that worked beautifully   Thank you!