Questionable Query

[Shaman13]

Can anyone tell me how to rewrite this query to find exact matches instead of all records "like" my search terms. I have tried using = but it doesn't work. I afraid my syntax is wrong but I am relatively new to PHP and frankly I am stumped! Any help and/or suggestions are very much appreciated!

I use this query on a page that allows users to select a search criteria from a list "searchtype" and submit a "searchterm" to query the database with the following query....

$query = "select * from _clientsw_cases where ".$searchtype." like '%".$searchterm."%' and ".$searchtype2." like '%".$searchterm2."%' ";

[CaseyRyan]

If you're looking for exact matches then you have to use the equals (=) sign. Your current sql is always going to display every match.

What I would do is this:
Offer the ability to the user to decide if they want to do a wild card match or exact match. You can allow them to add a * (asterisk) to their searchterm to indicate a wildcard when searching. (you could also get fancy and put a dropdown with choices like "exact match", "starts with", "ends with", "contains".

Option 1: using the * (asterisk):
When the user clicks the search button, first check to see if the searchterm is blank. If it is blank, then exit and say no results were found. This will save you returning all results in your database.

If there is a value, parse the $searchterm to see if it contains a * (asterisk). If an asterisk exists, you will use the LIKE operator and replace all asterisks in the $searchterm with a % (percent sign).

If no asterisks exist, you will use the = (equal sign).

Option 2: using the drop down
Let's call the dropdown $searchMethod

When the user clicks the search button, first check to see if the searchterm is blank. If it is blank, then exit and say no results were found. This will save you returning all results in your database.

If there is a value, check the value of the $searchMethod.

If $searchMethod = "Exact Match", you will use the equals sign.
If $searchMethod = "Starts With", you will use the LIKE operator and put a % (percent sign) at the end of the $searchTerm.
If $searchMethod = "Ends With", you will use the LIKE operator and put a % (percent sign) at the beginning of the $searchTerm.
If $searchMethod = "Contains", you will use the LIKE operator and put a % (percent sign) at the beginning and end of the $searchTerm.

Anyway, I think that should do it. You're putting the ability in the hands of the user. I like the dropdown the most because it offers the most control with the least amount of explanation.

-=casey=-

[baze22]

This should work:

$query = "select * from _clientsw_cases where ".$searchtype."='".$searchterm."' and ".$searchtype2."='".$searchterm2 . "'";

baze

[Shaman13]

BAZE

Thanks a bunch! That worked slick! Is there a way to make searchtype2 optional so if the user fills in only searchtype1 field and leaves searchtype2 field blank the query looks for that term only, but if the user fills in both fields the query looks for both search terms?
Thanks Again!
Have Several Great Days!

[baze22]

To make searchterm2 optional you'd have to do something like:

$optionalsearch = '';
if (trim($searchterm2) <> '') $optionalsearch = " and ".$searchtype2."='".$searchterm2 . "'";
$query = "select * from _clientsw_cases where ".$searchtype."='".$searchterm."'" . $optionalsearch;

baze

[Shaman13]

EXCELLENT! You have got this stuff nailed Baze! That worked too! Now I am really excited! Thanks so much! Have you been programming this stuff for long? You nailed that in no time! Many,many thanks again!