Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    134

    Unanswered: How to Search for names in MySQL

    Hi,

    I have mySQL with first_name, middle_name and last_name fields.

    How can I pass a string from my php like param_name so when the user pass single name or multiple name it will split and search for the name in every fields..

    For example if the user search for param_name = ""John Mathew Clark" it will do:

    first_name or middle_name or last_name LIKE "%John%"
    first_name or middle_name or last_name LIKE "%Mathew%"
    first_name or middle_name or last_name LIKE "%Clark%"

    Thanks,
    Jassim

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the only way I can think of doing that is
    Code:
    first_name like '%john%' or first_name like '%Mathew%' or first_name like '%Clark%'
    or
    middle_name like '%john%' or middle_name like '%Mathew%' or middle_name like '%Clark%'
    or
    last_name like '%john%' or last_name like '%Mathew%' or last_name like '%Clark%'
    you could used a stored procedure to build the fullname (or store the full name as a column) and then
    Code:
    full_name like '%john%' or full_name like '%Mathew%' or full_name like '%Clark%'
    whether you should be using like in the first place is a moot point as middle_name like '%Mathew%' will not find matthew

    you could (assuming you had the right table type and index) use match
    Code:
    WHERE MATCH (fore_name, middle_name, last_name)
        AGAINST ('John' IN NATURAL LANGUAGE MODE)
    or MATCH (fore_name, middle_name, last_name)
        AGAINST ('Mathew' IN NATURAL LANGUAGE MODE)
     MATCH (fore_name, middle_name, last_name)
        AGAINST ('Clark' IN NATURAL LANGUAGE MODE);
    perhaps you should consider using one of the phonetic encoding structures for surnames such as Soundex or double metaphone (there is already an implementation for PHP for Double metaphone)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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