Need help on WHERE Clause

[CrashOverride]

Okie I have a form that a User submits.

 

the Form includes things like Username, bike year, bike color, email etc...

 

I want to run a Query to SELECT * FROM users_table WHERE

 

This is where im stuck.

 

It has to grab all the ones ONLY where the fields were entered.

 

ie.

 

say you entered a user name and bike year. I need it to select all matching results from these inputs but ignore the ones that the user didnt fill out.

 

Any ideas???

 

Thanx

[CrashOverride]

does this look kinda right. will it do what I want it to do..

 

$sql = "SELECT userid, username, user_firstname, user_lastname, user_email, user_bike_year, user_bike_make, user_bike_model, user_bike_color, user_bike_engine, user_bike_suspension, user_bike_bodymods, user_bike_futuremods, user_bike_performancemods



FROM users

   

WHERE username = \'$search_username\'

   

OR user_lastname = $search_lastname

   

OR user_email =  \'$search_email\'

   

OR user_bike_year = \'$search_bike_year\'

   

OR user_bike_make = \'$search_bike_mfg\'

   

OR user_bike_model = \'$search_bike_model\'

   

OR user_bike_color = \'$search_bike_color\'

   

OR user_bike_mods = \'$search_bike_mods\'

   

ORDER BY username DESC ";

[Barand]

If the user enters, say, \'Harley\' and \'Black\' then a query like that will give

 

Harley | Black

Harley | Blue // the mfg condition is true

BMW | Black // the color condition is true

 

Using OR will give a match if any of the conditions are met. I\'m guessing that you really want only the Black Harleys listed?

 

To do this you need

 

SELECT .... WHERE 

user_bike_make = \'$search_bike_mfg\' 

AND

user_bike_color = \'$search_bike_color\' 

 

Which means you have to test which fields were entered and include just those in the WHERE clause of the query.

 

hth

[CrashOverride]

Yeha I thought about that after I posted that.

 

What about this.

 

Its all SQL within a variable

 

or is my logic a tad off. If I just use the

 

WHERE

user_bike_make = \'$search_bike_mfg\'

AND

does this mean that if the user didnt select a bike_mfg it wouldnt add anything to it. The way I see that in my head if the user didnt submit that would be WHERE user_bike_make = \'NULL\' AND

 

will this not cause an error or something

 

$sql = "SELECT userid, username, user_firstname, user_lastname, user_email, user_from, user_bike_year, user_bike_make, user_bike_model, user_bike_color, user_bike_engine, user_bike_suspension, user_bike_bodymods, user_bike_futuremods, user_bike_performancemods



FROM " . USERS_TABLE . "

     

WHERE user_name !=". \'\'. (($search_time)? " AND user_name =".$username : \'\' );  	

if ($search_lastname>\'\') $sql .= " AND user_lastname=".$search_lastname;



if ($search_email>\'\') $sql .= " AND user_email=".$search_email;

     

if ($search_location>\'\') $sql .= " AND user_from=".$search_location;

     

if ($search_bike_year>\'\') $sql .= " AND user_bike_year=".$search_bike_year;

     

if ($search_bike_mfg>\'\') $sql .= " AND user_bike_make=".$search_bike_mfg;

     

if ($search_bike_model>\'\') $sql .= " AND user_bike_model=".$search_bike_model;

     

if ($search_bike_color>\'\') $sql .= " AND user_bike_color=".$search_bike_color;

     

if ($search_bike_engine>\'\') $sql .= " AND user_bike_engine=".$search_bike_engine;

     

if ($search_bike_mods>\'\') $sql .= " AND user_bike_mods=".$search_bike_mods;      

$sql .= " ORDER BY user_lastname";

[Barand]

No.

 

That way, if any of the user entries are blank the you only get a match if the corresponding field in the record is also blank.

 

You will get away with it for text fields if you use \'LIKE\':-

 

\" ... AND bike_suspension LIKE \'%$suspension%\' ... \"

 

if $suspension is blank then you get all values of suspension

 

 

Generally, though, If the user doesn\'t enter a value, leave it out of the where clause of the query