Results 1 to 9 of 9

Thread: MySQL Quick Search

  1. #1

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    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?
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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.

  3. #3

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    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?
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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"...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: MySQL Quick Search

    That is a good idea if it is appropriate for your data and system.
    Quote Originally Posted by dday9 View Post
    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

  6. #6

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    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)?
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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.


    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    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.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width