Jump to content

Successful Update Throws Error?!


doubledee

Recommended Posts

I am running an UPDATE query which runs successfully, but my code throws an error saying that the UPDATE did not occur?!  :shrug:

 

Specifically, I get a "MEMBER_UPDATE_FAILED_2126"

 

Here is my code...

// ************************
// Update Last Activity.	*
// ************************
if ((isset($_SESSION['loggedIn'])) && ($_SESSION['loggedIn'] == TRUE)){

	// Initialize variables.
	$loggedIn = 1;
	$memberID = 19;		// For Test Purposes
//		$memberID = (isset($_SESSION['memberID']) ? $_SESSION['memberID'] : '');


	// ************************
	// Update Member Record.	*
	// ************************

	// Connect to the database.
	require_once(WEB_ROOT . 'private/mysqli_connect.php');

	// Build query.
	$q = "UPDATE member
					SET logged_in=?,
							last_activity=now()
					WHERE id=?
					LIMIT 1";

	// Prepare statement.
	$stmt = mysqli_prepare($dbc, $q);

	// Bind variables to query.
	mysqli_stmt_bind_param($stmt, 'ii', $loggedIn, $memberID);

	// Execute query.
	mysqli_stmt_execute($stmt);

	// Verify Update.
	if (mysqli_stmt_affected_rows($stmt)!==1){
		// Update Failed.
		$_SESSION['resultsCode'] = 'MEMBER_UPDATE_FAILED_2126';

		// Redirect to Display Outcome.
		header("Location: " . BASE_URL . "/members/results.php");

		// End script.
		exit();
	}//End of UPDATE MEMBER RECORD

	// Close prepared statement.
	mysqli_stmt_close($stmt);

	// Close the connection.
	mysqli_close($dbc);
}//End of UPDATE LAST ACTIVITY

 

This was working earlier, so what is going on?!

 

Thanks,

 

 

Debbie

 

 

Link to comment
Share on other sites

What does....

 

mysqli_stmt_affected_rows($stmt)

 

return?

 

Apparently not 1.

 

How would I find out?

 

I tried...

echo mysqli_stmt_affected_rows($stmt);

...but I get an "Header Already Sent" error?!

 

