Page 1 of 1

mysql_affected_rows and it's scope

Posted: Sun Jan 23, 2005 4:55 pm
by macworks
I'm experiencing some inconsistent behaviors with sessions which I'm storing in a mysql table. For the most part, the sessions are working, but on occasion, my scripts are catching and reporting an error with regards to my session write function.

I know the problem is with the mysql_affected_rows function.

I'm beginning to think that mysql_affected_rows (with no parameter) falls outside the scope of the currently executing instance of the script -- AKA -- it asks the MySQL daemon how many rows were affected by the last query that was issued to it -- even if it wasn't issued by the currently executing script.

Would I be correct in my thinking?

Posted: Sun Jan 23, 2005 5:12 pm
by Bill H
Form the php manual:
mysql_affected_rows() returns the number of rows affected by the last INSERT, UPDATE or DELETE query associated with link_identifier. If the link identifier isn't specified, the last link opened by mysql_connect() is assumed.
Note that it doesn't say that the function is script-specific.
So, to answer your question, yes: the last operation regardless of script.

Posted: Sun Jan 23, 2005 5:21 pm
by macworks
Well then I offer up my script for suggestions. I realize that I could add a third SELECT query, but would prefer not to if I don't have to.

Code: Select all

function DB_SessionWrite( $ses_id, $data )
        {
            $session_sql = "UPDATE sessions SET ses_time='" . time() . "', ses_value='$data' WHERE ses_id='$ses_id'";
            $session_res = @DB_Query($session_sql, true );

			if ( !$session_res ) return false;
			if ( 0 < mysql_affected_rows()) return true;
            
            $session_sql = "INSERT INTO sessions (`ses_id`, `ses_time`, `ses_start`, `ses_value`) VALUES ('$ses_id', '" . time() . "', '" . time() ."', '$data')";
            $session_res = @DB_Query($session_sql);

            if (!$session_res)  return false;
            return true;

        &#125; // END FUNCTION
Note that if I supply the result resource, the script will spit out errors.

Posted: Sun Jan 23, 2005 11:27 pm
by Bill H
I would suggest using mysql_query($querystring, $link) function.
Then you can use mysql_affected_rows($link) successfully.
You will need to pass the $link variable into your function.

(Both functions, however, will work without specifying $link, using the last opened database resource. But I never like to do that myself, I always prefer to be specific.)

Posted: Mon Jan 24, 2005 2:53 pm
by macworks
Bill, thanks for your response but I'm not sure I understand. I'm already using what you suggested, but it doesn't change what's returned by the mysql_query call. When the UPDATE query fails (when no record to update exists), a resource handler is not returned. Which is right where the problem is. I think PHP or MYSQL should be providing a resource with some useful information versus throwing a warning.

Here's my MySQL Query function. If you look at my session write function from the post above, you'll get a sense for how the two are working together.

Code: Select all

function DB_Query( $query, $suppress_err=false )
		&#123;
			if ( !$GLOBALS&#1111;'db_connect'] ) $GLOBALS&#1111;'db_connect'] = DB_Connect(); // DB_Connect function is set to maintain persistent connections. Therefore this statement tests to see if the connection has already been created and only creates a new one if a connection does not already exist.

			if ( $GLOBALS&#1111;'db_connect'] )
				&#123;
					$query_result = mysql_query( $query, $GLOBALS&#1111;'db_connect'] );

					if ( !$suppress_err ) $mysql_error = DB_GetDBError();
					if ( $mysql_error && $GLOBALS&#1111;'inc_output_core'] ) Output_ProcessError("A database error occurred while attempting to issue a query.", "query = '$query' error = $mysql_error", __LINE__, __FILE__, __FUNCTION__);

					return $query_result;
				&#125;
			else if ( $GLOBALS&#1111;'inc_output_core'] ) Output_ProcessError("A connection to the database could not be made. IMMEDIATE ACTION should be taken to resolve this matter. Check to make sure the database is accessible and that permissions are properly set. If everything seems in order with the database, check the calling script for errors.", "", __LINE__, __FILE__, __FUNCTION__);

			return false;

		&#125; // END FUNCTION
I'm still thinking my only affective way around this is to add a SELECT statement to my SessionWrite function in order to see if a record exists before attempting to update / insert the session record.

Any advice or clarification would be very much appreciated.

I've tried testing my theory that mysql_affected_rows asks MySQL about the last transaction it received by manually updating a few records (at the command line) and then having PHP call mysql_affected_rows() (with no parameter) but it reports nothing. So I'm still confused.

Posted: Mon Jan 24, 2005 3:06 pm
by Bill H
Sorry, you are doing some sort of global thing that I do not see in your code. The function you posted originally can be quite self-contained without the need for the $GLOBAL thing, unless the $GLOBAL is doing something that I am unaware of.

mysql_query() will return FALSE if there was a database error and you can handle it right there -- you don't need an extra function to do that.

Or to catch the error without a statement and handle it, do:

Code: Select all

@mysql_query(etc) or die(etc);

Posted: Mon Jan 24, 2005 5:22 pm
by Bill H
macworks: This should do what you are looking for. You can divide it up into functions as needed.

Code: Select all

<?php
$Host = "myhost";                    // server name
$User = "username";                  // database param
$Password = "password";              // database param
$Link = @mysql_pconnect($Host, $User, $Password);
if (!$Link)
&#123;    die("Database connection failed.");
&#125;
if (!@mysql_select_db($dbmName, $Link))    // link to the database
&#123;    die("Database selection failed.");
&#125;
$sql = "UPDATE sessions SET ses_time='" . time() . "', ses_value='$data' WHERE ses_id='$ses_id'";
if (!@mysql_query($sql, $Link))
&#123;    die("Database connection failed.");
&#125;
elseif (@mysql_affected_rows($Link) == 0)   // no record found to update
&#123;
     $sql = "INSERT INTO sessions (`ses_id`, `ses_time`, `ses_start`, `ses_value`) VALUES ('$ses_id', '" . time() . "', '" . time() ."', '$data')";
     if (!@mysql_query($sql, $Link))
          die("Database connection failed.");
&#125;
?>