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
    Regular Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    939
    Thanks
    21
    Thanked 0 Times in 0 Posts

    Check if Max failed?

    I am wondering how to check if a query using the MAX() function failed?

    Normally, I code my Prepared Statements like this...
    PHP Code:
        // Build query.
        
    $q2 "SELECT MAX(comment_no) AS lastCommentNo
                FROM comment
                WHERE article_id = ?"
    ;

        
    // Prepare statement.
        
    $stmt2 mysqli_prepare($dbc$q2);

        
    // Bind variable to query.
        
    mysqli_stmt_bind_param($stmt2'i'$articleID);

        
    // Execute query.
        
    mysqli_stmt_execute($stmt2);

        
    // Store results.
        
    mysqli_stmt_store_result($stmt2);

        
    // Check # of Records Returned.
        
    if (mysqli_stmt_num_rows($stmt2)==1){
            
    // Maximum Found.

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

            
    // Fetch record.
            
    mysqli_stmt_fetch($stmt2);

            
    // And so on...


        
    }else{
            
    // Maximum Not Found.
            // Handle error here...

            // End script.
            
    exit();

        }
    //End of FIND LAST COMMENT-NO 

    The problem is that SELECT MAX() always returns something, and so this code doesn't make sense.

    Yet at the same time, I feel my code needs a way to handle things if the query completely blows up, if that makes sense?!

    I'm all about having thorough Error-Handling so my code doesn't blow up, and I feel like I have a weakness here with things as-is...

    Sincerely,


    Debbie

  • #2
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,341
    Thanks
    260
    Thanked 32 Times in 31 Posts
    am curious about this...

    PHP Code:

    // Check # of Records Returned. 
        
    if (mysqli_stmt_num_rows($stmt2)==1){ 
            
    // Maximum Found. 
    is your max 1, i mean it looks as if you only want to seek 1, if its 0 or more than 1 then it dont matter, is that what you want..

    Do you want to do > 0 so you know at least one was found?

  • #3
    Regular Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    939
    Thanks
    21
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by durangod View Post
    am curious about this...

    PHP Code:

    // Check # of Records Returned. 
        
    if (mysqli_stmt_num_rows($stmt2)==1){ 
            
    // Maximum Found. 
    is your max 1, i mean it looks as if you only want to seek 1, if its 0 or more than 1 then it dont matter, is that what you want..

    Do you want to do > 0 so you know at least one was found?

    In most contexts of what I'm doing, a SELECT query returns one record. (e.g. Record for MemberID=123)

    That line is saying, "If you run the SELECT and get back a single record, we assume all ran as expected. But if you don't get back a record, then there was some issue."

    Of course, this might be modified to be >=1, but in this case, I want to check for a case where I run SELECT MAX and the query dies and returns nothing. Maybe that isn't possible?!

    Follow me?

    When I did testing like this...

    Code:
    SELECT MAX(comment_no) AS lastCommentNo
    FROM comment
    WHERE article_id = -999
    ...the query returns *one* record, but the results are "NULL".

    Maybe I want to check for a $lastCommentNo equal to NULL as a sign that my query failed??


    Debbie

  • #4
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,341
    Thanks
    260
    Thanked 32 Times in 31 Posts
    Yes i understand what it was doing, my concern is that it would me more logical to ask is it > 0 which would include 1.

    What might happen depending on your query is that if you dont do the query exactly correct or if something should happen to the db then it will fail every time, especially if 2 happen to exist.

    What i find works best in many cases is to just use if num rows > 0, then i know for sure it atleast found 1, then if i wanted to i could use order by to get the latest one just to cover myself if there are 2.

    I just think its safest to use the > 0 and then if it is not > 0 you know it found nothing.

    Are you just trying to get the last comment from the table? And error control if not exist? I ask because i get the idea you might be using select max in a way it was not intended.
    Last edited by durangod; 03-04-2013 at 05:10 PM.

  • #5
    Regular Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    939
    Thanks
    21
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by durangod View Post
    Yes i understand what it was doing, my concern is that it would me more logical to ask is it > 0 which would include 1.

    What might happen depending on your query is that if you dont do the query exactly correct or if something should happen to the db then it will fail every time, especially if 2 happen to exist.

    What i find works best in many cases is to just use if num rows > 0, then i know for sure it atleast found 1, then if i wanted to i could use order by to get the latest one just to cover myself if there are 2.
    I follow what you are saying, but that advice doesn't apply here...

    In other situations where I might have one or more records returned (e.g. Incoming Messages), then I would do what you are saying.

    But if you re-read what I said above, and look at my actual query, you can see that I am trying to get the highest CommentNo so that I can increment it by one.

    There will NEVER be more than one MAX...

    But to my point in my OP, if the query failed, for example because there was a bogus ArticleID of -999, then unlike a regular SELECT which would return ZERO records, the MAX function will return ONE record with a value of NULL.

    So the way I normally check if a query fails...
    PHP Code:
    // Check # of Records Returned. 
        
    if (mysqli_stmt_num_rows($stmt2)==1){ 
            
    // Maximum Found. 
    or

    PHP Code:
    // Check # of Records Returned. 
        
    if (mysqli_stmt_num_rows($stmt2)>=1){ 
            
    // Maximum Found. 
    ...would not work here?!


    I just think its safest to use the > 0 and then if it is not > 0 you know it found nothing.
    That doesn't apply here, but in other situations, I disagree using that approach, because usually I am just looking for ONE record, and not one or more...


    Are you just trying to get the last comment from the table? And error control if not exist? I ask because i get the idea you might be using select max in a way it was not intended.
    Re-read my SQL above.


    Debbie

  • #6
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,341
    Thanks
    260
    Thanked 32 Times in 31 Posts


  •  

    Posting Permissions

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