Page 1 of 1

UPDATE query failing to return correct affected_rows value

Posted: Fri Apr 29, 2005 9:22 pm
by The Monkey
There's this problem with my Session class that has me stumped. Basically, I figured I could save a query to the database (When not creating a new session) by attempting to refresh the session (Update the session time, and the http_referer), and, if no rows were affected, then we obviously need to create a new session because the user's sessionID isn't logged in the database.

This works fine, except when you refresh more than once quickly. If you refresh at a faster rate than about 1 second, you get:

Code: Select all

Error Type : 256 :: Error: Duplicate entry '7002e886904e59041bf4f11655a2eaed' for key 1
SQL: INSERT INTO `sessions` SET `sessionID` = '7002e886904e59041bf4f11655a2eaed', `userID` = '-1', `session_begin` = '1114827569', `session_time` = '1114827569', `referer` = 'http://www.yogler.net/beta_test.4.26.05.php' :: File:  /home/yogle/includes/classes/db/db.class.php :: Line: 179

Code: Select all

$refresh = new DB();
$refresh->prepare( CURRENT_TIME, $referer, $this->identifier );
$refresh->query(&quote;UPDATE `sessions` SET `session_time` = ':1', `referer` = ':2' WHERE `sessionID` = ':3'&quote;);
		
//
// If no affected rows, then we have to create a session
//
				
if ( $refresh->affected_rows() < 1 )
{
	$create = new DB();
	$create->prepare( $this->identifier, $userID, CURRENT_TIME, $referer);
	$create->query(&quote;INSERT INTO `sessions` SET `sessionID` = ':1', `userID` = ':2', `session_begin` = ':3', `session_time` = ':3', `referer` = ':4'&quote;);		
}
Why is this happening, and is the only solution to have a third query (a "does session exist" query, if exists, refresh, else create session)? :?

Thanks, :)
- Monkey

Posted: Sat Apr 30, 2005 12:09 am
by ol4pr0
Why not just something like

Code: Select all

$do_update = $refresh->query("UPDATE `sessions` SET `session_time` = ':1', `referer` = ':2' WHERE `sessionID` = ':3'");     
#do you really want to know howmany rows there were affected ?
if ( $do_update ) {
## and whatever you want to do
}