Search Dilemma

paulkenn

I have built a simple search feature for selecting clients where one may search by last, first (name), company, city or state.
The problem arises when someone searches, for instance, the city "Hartford". The search results displays all of the cities with the name Hartford in it. (East Hartford, West Hartford, etc).

How do I setup the query to only return results that match the keyword entered by the user i.e. only "Hartford"?

Here's a snipet of the code so far:
$query = "SELECT * FROM client";
$first_one = 1;
if($clientl != "") {
if($first_one == 1) {
$query = "$query WHERE";
$first_one = 0;
$query = "$query clientl LIKE '%$clientl%'";
}
else {
$query = "$query AND clientl LIKE '%$clientl%'";
}
}
if($city != "") {
if($first_one == 1) {
$query = "$query WHERE";
$first_one = 0;
$query = "$query city LIKE '%$city%'";
}
else {
$query = "$query AND city LIKE '%$city%'";
}
}

Any help is appreciated!!

justsomeone

 $query = "$query city LIKE '%$city%'";

The LIKE usage above is telling mysql to do a wildcard search, providing exactly the results you describe.

try:

 $query = "$query city = '$city'";

This should only match "Hatford" with "Hatford". Note, it will return nothing (as would your original code) if your user inputs "hatford" - case matters. MySQL will help you here too by allowing you to convert both sides of the = to the same case, eg: (typing this from memory here, some debugging may be required)

 $query = "$query UPPER(city) = UPPER('$city')";

now an input of "hatfield" will match data of "Hatfield", "hatfield", "HATFIELD" or even "hAtfiELd".

superwormy

MySQL isn't actually case sensitive, so you don't need the UPPER hack for MySQL ( though its good for compatibility anyways )

Do you live in Hartford? I live in Coventry, only about 30 mintes away :-)

krotkruton

Also, just in case you want something that is kinda easier to read, although that is arguable, your two lines:

$query = "SELECT * FROM client"; 
$query = "$query WHERE";

are the same as:

$query = "SELECT * FROM client"; 
$query .= " WHERE";

the ".=" operator appends the new statement to the previous statement

justsomeone

superwormy wrote:
MySQL isn't actually case sensitive, so you don't need the UPPER hack for MySQL ( though its good for compatibility anyways )

Do you live in Hartford? I live in Coventry, only about 30 mintes away :-)

Would you believe that over 6 or 7 years developing on and off in MySQL, I had always assumed it was case sensitive. I wonder if that's a config setting someplace.

And no, I'm not very local to ya, I'm in Dublin. 😉

paulkenn

Thanks guys, that resolved the problem! Appreciate the help.

(Glastonbury, close to Hartford :-)