Page 1 of 1
SQL Search Query Help
Posted: Wed Apr 07, 2004 10:50 am
by Pyrite
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?
Code: Select all
SELECT pmpUserID, pmpFirstName, pmpLastName, pmpNickName
FROM pmp_people
WHERE (pmpFirstName LIKE '%$searchstring%')
OR (pmpNickName LIKE '%$searchstring%')
OR (pmpLastName LIKE '%$searchstring%')
ORDER BY pmpLastName ASC
Posted: Wed Apr 07, 2004 11:09 am
by xisle
if you echo the $query, you will see that you are looking for
Code: Select all
WHERE (pmpFirstName LIKE 'John Doe JD')
OR (pmpNickName LIKE 'John Doe JD')
OR (pmpLastName LIKE 'John Doe JD')
That will return zero results. Try breaking your search into first, last , etc. and making the query dynamic
Code: Select all
$query="blah blah WHERE id != ''";
if($_POSTї'first'] != "")
$query.="&& (pmpFirstName LIKE '%$first%')";
if($_POSTї'last'] != "")
$query.="&& (pmpLastName LIKE '%$last%')
Posted: Wed Apr 07, 2004 11:09 am
by Steveo31
I haven't used the LIKE operator much before, but how about this:
Code: Select all
$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.
Posted: Wed Apr 07, 2004 11:16 am
by Pyrite
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.
Posted: Wed Apr 07, 2004 11:22 am
by JayBird
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.
You need to spoon feed users these days.
Mark
Posted: Wed Apr 07, 2004 11:42 am
by magicrobotmonkey
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
Posted: Wed Apr 07, 2004 12:09 pm
by Pyrite
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.
Posted: Wed Apr 07, 2004 1:15 pm
by magicrobotmonkey
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.
http://www.mysql.com/documentation/maxd ... ontent.htm
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
Posted: Wed Apr 07, 2004 3:47 pm
by Pyrite
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..
Re: SQL Search Query Help
Posted: Thu Apr 08, 2004 12:19 pm
by xisle
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

Posted: Thu Apr 08, 2004 9:37 pm
by Pyrite
I did. But it looks like the explode method is going to have to suffice for now. Thanks for your help guys, appreciate it.