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
    Aug 2010
    Posts
    89
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Quick question on an optional WHERE clause

    The below code seems to be working but i just wanted to check its working because its correct, and not a fluke!

    Code:
    SELECT * FROM TABLE WHERE 
    i.gender = '$gender'
    AND c.catName = '$catName'
    AND t.typeName = '$typeName'
    OR v.varName = '$varName'";
    I want this statement to be able to handle queries that may or may not contain a varName.

    So if the $varName field is null it will just disregard it, but if there is a $varName it will be used in the query.

    So have i done this right? Thanks

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,596
    Thanks
    80
    Thanked 4,633 Times in 4,595 Posts
    ???

    No, I don't think so.

    If $varName is blank (or null...same thing in PHP when you then try to use the value as part of a longer string), the query becomes:
    Code:
    SELECT * FROM i,c,t,v WHERE 
    i.gender = 'M'
    AND c.catName = 'framitz'
    AND t.typeName = 'zoogle'
    OR v.varName = ''
    And so it will only match those records where v.varName is blank.

    There's an easy answer.

    Code:
    if ( $varName == "" ) $varName = "%";
    
    $sql = "SELECT ... WHERE v.varName LIKE '$varName' ";
    It won't give you the best performance (better is to have your PHP code simply not include that condition in the SQL at all), but if you aren't talking tens of thousands of records it should be fine.
    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.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,596
    Thanks
    80
    Thanked 4,633 Times in 4,595 Posts
    DOH on me.

    Yes, in the *EXACT* query you showed, it will work!!!

    That's because of the *PRECEDENCE* of the AND and OR operators!!!

    Your query is *REALLY* doing
    Code:
    SELECT * FROM i,c,t,v 
    WHERE 
        ( i.gender = 'M' AND c.catName = 'framitz' AND t.typeName = 'zoogle' )
    OR 
        v.varName = ''
    So what if the v.varName = '' is never true???

    If the other three conditions are true, it NEVER matters what v.varName is!!!

    Remember: AND conditions have a higher precedence than OR conditions.

    *IF* that OR in the query was an AND, or maybe if the query was intended to be
    Code:
    SELECT * FROM i,c,t,v 
    WHERE 
        i.gender = 'M' 
    AND 
        c.catName = 'framitz' 
    AND 
        ( t.typeName = 'zoogle' OR v.varName = '' )
    then of course it wouldn't work.

    So...it just happens to work in this combination of ANDs and ORs.
    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.

  • #4
    New Coder
    Join Date
    Aug 2010
    Posts
    89
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks for your help on this.

    It seems i was wrong, its not working actually. I'll just check whether $varName is null first in the script.

    Then i could

    A) set varName to "None", because the records do actually have None i they don't have a variation. (doh)

    Or

    B) Perform query x if varName is not there and perform query y if varName is there.

    Which would you advise as the best method?

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,596
    Thanks
    80
    Thanked 4,633 Times in 4,595 Posts
    Best results is to not use the condition if you don't need to. Less work for the DB.

    But that's pretty easy:
    Code:
    $sql = "SELECT * FROM i,c,t,v WHERE i.gender = '$gender' AND c.catName = '$catName' AND t.typeName = '$typeName' ";
    
    if ( $varName != "" ) $sql .= "OR v.varName = '$varName'";
    
    ...
    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
    •