(I've had my fill of those today!!)

 

 

Debbie

 

Link to comment
Share on other sites

Here is the top part of my file...

<?php
session_start();

require_once('config/config.inc.php');

// Include Function.
require_once('members/last_activity.php');

$_SESSION['returnToPage'] = $_SERVER['SCRIPT_NAME'];
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<!-- ################## DEBBIE ##################### -->
<!-- HTML Metadata -->
<title>Double Dee, Inc.</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<!-- Page Stylesheets -->
<link type="text/css" rel="stylesheet" href="css/_main.css" />
<link type="text/css" rel="stylesheet" href="css/_layout.css" />
<link type="text/css" rel="stylesheet" href="css/top_menu.css" />
<link type="text/css" rel="stylesheet" href="css/components.css" />
</head>
<body>
  <div id="pageWrapper" class="clearfix">
    <div id="pageInner">
		<!-- BODY HEADER -->
		<?php	require_once('components/body_header.inc.php');	?>

 

 

Debbie

 

Link to comment
Share on other sites

Thorpe,

 

Okay, I was able to do what you asked about...

 

I am logged out now and see this in the database...

logged_in = 0

last_activity = 2012-03-09 21:39:11

 

 

I added this code to my "last_activity.php"

	// Verify Update.
	if (mysqli_stmt_affected_rows($stmt)!==1){
			echo mysqli_stmt_affected_rows($stmt);
			exit();

 

And I see "0" on my screen.

 

 

After I see the above message, I see this in the database...

logged_in = 1

last_activity = 2012-03-09 21:40:53

 

 

So the User record is being "logged in", but no rows are affected and thus my error message is being falsely triggered and it screws up my expect outcome...

 

What in the world is going on???

 

If I try to log in from "index.php", after I click Log In that script tries to return ne back to "index.php" and I get the above error.  However, if I click on another page, and then click back on "index.php" it loads just fine with no false error.

 

"last_activity.php" is loaded every time the page is to update the User's activity, so there is something about logging in that is screwed up, but navigating from page to page appears to be working...

 

Hope you can follow what I'm saying!!

 

Thanks,

 

 

Debbie

 

 

Link to comment
Share on other sites

Dee on your ternary you realize that if the session does not return true it will just return empty right?

 

Right, and so that would cause the query would fail which is what I would want if there wasn't a $memberID.

 

I have code to error-handle such a case.

 

What about my OP though?

 

 

Debbie

 

Link to comment
Share on other sites

member is a reserved word in MYSQL if you want to keep it as member use backticks only thing i can think of.

 

Where do you get this stuff? Member is not a reserved word.

 

As for the issue, is there any way this code could be executing twice?

Link to comment
Share on other sites

The logic in the code in the OP makes no sense at all.  It reads... If the user is logged in, update the database to show that they are logged in.  Whaaa? If they're already logged in (as shown by $_SESSION['loggedIn'] being true), and you update the database then of course you'll get zero rows updated because the database will be trying to update the logged_in column from 1 to 1 again.

Link to comment
Share on other sites

The logic in the code in the OP makes no sense at all.  It reads... If the user is logged in, update the database to show that they are logged in.  Whaaa? If they're already logged in (as shown by $_SESSION['loggedIn'] being true), and you update the database then of course you'll get zero rows updated because the database will be trying to update the logged_in column from 1 to 1 again.

 

Not that simple...

 

God is this hard to explain.

 

The fields "logged_in" and "last_activity" are entirely for determining which "Online Status" icon to display for a User in the Comments section below an Article.  (I can't use the $_SESSION['loggedIn'] for every User that commented on an Article that Bob is reading.  Follow me?!)

 

I probably can get by with the field "last_activity" but that is immaterial.

 

So my code from hell is trying to do this...

 

IF a User is logged in, then every time he/she surfs to a new page, the "last_activity.php script updates the "last_activity" field to the current time.  I then check "last_activity" against the current time to determine if a User is "Online", "Idle" or "Offline".  IF a User is NOT logged in, then I need not update "last_activity" because that is only a tracking mechanism for Online Users.

 

Follow me?

 

I originally had this code in my "body_header.inc.php" script, but it was causing problems with "Headers already sent", so I stripped out the code and put it into a standalone file, which I require at the top of nearly every script like this...

 

index.php

<?php
// Initialize a session.
session_start();

// Access Constants.
require_once('config/config.inc.php');

// Update Activity.
require_once('members/last_activity.php');

 

I can't ever remember one little block of code upending my entire code-base like this one?!  Ugh!

 

Hope you can better follow what I am trying to do...

 

Thanks,

 

 

Debbie

 

Link to comment
Share on other sites

Yep I follow you. The trouble looks to be that you consider zero updated rows as an error when that's not the case.  If the logged_in is already 1, and you update it to 1, then MySQL will report zero rows updated --- but that's fine. You don't want to give an error message when no rows are updated.

 

It looks to me like you should really be checking on the return value of mysqli_stmt_execute(), to see if something went wrong with the query; not counting how many rows changed.

Link to comment
Share on other sites

Yep I follow you. The trouble looks to be that you consider zero updated rows as an error when that's not the case.  If the logged_in is already 1, and you update it to 1, then MySQL will report zero rows updated --- but that's fine.

 

Oh, I didn't know it worked that way.

 

And, yes, this all arose out of my desire to handle every possible place where my code could blow up.  (I have really been trying hard to go the extra-mile to have error-handling for everything.)

 

 

You don't want to give an error message when no rows are updated.

 

It looks to me like you should really be checking on the return value of mysqli_stmt_execute(), to see if something went wrong with the query; not counting how many rows changed.

 

Fair enough.

 

Yeah, I just wanted to say, "If for some reason we are unable to update the values of 'logged_in' and 'last_activity' then there must be something wrong with the code/database and we should report an error."

 

 

Anyways, so based on the original code I posted in #1, how can I improve my code to handle errors if something blows up, while not checking things the way that I currently am?

 

My goal was just to have safe code, but it looks like I screwed up again?!

 

Thanks,

 

 

Debbie

 

Link to comment
Share on other sites

Oh, I didn't know it worked that way.

 

Now you do!  :idea:

 

 

I just wanted to say, "If for some reason we are unable to update the values of 'logged_in' and 'last_activity' then there must be something wrong with the code/database and we should report an error."

 

Anyways, so based on the original code I posted in #1, how can I improve my code to handle errors if something blows up, while not checking things the way that I currently am?

 

The only necessary change, to do what you described, would be:

 

From this

		// Execute query.
	mysqli_stmt_execute($stmt);

	// Verify Update.
	if (mysqli_stmt_affected_rows($stmt)!==1){

 

To this

		// Execute query.
	$success = mysqli_stmt_execute($stmt);

	// Verify Update.
	if ($success === FALSE) {

 

My goal was just to have safe code, but it looks like I screwed up again?!

 

Don't beat yourself up over not realising that zero affected rows isn't an error. Learning material, particularly on the internets, often stresses the supposed importance of checking for the number of affected rows for INSERT/UPDATE/DELETE queries, as an indicator of success/failure. So much so, that it is easy to miss the fact that sometimes when there really is nothing to update, depending on what the script is trying to do, it's okay to not have any rows affected.

Link to comment
Share on other sites

salathe,

 

Don't beat yourself up over not realising that zero affected rows isn't an error. Learning material, particularly on the internets, often stresses the supposed importance of checking for the number of affected rows for INSERT/UPDATE/DELETE queries, as an indicator of success/failure. So much so, that it is easy to miss the fact that sometimes when there really is nothing to update, depending on what the script is trying to do, it's okay to not have any rows affected.

 

Thanks for the support.  :)

 

Yeah, I thought "affected rows" meant that the query touched at least one row even if the values didn't change, but now I see where I had it wrong.

 

As far as fixing my code, how about this...

// Connect to the database.
require_once(WEB_ROOT . 'private/mysqli_connect.php');

// Build query.
$q = "UPDATE member
		SET logged_in=?,
			last_activity=now()
		WHERE id=?
		LIMIT 1";

// Prepare statement.
$stmt = mysqli_prepare($dbc, $q);

// Bind variables to query.
mysqli_stmt_bind_param($stmt, 'ii', $loggedIn, $memberID);

// Execute query.
if (!mysqli_stmt_execute($stmt)){
	// Update Failed.
	$_SESSION['resultsCode'] = 'MEMBER_UPDATE_FAILED_2126';

	// Redirect to Display Outcome.
	header("Location: " . BASE_URL . "/members/results.php");

	// End script.
	exit();
}

// Close prepared statement.
mysqli_stmt_close($stmt);

// Close the connection.
mysqli_close($dbc);

 

That was my best stab at things...

 

 

Debbie

 

P.S. Actually I think I like your way better, but it would still be good to know if my way would work too!

 

Link to comment
Share on other sites

Sure, your way would work just fine.  :thumb-up:

 

Okay, so I think I've got things working now - although technically they were before.

 

So I have one last BIG PROBLEM on this topic...

 

Nearly all of my scripts start off like this...

<?php
// Initialize a session.
session_start();

// Access Constants.
require_once('../config/config.inc.php');

// Update Last Activity.
require_once('../members/last_activity.php');

 

 

But when I try this in my article.php script I get a page full of database errors?!

 

This is going to be super hard to describe, but let me provide at least snippets...

 

My website has database-driven Articles, and using "pretty URL's".

 

Let's say you are reading the Articles Index and you want to read Article #1, so you click on the link below...

<!-- Summary #1 -->
<div class="articleSummary">
<h3>Postage Meters Can Save You Money</h3>
	<a href="<?php echo BASE_URL; ?>/articles/postage-meters-can-save-you-money">
		<img src="<?php echo BASE_URL; ?>/images/PostageMeter.png" width="170" />
	</a>

 

 

Here is a snippet of article.php...

<?php

// Initialize a session.
session_start();

// Access Constants.
require_once('../config/config.inc.php');

// Update Last Activity.
require_once('../members/last_activity.php');

// ******************************
// Attempt to Retrieve Article.	*
// ******************************
if (isset($_GET['slug']) && $_GET['slug']) {
	// Slug found in URL.

	// Set variable.
	$slug = $_GET['slug'];

	// ************************
	// Find Article Record.		*
	// ************************
	// Connect to the database.
	require_once(WEB_ROOT . 'private/mysqli_connect.php');

	// Set current Script Name.
	$_SESSION['returnToPage'] = $_SERVER['SCRIPT_NAME'] . '?slug=' . $slug;

	// Build query.
	$q1 = 'SELECT id, title, description, keywords,
								heading, sub_heading, published_on, author,
								body, reference_listing, endnote_listing
					FROM article
					WHERE slug=?';

	// Prepare statement.
	$stmt1 = mysqli_prepare($dbc, $q1);

	// Bind variable to query.
	mysqli_stmt_bind_param($stmt1, 's', $slug);

	// Execute query.
	mysqli_stmt_execute($stmt1);

	// Store results.
	mysqli_stmt_store_result($stmt1);

	// Check # of Records Returned.
	if (mysqli_stmt_num_rows($stmt1)==1){
		// Article was Found.
		$articleExists = TRUE;

		// Bind result-set to variables.
		mysqli_stmt_bind_result($stmt1, $articleID, $title, $description, $keywords,
									$heading, $subHeading, $publishedOn, $author,
									$body, $referenceListing, $endnoteListing);

		// Fetch record.
		mysqli_stmt_fetch($stmt1);

		// Close prepared statement.
		mysqli_stmt_close($stmt1);

 

 

If I run the code as-is above I get a page full of errors including...

Warning: mysqli_prepare() [function.mysqli-prepare]: Couldn't fetch mysqli in /Users/user1/Documents/DEV/++htdocs/05_Debbie/articles/article.php on line 47

 

Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, null given in /Users/user1/Documents/DEV/++htdocs/05_Debbie/articles/article.php on line 50

 

Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, null given in /Users/user1/Documents/DEV/++htdocs/05_Debbie/articles/article.php on line 53

 

Warning: mysqli_stmt_store_result() expects parameter 1 to be mysqli_stmt, null given in /Users/user1/Documents/DEV/++htdocs/05_Debbie/articles/article.php on line 56

 

and so on...

 

 

But if I move my "Last Activity" include to the very end of the above PHP, things work fine?!

// Update Last Activity.
require_once('../members/last_activity.php');

 

I have looked over my code and I don't see what is going on?!

 

It is almost like the Database Connections or the Prepared Statements are "colliding"?!

 

I want to figure out what is going on, so I don't overlook a bigger issue...

 

Thanks,

 

 

Debbie

 

 

Link to comment
Share on other sites

Inside your last_activity.php file, you close the connection to the database with mysqli_close($dbc).  This means that for anything below where you include that file, the database connection is not available.  Moving that require_once() to the end "fixes" things because if it's the last thing using the database then it's okay to close the connection.

 

The simplest thing to do is delete that line of code which closes the connection.

Link to comment
Share on other sites

Inside your last_activity.php file, you close the connection to the database with mysqli_close($dbc).  This means that for anything below where you include that file, the database connection is not available.  Moving that require_once() to the end "fixes" things because if it's the last thing using the database then it's okay to close the connection.

 

Okay, but in "article.php" I also have this after my "last_activity.php" include...

	// ************************
	// Find Article Record.		*
	// ************************

	// Connect to the database.
	require_once(WEB_ROOT . 'private/mysqli_connect.php');

 

Shouldn't that create a new connection even though I did close the first one at the end of "last_activity.php" like you said?!

 

 

The simplest thing to do is delete that line of code which closes the connection.

 

It isn't bad to leave the connection open?

 

Also, does it matter where I include "last_activity.php"?

 

Thanks,

 

 

Debbie

 

 

Link to comment
Share on other sites

Shouldn't that create a new connection even though I did close the first one at the end of "last_activity.php" like you said?!

 

Since your using require_once that file is only ever going to be included in your script ONCE.  Since you already included it in your last_activity.php file, this second call to it is essentially ignored. 

 

It isn't bad to leave the connection open?

 

PHP will automatically clean up any open connections when your script ends.  Unless your writing a long-running script but don't need the DB access for the whole duration of the script, you don't really gain anything by trying to close the connection early.  It just adds confusion and complication to your code by making you have to remember when a connect is or isn't open.  Also it's better to open the connection once, do everything you need then close it instead of opening a new connection for each query (or group of queries).

 

 

Link to comment
Share on other sites

Shouldn't that create a new connection even though I did close the first one at the end of "last_activity.php" like you said?!

 

Since your using require_once that file is only ever going to be included in your script ONCE.  Since you already included it in your last_activity.php file, this second call to it is essentially ignored. 

 

That isn't what I said...

 

I said that at the top of "article.php" I require "last_activity.php"...

// Update Last Activity.
require_once('../members/last_activity.php');

 

 

And that "last_activity.php" opens and closes a database connection like this...

	// Connect to the database.
	require_once(WEB_ROOT . 'private/mysqli_connect.php');


	// Close prepared statement.
	mysqli_stmt_close($stmt);

	// Close the connection.
	mysqli_close($dbc);

 

But back in "article.php" - after the require - I have...

	// Connect to the database.
	require_once(WEB_ROOT . 'private/mysqli_connect.php');

 

So that should take care of my required file opening and closing the connection, right?!

 

 

It isn't bad to leave the connection open?

 

PHP will automatically clean up any open connections when your script ends.  Unless your writing a long-running script but don't need the DB access for the whole duration of the script, you don't really gain anything by trying to close the connection early.  It just adds confusion and complication to your code by making you have to remember when a connect is or isn't open.  Also it's better to open the connection once, do everything you need then close it instead of opening a new connection for each query (or group of queries).

 

Okay, you got me on that last part.

 

So if I forgot and left a connection open it wouldn't be a resource hog or security issue?

 

 

Debbie

 

Link to comment
Share on other sites

So that should take care of my required file opening and closing the connection, right?!

 

No, because that file is not included and thus, the code inside of it is not run. It's not included because it's already been previously included in your last_activity.php file.  Because it's not included and the code is not run, it will not re-connect to mysql.

 

The once-ness of require_once/include_once is not per-file, it's per-request.  It does not matter if the file was included first in some other file.  If it has been included at all during the current request, it will not be included anymore.

 

So if I forgot and left a connection open it wouldn't be a resource hog or security issue?

 

No, it would not.  It's quite common to let PHP handle closing the database resources on script end rather than trying to manage it yourself.

 

Link to comment
Share on other sites

No, because that file is not included and thus, the code inside of it is not run. It's not included because it's already been previously included in your last_activity.php file.  Because it's not included and the code is not run, it will not re-connect to mysql.

 

The once-ness of require_once/include_once is not per-file, it's per-request.  It does not matter if the file was included first in some other file.  If it has been included at all during the current request, it will not be included anymore.

 

Kicken, you still missed what I said above...

 

 

I said...

But back in "article.php" - after the require to "last_activity.php" I have...

// Connect to the database.
require_once(WEB_ROOT . 'private/mysqli_connect.php');

 

 

So, YES, "article.php" does re-connect to MySQL, because it includes...

// Make the connection.
$dbc = @mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
						OR die('Could not connect to database.  Contact System Administrator.');

 

 

Debbie

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.