Debbie, are you getting any other errors? (make sure your script is set to show them all) Since mysqli_stmt_affected_rows() isn’t giving 1, check to see what it is giving. Also, a common cause of what you’re seeing is due to the preparing failing for some reason.
Try your script but with a little more feedback when things go wrong.
error_reporting(-1);
ini_set('display_errors', 'On');
// Initialize variables.
$loggedIn = 1;
$memberID = 19;
// Connect to the database.
require_once(WEB_ROOT . 'private/mysqli_connect.php');
// Build query.
$q1 = "UPDATE member
SET logged_in=?,
last_activity=now()
WHERE id=?
LIMIT 1";
// Prepare statement.
$stmt1 = mysqli_prepare($dbc, $q1);
if ( ! $stmt1) {
die("Prepare failed: " . mysqli_error($dbc));
}
// Bind variables to query.
mysqli_stmt_bind_param($stmt1, 'ii', $loggedIn, $memberID);
// Execute query.
mysqli_stmt_execute($stmt1);
// Verify Update.
$affected_rows = mysqli_stmt_affected_rows($stmt1);
if ($affected_rows !== 1) {
echo "Affected rows: ";
var_dump($affected_rows);
// Update Failed.
$_SESSION['resultsCode'] = 'MEMBER_UPDATE_FAILED_2126';
echo 'HERE';
}//End of UPDATE MEMBER RECORD
No, and the one error I do get isn’t really an error - it is just my own custom error to show what happens when things go wrong.
(make sure your script is set to show them all)
I have a debugger in my IDE…
Since mysqli_stmt_affected_rows() isn’t giving 1, check to see what it is giving.
I already had similar code to what you suggested above, and I get back a zero “0” when I run things, which is strange because after logging in I see the following in phpMyAdmin…
logged_in = 1
last_activity = 2012-03-10 11:52:01
Which means the UPDATE query DID run successfully and log me in.
But then why doesn’t my code reflect that as well?!
This might be a silly question, are you sure logged_in is ever being set to anything other than 1 for the member 19? The 0 returned from mysqli_stmt_affected_rows() indicates that no rows are being updated.
My website is set up to capture the $returnToPage on most pages, so that when you log in, the log in script will redirect back to the page you were on before needing to log in.
Let’s say I am on “index.php” and I click on the “Log In” link in the page header. When I click “Log In” on the form, I see…
System Error
A Fatal Error has occurred. Please contact the System Administrator. (2126)
If I click on another tab (e.g. How-To Guides) and then I click back on the original tab I was on (e.g. “index.php”) then that page appears properly.
So while my Prepared Statement is running successfully and updating the Member record as it should, mysqli_stmt_affected_rows is not reflecting that record was updated?!
From your description there is nothing wrong with the updating of your row in the database, which means the query isn’t failing. Since the query isn’t failing, I don’t see how (if, as you say, the row is getting updated) the affected rows wouldn’t equal 1. Unless the query is being executed more than once (on the same page, or different page loads).
I don’t see anything particularly strange about what’s happening, something else is happening that we can’t see yet. A little more to go on would be nice, could you show the full page?
