Page 1 of 1
Help with query - matching multiple (exclusive) search terms
Posted: Fri Oct 15, 2010 4:08 pm
by mecha_godzilla
Hello,
I have a form that contains three fields:
First Name
Surname
Client ID
These three fields relate to three identical fields (first_name, surname and client_id) of the same table in my DB (I'm using MySQL, BTW).
The query I've got so far looks like this:
Code: Select all
SELECT first_name, surname, client_id
FROM client_records
WHERE first_name LIKE '%$first_name%' OR surname LIKE '%$surname%' OR client_id LIKE '%$client_id%'
ORDER BY surname ASC
So, if you enter "david", "smith" and "55" in the three fields my query matches any records that contain one or more of those values, but what I need to do is make sure that if you enter "david", "smith" and "55" it will only display a result that matches all three values exactly. If the user doesn't know (for example) the first name or the client ID and just enters "smith" as the surname, it still needs to match this record as well as any others with the same surname.
I've already looked at this page
http://dev.mysql.com/doc/refman/5.1/en/ ... earch.html
but I couldn't see anything obvious to solve my problem. Another feature of the search form is that if all fields are left blank then it needs to display all the records. This works with the query that I'm using (because it just has to match '') but fails when I try to use things like MATCH in the query, etc.
Thank you in advance o knowledgeable ones,
Mecha Godzilla
Re: Help with query - matching multiple (exclusive) search t
Posted: Fri Oct 15, 2010 4:23 pm
by Eran
I'm having a little difficulty understanding what you are trying to do. It seems to me you want exact matches on those fields (or at least - starting with the same string) yet you are using a wildcard search. Regarding your other requirement - to match multiple filtering fields use an 'AND' not an 'OR' and put only the parameters that are not empty.
Re: Help with query - matching multiple (exclusive) search t
Posted: Fri Oct 15, 2010 4:45 pm
by mecha_godzilla
Thanks for the quick response.
The query that I posted is just what I've got so far - I'm not suggesting it's right! I still want wildcard matches on all the fields but the difference is that I want the search to become more restricted the more information the user enters and not to display results that just match some of the values.
As you suggest, one way to solve this might be to amend the query to only match against fields that have been completed. I prefer to use hard-coded queries rather than piece them together in the script but maybe I'm being over-cautious

I'd also like to move my queries over to stored procedures at some point so I'm not sure whether I could use this approach.
If anyone has any other suggestions please let me know.
Thanks,
M_G
Re: Help with query - matching multiple (exclusive) search t
Posted: Fri Oct 15, 2010 5:17 pm
by mecha_godzilla
Ok, I've got it now...
I amended my query so it looks like
Code: Select all
SELECT first_name, surname, client_id
FROM client_records
WHERE first_name LIKE '%$first_name%' AND surname LIKE '%$surname%' AND client_id LIKE '%$client_id%'
ORDER BY surname ASC
then added some code before it to initialise the values if they're empty
Code: Select all
if ($first_name == NULL) $first_name = '';
if ($surname == NULL) $surname = '';
if ($client_id == NULL) $client_id = '';
I'm not sure whether this last bit of code is necessary but I wasn't sure how PHP parses a variable with a value of NULL when it gets included in the string used for the MySQL query. I also found that using multiple MATCH...AGAINST statements worked but I couldn't get the wildcard working in them properly.
Thanks again,
M_G
Re: Help with query - matching multiple (exclusive) search t
Posted: Fri Oct 15, 2010 5:28 pm
by Eran
Personally, I think dynamic queries are the way to go. You are trying to fit a square peg into a round hole in my opinion - I'm not sure how MySQL treats the empty wildcard search but chance are performance implications are not good.
A couple more points -
* Shouldn't the client ID be an exact search? what is the purpose of using a wildcard on that?
* I think the wildcard should be only at the end. I would assume clients know how to spell the beginning of their name at least, and this way you could at least use an index for the search
Re: Help with query - matching multiple (exclusive) search t
Posted: Fri Oct 15, 2010 6:46 pm
by mecha_godzilla
I haven't checked what the performance is like yet - the database will probably store up to 1000 records at most so it might not be an issue even if the query is sub-optimal. Of course, that's not really the right attitude to have but I just need to get everything working first, then I'll see if it's fast enough or not. As Larry Wall once said:
Even if it isn't faster, it feels like it is
Re: your other points...
1. Yes, client ID should be an exact match and not wildcarded as I've put it.
2. I can't remember exactly why I'm using wildcards at the start as well as the end, but agree that the opening one is probably worthless.
3. My preference for not using dynamic queries is to try and make sure my code safe, that's all - this may or may not be an irrational decision on my part
Thanks again - I appreciate your help,
M_G