Search query with mysql

iBuddy

Hi guys. If I have 3 variables to search what is the best way to show the results based on what the user searches for?

3 fields are:

-case number
-first name
-last name

All fields are optional so that is why I am hitting a road block. Using OR queries is not showing the right results either because some fields are NULL.

Let's say I want to search for first name John, 5 results are shown. If I search for case number 123456 and first name John I am shown the same results instead of just one.


$sql = mysql_query("select * from subpoena where case_number = '".$p_case."' OR name_first = '".$p_fname."' OR name_last = '".$p_lname."' ");

Any thoughts? thanks!

bradgrafelman

iBuddy;11031513 wrote:
If I search for case number 123456 and first name John I am shown the same results instead of just one.

That's because you aren't search for "case number 123456 and first name John"; instead you're searching for "case number 123456 or first name John." If you want the different conditions to be AND'ed together, then use 'AND' - not 'OR' - in the query.

However, since not all search criteria are required, you'll probably want to manually build up the SQL query to AND the conditions together only if the user actually specified something in the corresponding search field.