SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    How can MAX() Fail?

    What are the possible outcomes of this query (if it fails)...

    Code SQL:
    SELECT MAX(comment_no) AS lastCommentNo
    FROM comment
    WHERE article_id = ?


    I'm trying to build error-handling for it in PHP, and am not sure I understand the possible scenarios?!

    Sincerely,


    Debbie

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    If nothing matches the criteria, you'll get a null result, so you need to be able to handle that accordingly.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    If nothing matches the criteria, you'll get a null result, so you need to be able to handle that accordingly.
    But can a query like this in MySQL just "blow up", "crash", "burn", etc?

    (Maybe I don't really understand the inner workings of databases...)

    I am trying to handle 3 - perceived - scenarios...

    1.) The query find the largest "Comment No" for a given Article

    2.) The query blows up

    3.) The query finds no Comments for a given Article


    My fear is that if I combine #2 and #3 together and just say "A NULL return is okay" that there might be something bad that could happen?!

    Follow me??


    Debbie

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    The only time queries truly "blow up" is if you're trying something with discordant datatypes, like performing a numeric comparison with a character value, or trying to imply conversions for some of the larger datatypes (CLOBS to strings for example).

    If you do a straight query, the normal result is a recordset, which will have rows if the conditions are met, and nothing if the conditions are not met.
    If you try to perform just an aggregate function like MAX() or MIN() for a condition that can't be met, the result set will be a null value.
    If you try to perform just an aggregate function like COUNT() OR SUM() for a condition that can't be met, the result set will be zero.

    The easiest way to understand how the query to behave is to test it in the database (phpmyadmin, sql server management studio, etc.). Try the query where the conditions are met, and when they're not, and see how the resultsets are returned.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    The only time queries truly "blow up" is if you're trying something with discordant datatypes, like performing a numeric comparison with a character value, or trying to imply conversions for some of the larger datatypes (CLOBS to strings for example).
    Hmm, okay.


    If you do a straight query, the normal result is a recordset, which will have rows if the conditions are met, and nothing if the conditions are not met.
    If you try to perform just an aggregate function like MAX() or MIN() for a condition that can't be met, the result set will be a null value.
    If you try to perform just an aggregate function like COUNT() OR SUM() for a condition that can't be met, the result set will be zero.
    These I knew.


    The easiest way to understand how the query to behave is to test it in the database (phpmyadmin, sql server management studio, etc.). Try the query where the conditions are met, and when they're not, and see how the resultsets are returned.
    Well, I did that, and I saw that the query returned "NULL" when there were no Comments, but my fear was how to handle things if things "blow up".

    So, to frame this question better - although this is more of a programming question - how should my code handle things?

    Using pseudo-code, I do this...

    PHP Code:
        // Build query.
        
    $q2 "SELECT MAX(comment_no) AS lastCommentNo
                FROM comment
                WHERE article_id = ?"
    ;

        
    // Bind result-set to variables.
        
    mysqli_stmt_bind_result($stmt2$lastCommentNo);

        
    // Fetch record.
        
    mysqli_stmt_fetch($stmt2);

        
    // Check # of Records Returned.
        
    if (isset($lastCommentNo)){
            
    // Maximum Found.

            // Increment CommentNo.
            
    $commentNo $lastCommentNo 1;

        }else{
            
    // Maximum Not Found.
            // Thrown an Error.
        


    If no one has commented yet on the Article, then my ELSE branch would fire, and that is okay, so in that scenario I should "Do Nothing".

    However, you could argue that the query could also fail, and then in that case my ELSE branch would fire again, but this time it should throw an Error.

    See my confusion?

    What would you advise?

    Sincerely,


    Debbie

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by DoubleDee View Post

    PHP Code:
        // Build query.
        
    $q2 "SELECT MAX(comment_no) AS lastCommentNo
                FROM comment
                WHERE article_id = ?"
    ;

        
    // Bind result-set to variables.
        
    mysqli_stmt_bind_result($stmt2$lastCommentNo);

        
    // Fetch record.
        
    mysqli_stmt_fetch($stmt2);

        
    // Check # of Records Returned.
        
    if (isset($lastCommentNo)){
            
    // Maximum Found.

            // Increment CommentNo.
            
    $commentNo $lastCommentNo 1;

        }else{
            
    // Maximum Not Found.
            // Thrown an Error.
        

    Actually, your logic is flawed - the $lastCommentNo would be set - it would be null, but it would be set....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  7. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    Actually, your logic is flawed - the $lastCommentNo would be set - it would be null, but it would be set....
    It's not flawed.

    In PHP, isset() determines if a variable is set and is not NULL.

    That is what my code does.

    I have been asking about the ELSE branch of my code, i.e. should I just set $lastCommentNo = 1 and call things acceptable, or do I get neurotic, and first run a query that checks the COUNT() of Comments, and if it is non-zero, THEN run the code above.

    Your earlier post suggests that I can assume the SELECT MAX() won't "blow up", and so if I get a "NULL", then I can do as I suggest above...

    Sincerely,


    Debbie

  8. #8
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    You're right. That's the downfall to writing in different languages.

    Why throw an error, though? No comments seems like a valid situation....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  9. #9
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    You're right. That's the downfall to writing in different languages.

    Why throw an error, though? No comments seems like a valid situation....
    Again, because I was thinking that my SELECT MAX() could fail, and *also* return a NULL under some other condition besides there just simply not being any Comments.

    I have decided this should be safe enough code...

    PHP Code:
        // Build query.
        
    $q2 "SELECT MAX(comment_no) AS lastCommentNo
                FROM comment
                WHERE article_id = ?"
    ;

    and 
    so on...

        
    // Fetch record.
        
    mysqli_stmt_fetch($stmt2);

        
    // Check # of Records Returned.
        
    if (isset($lastCommentNo)){
            
    // Maximum Found.
            // Increment CommentNo.
            
    $commentNo $lastCommentNo 1;

        }else{
            
    // Maximum Not Found.
            // Assume No Comments Exist.
            
    $commentNo 1;
        } 
    Sincerely,


    Debbie

  10. #10
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,080
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    If you're willing to cut corners you can do away with the if (isset(...)) { ... } else { ... } part and replace it with $lastCommentNo++, since if you have $var=null; $var++;, $var will be 1.

    (whether this is mathematically correct is another thing, let's not go there).
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Bookmarks

Posting Permissions

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