Jump to content


Photo

SQL Query Syntax


  • Please log in to reply
3 replies to this topic

#1 wardo

wardo
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 17 August 2006 - 03:16 PM

I have the follwoing query that returns the results from a database if any of the search fields (department,name,title,color, classification) are filled in:

$sql = "SELECT name, title, id,reviewdate,date FROM upload WHERE (department = '$listname' OR name LIKE '%$name%' OR title LIKE '%$name%' OR colour LIKE '%$colour%' OR classification LIKE '%$classification%') ORDER by title asc";

This works fine but how do I return the results if someone searches, for example, for a specifit name within a specific department or a name with a specific classification etc.

So basically, I need to be able to return the results if someone enters information into just one search criteria and also if they enter data into multiple search criteria.

Thanks for your help.
www.realdesignnetwork.com

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 August 2006 - 04:12 PM

Well, if you want to satisfy multiple criteria, you need to use ANDs... or multiple passes... or rank the results afterwards yourself.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 wardo

wardo
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 18 August 2006 - 01:37 PM

I have tried simplifying the query and using ANDs but it does not work how I want. I now have:

$sql = "SELECT name, title, id,reviewdate,date FROM upload WHERE (department = '$listname' AND name LIKE '%$name%') OR (department = '$listname' OR name LIKE '%$name%')ORDER by title asc";

This gives the same results as before. Is there a way to modify the statement to do what I need or should I use php if statements to determine which sql statement to run under different circumstances?

Thanks.
www.realdesignnetwork.com

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 August 2006 - 04:10 PM

Ah, well yes, if you want to have the option to do either, you'll have to separate the queries, or somehow "mark" each result.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users