doubledee Posted March 10, 2012 Share Posted March 10, 2012 I am running an UPDATE query which runs successfully, but my code throws an error saying that the UPDATE did not occur?! 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 Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/ Share on other sites More sharing options...
trq Posted March 10, 2012 Share Posted March 10, 2012 What does.... mysqli_stmt_affected_rows($stmt) return? Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325760 Share on other sites More sharing options...
doubledee Posted March 10, 2012 Author Share Posted March 10, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325761 Share on other sites More sharing options...
doubledee Posted March 10, 2012 Author Share Posted March 10, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325762 Share on other sites More sharing options...
doubledee Posted March 10, 2012 Author Share Posted March 10, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325764 Share on other sites More sharing options...
darkfreaks Posted March 10, 2012 Share Posted March 10, 2012 Dee on your ternary you realize that if the session does not return true it will just return empty right? Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325765 Share on other sites More sharing options...
doubledee Posted March 10, 2012 Author Share Posted March 10, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325767 Share on other sites More sharing options...
darkfreaks Posted March 10, 2012 Share Posted March 10, 2012 member is a reserved word in MYSQL if you want to keep it as member use backticks only thing i can think of. Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325779 Share on other sites More sharing options...
trq Posted March 10, 2012 Share Posted March 10, 2012 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? Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325789 Share on other sites More sharing options...
doubledee Posted March 10, 2012 Author Share Posted March 10, 2012 As for the issue, is there any way this code could be executing twice? No that I have seen so far... I am busy pouring over my code and trying to step through it in NetBeans to determine what is going on, but I'm stuck?! Debbie Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325930 Share on other sites More sharing options...
Pikachu2000 Posted March 10, 2012 Share Posted March 10, 2012 Dee on your ternary you realize that if the session does not return true it will just return empty right? Where do you see ternary syntax in that code? I've looked through it 4 times, and I'll be damned if I can spot it. Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325949 Share on other sites More sharing options...
salathe Posted March 10, 2012 Share Posted March 10, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325962 Share on other sites More sharing options...
doubledee Posted March 10, 2012 Author Share Posted March 10, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325976 Share on other sites More sharing options...
salathe Posted March 10, 2012 Share Posted March 10, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325979 Share on other sites More sharing options...
doubledee Posted March 10, 2012 Author Share Posted March 10, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325980 Share on other sites More sharing options...
salathe Posted March 10, 2012 Share Posted March 10, 2012 Oh, I didn't know it worked that way. Now you do! 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. Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325987 Share on other sites More sharing options...
doubledee Posted March 10, 2012 Author Share Posted March 10, 2012 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! Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325988 Share on other sites More sharing options...
salathe Posted March 10, 2012 Share Posted March 10, 2012 P.S. Actually I think I like your way better, but it would still be good to know if my way would work too! Sure, your way would work just fine. Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325992 Share on other sites More sharing options...
doubledee Posted March 10, 2012 Author Share Posted March 10, 2012 Sure, your way would work just fine. 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 Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1325996 Share on other sites More sharing options...
salathe Posted March 10, 2012 Share Posted March 10, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1326000 Share on other sites More sharing options...
doubledee Posted March 10, 2012 Author Share Posted March 10, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1326001 Share on other sites More sharing options...
kicken Posted March 11, 2012 Share Posted March 11, 2012 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). Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1326039 Share on other sites More sharing options...
doubledee Posted March 11, 2012 Author Share Posted March 11, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1326051 Share on other sites More sharing options...
kicken Posted March 11, 2012 Share Posted March 11, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1326067 Share on other sites More sharing options...
doubledee Posted March 11, 2012 Author Share Posted March 11, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/258628-successful-update-throws-error/#findComment-1326069 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.