Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jun 2010
    Posts
    54
    Thanks
    4
    Thanked 0 Times in 0 Posts

    SELECT WHERE x LIKE row1+row2

    I am trying to figure out how to make a simple query that finds where a variable is LIKE two rows in combination. For example, in my database, I have split names between first and last names. I want to be able to search these names put together. I know this seems like a simple question, but it is VERY difficult to come up with search terms for this problem. (Think about how many results "combining rows mysql" turns up!)

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,385 Times in 4,350 Posts
    Well, you could do
    Code:
    SELECT * FROM table WHERE CONCAT(firstName,' ',lastName) = 'John Doe'
    (where you would of course put an actual value in place of John Doe).

    But...

    But what happens if the person who enters the name puts in two space? Or thinks you want it last name first ? Or or or ???

    I think a *better* way to do it is to use your server side coding (PHP, ASP, JSP, whatever) to get *separate* names and then do something like this:
    Code:
    SELECT * FROM table WHERE firstname = 'Joe' AND lastname = 'Blow'
    If you aren't sure which is the first and which is the last name (though I think you could design your form to ensure that!) then you could even do:
    Code:
    SELECT * FROM table WHERE firstname IN ('John','Doe') AND lastname IN ('John','Doe')
    In short, there are indeed many many answers to this question because there are so many variants on the question.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    soneen (06-23-2010)

  • #3
    New Coder
    Join Date
    Jun 2010
    Posts
    54
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Perfect, Thank you!

    I ignored the possible double space problem, but did see the "last name first" problem as a real killer. I remedied that with this code. Note that this search is dynamic and pops up new results are every letter. It probably would be better to go with Old_Pedant's approach if you are doing an old fashioned search query.
    Code:
    SELECT * FROM table WHERE CONCAT(firstName,' ',lastName) LIKE '%John Doe%'
    Last edited by soneen; 06-23-2010 at 04:53 PM. Reason: New code to contribute!

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Using a search term beginning with a wild card means no index will be used, a sure performance killer as your table grows in size.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,385 Times in 4,350 Posts
    Quote Originally Posted by guelphdad View Post
    Using a search term beginning with a wild card means no index will be used, a sure performance killer as your table grows in size.
    Note that this is a MySQL restriction. Not true of all databases.

    BUt oh man is it a killer with MySQL. Found that out the hard way a couple of years back. In fact, MySQL isn't very clever, at all, in its use of indexes. *sigh*
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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