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 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Feb 2011
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Cool If/then/else SELECT statement in MySQL?

    Hello everyone, my first post here! I have been using this forum for years to gain information but this time I could not find what I was looking for, so better give it a try and post my query here...


    Okay... I have a MySQL table looking like:


    contacts
    +----------------
    contact_id (int11)
    contact_name (varchar128)
    contact_surname (varchar128)
    +----------------

    Now, I want to do a SELECT statement that would do something like below:


    If "contact_surname" is NULL, then:
    ---> name = contact_name + '(foobar)'

    If "contact_surname" is NOT NULL, then:
    ---> name = contact_name + ' ' + contact_surname + '(foobar)'

    After we have a new variable (or fake column?) named "name" we do a SELECT on that one:

    SELECT foo FROM contacts WHERE name LIKE 'querystring%' ORDER BY name ASC;


    Is there any way to accomplish this in one query only? Or do I have to do some hacking with (in this case PHP) programming?



    Thank you in advance!

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    possibly CASE statement, order by column that CASE statement produces
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Could use CASE, but IF is simpler.

    Code:
    SELECT foo FROM contacts
    WHERE CONCAT( contact_name, IF(contact_surname IS NULL,'',CONCAT(' ',contact_surname)),'(foobar)')
          LIKE 'xxxx%'
    ORDER BY CONCAT( contact_name, IF(contact_surname IS NULL,'',CONCAT(' ',contact_surname)),'(foobar)')
    Looks ugly, but it would actually be reasonably fast. Not real fast, because there's no way for MySQL to use an index with the WHERE for that, so you are talking a complete table scan. But reasonable speed, at least.
    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:

    Forsberg (02-04-2011)

  • #4
    New to the CF scene
    Join Date
    Feb 2011
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Could use CASE, but IF is simpler.

    Code:
    SELECT foo FROM contacts
    WHERE CONCAT( contact_name, IF(contact_surname IS NULL,'',CONCAT(' ',contact_surname)),'(foobar)')
          LIKE 'xxxx%'
    ORDER BY CONCAT( contact_name, IF(contact_surname IS NULL,'',CONCAT(' ',contact_surname)),'(foobar)')
    Looks ugly, but it would actually be reasonably fast. Not real fast, because there's no way for MySQL to use an index with the WHERE for that, so you are talking a complete table scan. But reasonable speed, at least.

    Thank you very much! That did the trick sir!

    Are there also ELSE IF statements? Like I want to match:


    Case 1: "Firstname"
    Case 2: "Firstname (something)"
    Case 3: "Firstname Lastname"
    Case 4: "Firstname Lastname (something)"
    Case 5: "Firstname X Lastname (something)"

    I think that should match all different conditions...

    In PHP I would have done:

    $firstname = "Freddy"
    $lastname = "Rogers"
    $roomcode = "(UTTW)"

    $fullname = $firstname;

    if ($lastname) {
    $fullname .= ' ' . $lastname;
    }
    if ($roomcode) {
    $fullname .= ' ' . $roomcode;
    }


    Is it possible to do similar with SQL query?

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    MySQL's IF really *is* an if/else combination.

    It's a function that looks like if( condition, truevalue, falsevalue )

    That is, if the condition is true, the truevalue is returned. If the condition is false, the falsevalue is.

    Note that because it *is* a function, the truevalue and falsevalue may each, themselves, be full MySQL expressions. Yes, including another if( ) function call.

    But you don't even need that. You just need another concatenation:
    Code:
    SELECT foo FROM contacts
    WHERE CONCAT( contact_name, 
                  IF(contact_surname IS NULL, '', CONCAT(' ',contact_surname) ),
                  IF(roomcode IS NULL, '', CONCAT(' ',roomcode) )
                )
          LIKE 'xxxx%'
    ORDER BY CONCAT( contact_name, 
                     IF(contact_surname IS NULL, '', CONCAT(' ',contact_surname) ),
                     IF(roomcode IS NULL, '', CONCAT(' ',roomcode) )
                   )
    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:

    Forsberg (02-04-2011)

  • #6
    New to the CF scene
    Join Date
    Feb 2011
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you very much for your answers Old Pedant!

    Highly appreciated!


  •  

    Posting Permissions

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