SQL Search Query Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

SQL Search Query Help

Post 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
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post 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%')
Steveo31
Forum Contributor
Posts: 416
Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA

Post 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.
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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.
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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..
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Re: SQL Search Query Help

Post 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
:lol:
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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.
Post Reply