MySQL

MySQL query for FirstName, LastName search

TAGs: 3 Comment

Posted By: eligeske on in MySQL

In most cases databases are set up to split a persons first name and last name. So when doing a search for people in a database you need to join the two fields together in the search statement.

Or you could split it into separate queries based on the space, but that would add more server load and function time, not good.

It’s much easier to use the MySQL concat() method, or concat_ws() which is concat with a seperator.

SELECT * FROM `table_name` WHERE concat_ws(' ', 'FirstName', 'LastName');

Please note that in the first quotes is a space.

 

3 Responses to "MySQL query for FirstName, LastName search"

"SHARING IS CARING"
  1. This technique would run slow on large dataset. For optimal performance use the myisam Full text indexes and run a query with match /against. The beauty is that it returns a relevance score that you can sort. So if there is any typo it will still returns relevant matches.

    Concat is great to format returned but not so good in a where statement as everyrow of the database will need to be pre-concat before the engine match it. + you loose all the indexation features.

    // Runs in 0.24 second
    Select match(firstname, lastname) against(“Simon Ferragne”) as rel, id, firstname, lastname
    from tbl_consumer_profile where
    match(firstname, lastname) against(“simon ferragne”) order by rel desc

    // Runs in 0.49 second
    Select match(firstname, lastname) against(“Simon Ferragne”) as rel, id, firstname, lastname from tbl_consumer_profile where concat_ws(‘ ‘,firstname,lastname)=(“simon ferragne”) order by rel desc

  2. eligeske says:

    Nice stuff Simon, thanks for the info!

  3. Jonry Alcano says:

    This is great!!Thanks man!!.. :)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Learning DHTMLX Suite UI

Learn the foundation of the DHTMLX Suite quickly while building a single page application with multiple components in harmony.

Popular post