Yep, don’t throw an error! Seriously, what will you do differently if you fail to update the last activity time? Nothing, as it will likely succeed when they visit another page. So, why bother catching an error to begin with?
Or don’t base your error on affected rows, but on whether mysqli_stmt_error returns anything but NULL or an empty string. If it is not NULL or an empty string, that means your query failed.
My prior logic being that if my UPDATE query cannot update, then it failed for some reason that I need to know about.
Could be a Database Connection issue, no MemberID, query issue, etc. (In all honesty, I bet you A LOT of my Custom Error-Handling will never fire, but if something funky happens, then at least I know about it!!)
Is there somewhere, where I can read up more on Prepared Statements and things like mysqli_stmt_error??
Admittedly, maybe I am not choosing the best tool for certain situations like this??
Sincerely,
Debbie
In fact, when you visit that page, in the example, it shows how to detect a connection error too, so you can place that check right after your new mysqli() call.
cpradio,
The Manual says this about affected_rows…
An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE/DELETE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query has returned an error. NULL indicates an invalid argument was supplied to the function.
So why not just modify my code like this…
// Build query.
$q1 = "UPDATE member
SET last_activity = now()
WHERE id = ?
LIMIT 1";
// Prepare statement.
$stmt1 = mysqli_prepare($dbc, $q1);
// Bind variables to query.
mysqli_stmt_bind_param($stmt1, 'i', $sessMemberID);
// Execute query.
mysqli_stmt_execute($stmt1);
// Verify Update.
if (mysqli_stmt_affected_rows($stmt1)==0){
// No Update.
// Do Nothing.
}elseif (mysqli_stmt_affected_rows($stmt1)==1){
// Update Succeeded.
// Do Nothing.
}else{
// Update Failed.
$_SESSION['resultsCode'] = 'LAST_ACTIVITY_MEMBER_STATUS_NOT_UPDATED_2050';
Sincerely,
Debbie
cpradio,
My bigger question is, “When is enough, enough?”
I could change the table where I log my custom errors to include a field for MySQL’s mysqli_stmt_error, but is that really necessary?
If I have thoroughly tested my code in Dev, do I really need something like mysqli_stmt_error in my Production environment?
Sure it would be nice, however…
1.) I would have to rewrite all of the code I currently have for every query in my website…
2.) In this instance, I only care if my UPDATE didn’t run because of an error!!
Sure, for debugging, knowing the exact reason would be nice, but as far as Error-handling and the End-User, it is either…
a.) No Update because there was no change (Don’t tell User)
b.) There was an Update (Don’t tell User)
c.) Some error occurred (Tell User and Admin)
By the time I get to Production - most of the time - my code should be stable enough that you investigate more only when you need to.
So does it make sense to have to build in things to Log and Handle every know MySQL Error known to man?!
What do you think?
Sincerely,
Debbie
I’m only referring to this one instance to use instead of mysqli_affected_rows, as that is what you are trying to log (whether that’s true for other locations too, is up to you).
if (!empty(mysqli_stmt_error)) {
// log an error
}
MySQL, PHP, and Apache all have their own logs too, so you’d be able to see the error MySQL threw by looking in the MySQL logs. Although, appending the value of mysqli_stmt_error to a description of your error wouldn’t be a bad thing (if you have a description column).
For 2c), I don’t agree with telling the user. You don’t need to tell the user something unless it impacts them in a negative way (or if you can change something – form field for example; that would fix the error). This doesn’t do that! This is purely for your own benefit and not the User’s.
Put your code in Production, mark this off as something that needs tweaked after you are live. It isn’t going to destroy your site and it won’t negatively affect your users.
You can’t log everything known to man, you are bound to miss something here and there, but that’s how you learn.
cpradio,
For now I am going with the code I posted above…
// Verify Update.
if (mysqli_stmt_affected_rows($stmt1)==0){
// No Update.
// Do Nothing.
}elseif (mysqli_stmt_affected_rows($stmt1)==1){
// Update Succeeded.
// Do Nothing.
}else{
// Update Failed.
$_SESSION['resultsCode'] = 'LAST_ACTIVITY_MEMBER_STATUS_NOT_UPDATED_2050';
This seems to have fixed things.
As far as using MySQL’s mysqli_stmt_error, I am interested in that, but I can’t easily incorporate that into all places of my website, so it will have to wait for v3.0.
In the future, I think it would be good to have it so that code is associated with all queries, and then if they fail I have a more detailed description being logged in my database. But first I need to get this version done!!
Thanks for all of the help again!!
This was a very frustrating issue, but also a very educational one, and one that should make me a better coder down the road!! 
Thanks a bunch!!! :tup:
Sincerely,
Debbie
P.S. For c.) “Tell the User and Admin”, I meant give a dumbed down message to the User that an error occurred, and then the more detailed message should be logged so the Admin know what happened.