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 10 of 10
  1. #1
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,415
    Thanks
    269
    Thanked 32 Times in 31 Posts

    select on not 0 and not null values

    I have redone one of my queries to include checking for not null and not 0 values. Im just wondering if there is a shorter way, this appears that it should work. I dont get any errors so far.

    also does it make any difference if i use != or <> in this?

    PHP Code:
    "SELECT * FROM invite 
            WHERE department = '$department'
             if(visits > 0){ AND visits < '$visits' }
             if(seconds > 0){ AND seconds < '$timeon' }
             if(page IS NOT NULL OR page != '' OR LENGTH('page') > 0){ AND page = '$checkurl' }
             if(referer IS NOT NULL OR referer != '' OR LENGTH('referer') > 0){ AND referer = '$thereferer' }
             AND isactive='Y'"


  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    != is a proprietary mysql comparison, the standard SQL equivalent is <> and so using <> is to be preferred as it would make converting to a different sql database easier.

    With the particular comparisons you have there I can't see any obvious way to shorten the query.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • Users who have thanked felgall for this post:

    durangod (01-23-2014)

  • #3
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,415
    Thanks
    269
    Thanked 32 Times in 31 Posts
    thanks after some more testing i might be better off using case instead

    it seems to be hanging up on one of these two lines,
    i know i need to go with AND/&& rather than OR, but even with that i
    think maybe just using case might be the way to go here.

    PHP Code:

    if(page IS NOT NULL OR page != '' OR LENGTH('page') > 0){ AND page '$checkurl' 
    if(
    referer IS NOT NULL OR referer != '' OR LENGTH('referer') > 0){ AND referer '$thereferer' 
    From my reading i dont think i even need the AND, just seperate with coma, is this correct?

    PHP Code:
    if(page IS NOT NULLpage != ''LENGTH('page') > 0){ AND page '$currentUrl' }
    if(
    referer IS NOT NULLreferer != ''LENGTH('referer') > 0){ AND referer '$thereferer' 
    actually im now thinking all i need is the LENGTH, because if the LENGTH is > 0 then the
    other two checks are a moot point.


    UPDATE: what i am finding is that you cant use the if on nonstored precedures and if i understood correctly
    i cant manipulate the WHERE clause as i am trying to do, as the if is only desiged to be used in the select portion
    of the query only.

    I will keep digging .. so what i should be looking for is IF statement in a WHERE clause not SELECT..

    ANOTHER UPDATE: so here is what i came up with so far after doing some more searching and reading and maybe
    this is correct thinking

    as an example in the WHERE clause

    PHP Code:
    WHERE... whatever
    AND IF(myfield 'somevalue'10) = 1     true 1  false 0

    so what that tells me is that i have to give three expressions 
    something like 
    a ternary operator  
    (questionaction trueactive false)

    and 
    then tell it the action you want to accept which is the =or =true 
    so using a different part of the query to apply this
    and so if i have this thinking correctly this is what i need to do

    PHP Code:
    WHERE department '$department'
    AND IF(visits 0,10) = 1     // so this would me that this is true statement
           
    question    true    false     what i want to use 1 true 
    do i have that right?

    so here is the full query

    PHP Code:
             $sqlquerya "SELECT * FROM invite 
             WHERE department = '$department'
             AND IF(visits > 0,1,0)=1
             AND IF(visits < '$visits',1,0)=1
             AND IF(seconds > 0,1,0)=1
             AND IF(seconds < '$timeon',1,0)=1
             AND IF(LENGTH('page')>4,1,0)=1
             AND IF(page = '$currentUrl',1,0)=1
             AND IF(LENGTH('referer')>4,1,0)=1
             AND IF(referer = '$thereferer',1,0)=1
             AND isactive='Y' LIMIT 1"

    lets keep our fingers crossed lol
    Last edited by durangod; 01-23-2014 at 06:25 PM.

  • #4
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,415
    Thanks
    269
    Thanked 32 Times in 31 Posts
    ok i changed the query just because i had it wrong in my head, i think it is correct now, maybe, we will see if this blows up in my face or not lol..

    update: its not failing like before so we are one step closer to getting this function to work correctly...

    this query does seem to complete now and it does get past this section of code successfully so thats good, that means we did something right here lol..
    Last edited by durangod; 01-23-2014 at 06:42 PM.

  • #5
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,415
    Thanks
    269
    Thanked 32 Times in 31 Posts
    ok so i found out i have to find a way to pair them up in one AND statement together in pairs just as

    PHP Code:

    // I know this is not right but its what i need to figure out how to do, as they
    // will not select correctly unless they are as pairs. 

    AND IF(visits 0,1,0)=1 THEN AND IF(visits '$visits',1,0)=

    //and so forth 

    UPDATE: i used the db sql in phpmyadmin to run some tests. it appears that all lines work accept for the length lines

    it does not like this
    PHP Code:
    AND IF(LENGTH('page')>4,1,0)=
    turns out it did not like the 'page' so i changed it to (page) and it works in phpmyadmin but not in live file... ill keep digging
    Last edited by durangod; 01-23-2014 at 09:17 PM.

  • #6
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,415
    Thanks
    269
    Thanked 32 Times in 31 Posts
    this is strange, i noticed on the output from phpmyadmin that page is changed to uppercase for
    some reason where as referrer is not..
    I thought maybe page might be a reserved word for mysql but its not.

    PHP Code:
    SELECT 
    FROM invite
    WHERE department 
    '10'
    AND isactive 'Y'
    AND IF( visits >01) =1
    AND IF( visits <= '1'1) =1
    AND IF( seconds >01) =1
    AND IF( seconds <= '75'1) =1
    AND IF( LENGTH
    PAGE ) >41) =1
    AND IF( 
    PAGE 'xxxxxx.com/index.html'1) =1
    AND IF( LENGTHreferer ) >41) =1
    AND IF( referer 'xxxxxxxx.com'1) =1
    LIMIT 1 

    the input for both are exactly the same

    PHP Code:
            AND IF(LENGTH(page)>4,1,0)=
             
    AND IF(page 'xxxxxx.com/index.html',1,0)=1
             
    AND IF(LENGTH(referer)>4,1,0)=
             
    AND IF(referer 'xxxxxxxxxxxxxxxx.com',1,0)=
    so in both cases when it sees page it changes it to PAGE, how strange.

    as a test i change the field name to pageurl rather than page and it solved that issue, but i have no idea why, page must be a reserved word of some kind im thinking.

    now im thinking i have to go thru this entire script and change page for all table queries to pageurl or some other name..

    update well its only 6 files per agent ransack so thats not too bad
    Last edited by durangod; 01-23-2014 at 09:52 PM.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,515 Times in 4,479 Posts
    What in the whirled is the point of writing conditions in that silly fashion??

    Code:
    AND IF(LENGTH(page)>4,1,0)=1  
    AND IF(page = 'xxxxxx.com/index.html',1,0)=1 
    AND IF(LENGTH(referer)>4,1,0)=1  
    AND IF(referer = 'xxxxxxxxxxxxxxxx.com',1,0)=1
    First of all, *IF* the column page is indeed equal to "xxxxxx.com/index.html" then OF COURSE its length is greater than 4! WHY TEST THE LENGTH, AT ALL?
    Code:
             AND IF(page = 'xxxxxx.com/index.html',1,0)=1 
             AND IF(referer = 'xxxxxxxxxxxxxxxx.com',1,0)=1
    will do the same thing.

    But more than that, *THIS* code will do the same thing much more efficiently:
    Code:
             AND page = 'xxxxxx.com/index.html'
             AND referer = 'xxxxxxxxxxxxxxxx.com'
    I think maybe it is time you studied up--a LOT!--on how to create expressions in SQL code.
    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.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,515 Times in 4,479 Posts
    And for MORE just plain nutso code:
    Code:
    AND IF( visits >0, 1, 0 ) =1 
    AND IF( visits <= '1', 1, 0 ) =1
    So the condition is true if visits is > 0 *AND* the condition is true if visits is <= 1.
    SO WHY TEST THE VALUE OF VISITS AT ALL!!!

    More simplifying:
    Code:
    SELECT *  
    FROM invite 
    WHERE department = '10' 
    AND isactive = 'Y' 
    AND seconds BETWEEN 1 AND 75
    AND PAGE = 'xxxxxx.com/index.html'
    AND referer = 'xxxxxxxx.com'
    LIMIT 1
    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.

  • #9
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,415
    Thanks
    269
    Thanked 32 Times in 31 Posts
    Thanks Pedant i was hoping you would chime in...

    "What in the whirled is the point of writing conditions in that silly fashion??"

    That made my day lol... and i am not disagreeing, but i looked at some samples on the net and i thought well maybe it looks nuts but maybe its correctly done. Thats why i posted my whole process as i looked for solutions so someone could follow my exploritory process lol..

    give me a bit to go thru what you wrote here and i will reply, thanks for the reply..

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,515 Times in 4,479 Posts
    One last thing: If you need to check for either 0 or NULL, MySQL gives you an easy way to do it:
    Code:
    ... WHERE IFNULL( fieldname, 0 ) = 0 ...
    or, of course,
    Code:
    ... WHERE IFNULL( fieldname, 0 ) <> 0 ...
    IFNULL does NOT change the value of the given expression UNLESS the value is NULL. When it is NULL, it substitutes the value you specify. Really handy, really simple.
    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
    •