I have a people database that I have a search function. It searches fields for firstname, lastname and nickname. My problem is when I search for all 3 it returns no results. So like if a record has John, Doe and JD and I run a search for "John Doe JD", I get no results, but I do if I search for any of those terms individually. Here is my query, can somebody suggest what I'm doing wrong or a better query?
SELECT pmpUserID, pmpFirstName, pmpLastName, pmpNickName
FROM pmp_people
WHERE (pmpFirstName LIKE '%$searchstring%')
OR (pmpNickName LIKE '%$searchstring%')
OR (pmpLastName LIKE '%$searchstring%')
ORDER BY pmpLastName ASC
$query="blah blah WHERE id != ''";
if($_POSTї'first'] != "")
$query.="&& (pmpFirstName LIKE '%$first%')";
if($_POSTї'last'] != "")
$query.="&& (pmpLastName LIKE '%$last%')
$sql = "SELECT pmpUserID, pmpFirstName, pmpLastName, pmpNickName
FROM pmp_people WHERE (pmpFirstName LIKE '%$searchstring%' AND pmpNickName LIKE '%$searchstring%' AND pmpLastName LIKE '%$searchstring%') ORDER BY pmpLastName ASC"
Seems to me the code that you posted is looking for "The ID's name, lastName, nickname like Searchterm. OR nickname like $searchterm. Or lastName like $searchterm."
I could be wrong, and probably am, but it seems to me like the SELECT doesn't know what to take out after the first LIKE in the code you gave.
xisle:
Umm, that would work if I had 3 different search boxes for each one of the fields I want to search, but I don't. One search text box, searching multiple fields.
SteveO:
No, using AND's instead of OR's returns no results everytime no matter what the search string is.
you really need to split the search query into 3 component, and seeing as some users are retarded, you can allow for what they are going to enter in the box.
Therefor, the best solution i can see is have three entry boxes for the user to type into.
Or just grab the input, expode() it into and array and use a nested loop to check each search term against each field. With a little more code, you could keep track of how many "hits" each one gets and return "rated" results
Bech100:
My site isn't for users! It is for me only. It is a people database for keeping contact information for personal use.
magicrobotmonkey:
I have thought of this and I think that would work. But my query as is, is compary the whole string to each field, and since I'm using the LIKE function for MySQL, I am convinced the solution lies with it. If it doesn't, then exploding the searchstring might be my only choice.
Yea i know what you mean, but i think LIKE is too strong a comparision, you know? If you have more than one search term then the string isn't LIKE the field any more.
You might be able to create an expression which will handle the comparing to each term for you, but I don't have a lot of experience with that sort of thing so I can't help you. I think the nested loop may be your best option here
That's what I am saying, is there another function that is kind of like "LIKE", but not as strict? I wonder ...otherwise explode is probably the way to go..
Pyrite wrote: Here is my query, can somebody suggest what I'm doing wrong or a better query?
Pyrite,
You asked what is wrong and I told you.
You asked for a better query and I suggested one.
now try reading the manual.. http://www.mysql.com/doc/en/index.html