-
Oct 16th, 2017, 04:32 PM
#1
MySQL Quick Search
I'm working a website that I want to perform a quick search where the visitor can plug in a search value it will search my database based on the search value. The SQL dump for this particular data table looks like this:
Code:
CREATE TABLE `customer` (
`customer_id` int(11) NOT NULL,
`household_id` int(11) NOT NULL,
`first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`suffix_id` int(11) DEFAULT NULL,
`birthday` date NOT NULL,
`customer_since` date DEFAULT NULL,
`referral_id` int(11) NOT NULL,
`phone` char(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
`address` varchar(35) COLLATE utf8_unicode_ci NOT NULL,
`city` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`state_id` int(11) NOT NULL,
`zip_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
The quick search should only search the first_name, last_name, suffix_id, phone, or email columns. So some sample inputs could be:
Code:
John Smith III
Smith, John
John
Smith
555-555-5555
user@domain.com
My question is, should I use SQL to search each column like this:
Code:
SELECT ... From ... WHERE `first_name` = ? OR `last_name` = ? OR `phone` = ? `email` = ?
Or should I use the development language (in this case PHP) to validate whether or not the user input a name, phone, or email address and build my WHERE clause based on those conditions?
-
Oct 16th, 2017, 05:12 PM
#2
Re: MySQL Quick Search
You can't reliably tell which field(s) might be appropriate... for example James can be a first-name or a last-name, and is valid in an email address (even if that isn't the persons name, because it may be part of a company name). Also, email addresses can contain numbers.
To reliably deal with all of these types of input:
John Smith III
Smith, John
John
Smith
..you need to separate them into "words" somehow (for all of them, either "John" and/or "Smith" and/or "III"), and search each field in the database to see if it contains each of the "words"... so if the user enters two "words", you need each field in the Where clause twice (with separate parameters).
It is debatable whether that is better done in the database or not, and I would recommend using whichever you are most comfortable with.
-
Oct 16th, 2017, 09:36 PM
#3
Re: MySQL Quick Search
So to limit the number of returned results, I suppose that I should prioritize the number of AND matches? For example, if I get a result from:
Code:
first_name = John AND last_name = Smith AND suffix_id = [the id for III]
then that should obviously be returned and preferably towards the top of the list. Is there a way that I can order the results by the number of AND matches like that or will I need to run multiple queries?
-
Oct 17th, 2017, 05:38 AM
#4
Re: MySQL Quick Search
If we are talking about a web page here...
I use a jQuery AUTOCOMPLETE in the browser with a pre-loaded array of TEXT to search.
That text in your case would be "LAST NAME, FIRST NAME, Phone, EMail".
I can grab thousands of these in an array in the browser - the return speed from back end is not noticeable.
I overload the search function for the AUTOCOMPLETE to "find" text anywhere in the STRING.
That means my tax collection clients can start typing the name of the person on the phone as that person is stating the info. Not interrupting with "please give me your last name"...
-
Oct 17th, 2017, 06:25 AM
#5
Re: MySQL Quick Search
That is a good idea if it is appropriate for your data and system.
Originally Posted by dday9
So to limit the number of returned results, I suppose that I should prioritize the number of AND matches? For example, if I get a result from:
Code:
first_name = John AND last_name = Smith AND suffix_id = [the id for III]
then that should obviously be returned and preferably towards the top of the list. Is there a way that I can order the results by the number of AND matches like that or will I need to run multiple queries?
There are various ways to do that kind of thing (which is best varies based on data etc), but one I have used in the past is something like this:
Code:
SELECT <fields you want>, Sum(Matches)
FROM (
SELECT <fields you want>, 1 as Matches From ... WHERE `first_name`= ? OR `last_name`= ? OR `phone` = ? `email` = ?
UNION ALL
SELECT <fields you want>, 1 as Matches From ... WHERE `first_name`= ? OR `last_name`= ? OR `phone` = ? `email` = ?
...
)
GROUP BY <fields you want>
ORDER BY Sum(Matches)
(the first inner-select is for the first value, the second for the second value, etc)
This can be easier to design if you use a temporary table, running the inner-select in a loop for each value, then the outer-select to get the final results
-
Oct 17th, 2017, 05:33 PM
#6
Re: MySQL Quick Search
@szlamany, so what you're suggesting is to load the various column values into an array in the window's onload event, and then searching the values as they type (or even after they type)?
-
Oct 17th, 2017, 05:47 PM
#7
Re: MySQL Quick Search
This is an autocomplete
https://jqueryui.com/autocomplete/
You would have one of these and the "text" would be ALL YOUR parts.
You override the lookup function so that "any" word typed matches a STRING in that array without it being the left-most part and each SPACE makes a different lookup word so that the ORDER typed does not matter either.
PM me your real email address and I'll send you a quick video demo...
-
Oct 18th, 2017, 07:08 AM
#8
Re: MySQL Quick Search
@dday - here is a smaller piece of that video - and I'll explain further here...
In the page .Ready event I pre-load an array of 4460 items. You'll see that number flash a couple of times as all the text in the lookup gets deleted.
The AUTOCOMPLETE uses this array to search as text is entered. The search happens locally in the browser - using simple JavaScript. That's why it is so fast. And realize that the match logic is called on each keystroke.
The search uses REGEX on each "word" entered - so that example where I typed FINE ARTS could just as well have been ARTS FINE - the word ORDER is not important.
You have this need to type a LAST NAME or a FIRST NAME or a PHONE NUMBER or an EMAIL. To use this technique you would CONCATENATE all of these items into a single string for the array. That way the search is organically multi-valued without you ever having to think about what the user entered.
-
Oct 18th, 2017, 08:23 AM
#9
Re: MySQL Quick Search
This is probably what I'll go with, but since it is outside the realm of db development, I'm going to start a new thread in the jQuery forum because I'm having some issues with the autocomplete.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|