mysql_affected_rows and it's scope

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
macworks
Forum Newbie
Posts: 6
Joined: Sun Jan 23, 2005 4:25 pm
Location: Minneapoils, MN - USA

mysql_affected_rows and it's scope

Post 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?
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post 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.
macworks
Forum Newbie
Posts: 6
Joined: Sun Jan 23, 2005 4:25 pm
Location: Minneapoils, MN - USA

Post 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.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post 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.)
macworks
Forum Newbie
Posts: 6
Joined: Sun Jan 23, 2005 4:25 pm
Location: Minneapoils, MN - USA

Post 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.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post 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);
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post 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;
?>
Post Reply