Jump to content

Archived

This topic is now archived and is closed to further replies.

CrashOverride

Need help on WHERE Clause

Recommended Posts

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

Share this post


Link to post
Share on other sites

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 ";

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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";

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.