What are the possible outcomes of this query (if it fails)…
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
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
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.
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…
// 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
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
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…
// 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
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).