searching with multiple criteria

[gigido]

hi,

 

i have this statement

("SELECT * FROM `listings` WHERE year= \"".$_POST['year']."\" or type =\"".$_POST['type']."\" and title LIKE \"". $_POST['title'] ."\"")

 

now these values are populated by an html form with 2 drop down menus and one title text search field. the issue is getting the results to search for all the entries when the drop down isnt selected. As of now its been searching for value ="" 

I basicaly am trying to give the user 3 ways of searching, and the ability to use all 3 options at once to get more refined results. Im not sure if the "OR" is the right thing to be using in this situation.

 

thanks.

[roopurt18]

I could be mistaken, but AFAIK strings in MySQL begin and end with single quotes while you're using double quotes.

 

In order to make your query search for all titles when one isn't selected, you could make the first item in the drop down look like this:

<option value="%">All Titles</option>

Which will make your query look like

  and title like '%'

 

As for OR or AND, it depends on what you want.  If you want results that meet all conditions use AND.  If you want results that match one or more conditions use OR.  If you want something else use parenthesis to group your conditions with AND or OR between groups.  And if you want to be slick allow the user to decide.

[Jeremysr]

Maybe make the query like this:

 

if (empty($_POST['year'])) { $where_year = "true"; } else { $where_year = "year = '".$_POST['year']."'"; }
if (empty($_POST['type'])) { $where_type = "true"; } else { $where_type = "type = '".$_POST['type']."'"; }
if (empty($_POST['title'])) { $where_title = "true"; } else { $where_title = "title LIKE '".$_POST['title']."'"; }

$sql = "SELECT * FROM `listings` WHERE $where_year AND $where_type AND $where_title";

 

Now all three parts of the WHERE part have to be true, and if a form field is left blank it'll make that part true. Example:

 

SELECT * FROM `listings` WHERE true AND true AND title LIKE 'asdf'

[gigido]

perfect, thanks for the insight.... very helpful.

[wmguk]

hey, this is the sort of thing i would be looking for, however my information is - one field called orderdate (timestamp) so how would i be able to search for just orders in say 2008, or just march?