SQL Query Syntax

[wardo]

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:

[code]$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";[/code]

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.

[fenway]

Well, if you want to satisfy multiple criteria, you need to use ANDs... or multiple passes... or rank the results afterwards yourself.

[wardo]

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.

[fenway]

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.