Jump to content


Photo

Need help on WHERE Clause


  • Please log in to reply
4 replies to this topic

#1 CrashOverride

CrashOverride
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 28 August 2003 - 07:41 PM

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

#2 CrashOverride

CrashOverride
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 28 August 2003 - 07:57 PM

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


#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 30 August 2003 - 04:43 AM

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\' ANDuser_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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 CrashOverride

CrashOverride
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 02 September 2003 - 12:50 PM

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


#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 02 September 2003 - 05:49 PM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users