Forums | MacLife

resedit · 2009-01-14 7:07 pm

I seem unable to update a record in the same script that created the record being updated.
I can update the record in a different script using identical syntax, just not in the same php script that the record is created in.

Does anyone know more about this limitation?

Yes, there is a reason why I can't modify the insert of the record to include the update - the insert of the record is the creation of a php session id (using database for sessions) - so the record is created with session_regenerate_id(); function.

The table that stores php session ID and the session variables also contains a row for the userid - when a php session is created, it is created with 0 as the user ID - I need to update the record at login to put the users real userid in that field (I don't want to store userid in the php session variable, I want to easily and quickly query all session ID's that user is logged in with - I keep expired sessions in the database for several weeks, and also log the IP address that the session was created for in the php_sessions database, etc.)

Anyway - not all functions on the site require a log in, so when a user does log in I need to generate a fresh session ID (security reasons) and then want to set the userid field to their userid.

so right now - my code for successful login is :

Code:

$old_sessionid = mysql_real_escape_string(session_id());
session_regenerate_id();
$new_sessionid = mysql_real_escape_string(session_id());
$sql = "UPDATE php_sessions SET expired=1 WHERE session_id='$old_sessionid'";
mysql_query($sql);
$sql = "UPDATE php_sessions SET userid=$userid WHERE session_id='$new_sessionid'";
mysql_query($sql);

It seems that the way php is doing it's sql queries, even though $new_sessionid has a value when set, the mysql record associated with that session id can not be updated until the php script has exited.

Anyone know why this is and/or how to work around it?

resedit · 2009-01-14 9:28 pm

Interestingly - when you write session data, the mysql data isn't input until after the script has run.
IE you can set/change session variables under the old session, after setting them regenerate the session id, and they only are written to the database row for the new session.

For now I'm just setting a userid session id variable and any time a new session is created, it looks to see if the userid session variable is set - and updates the database if it is, but that's another query each time a page loads. It solves the problem though.

b_dubb · 2009-01-15 12:07 am

off the top of my head ....

you have two mysql_query($sql) in the same code. is it possible to unset() those? as there may be some crosstalk there?

i'm guessing but ... at first glance that's the best i got

b

resedit · 2009-01-15 1:29 am

When I comment out one it still fails.
I'm guessing that the actual problem is how session_regenerate_id(); works.
I'm guessing the actual php session itself isn't really created until the php script that calls it exits - just like session variables are not actually inserted into the session until after the php script exits. Thus when I try to update the row associated with the new session, it fails because the row hasn't actually been inserted into the database yet.

So it looks like what I have to do is store the user id associated with the username and password hash as a session variable (that works) and then on the "thank you for log in" page, do the sql update the the session_id table. That will work just dandy.

Altivec · 2009-01-16 1:31 am

If you find out through debugging that the problem is not with the session creation being done at script exit, and you are running MySQL 4 or newer, you can try running your update queries in a transaction.

Code:

$old_sessionid = mysql_real_escape_string(session_id());
session_regenerate_id();
$new_sessionid = mysql_real_escape_string(session_id());
@mysql_query("BEGIN");
$sql = "UPDATE php_sessions SET expired=1 WHERE session_id='$old_sessionid'";
mysql_query($sql);
@mysql_query("COMMIT");
@mysql_query("BEGIN");
$sql = "UPDATE php_sessions SET userid=$userid WHERE session_id='$new_sessionid'";
mysql_query($sql);
@mysql_query("COMMIT");

Your table must also be InnoDB.

The other option would be to close your connection after the first query, then reopen it, but this would be slower than using transactions.

Transactions are also helpful for trapping errors. If you are making any changes to the DB, you can check if the query is successful ($queryresult = @mysql_query($sql) wink , and if $result is not true you can rollback the transaction with peace of mind knowing that bad data won't make it into your DB regardless of the reason why the update failed.

resedit · 2009-01-16 9:43 pm

The table is MyISAM but it's not an issue, I just modified my class that handles php sessions to update the userid field to the contents of $_SESSION["userid"] if it's set, so I don't need to do anything, it's taken care of automagically when start_session() is run (In addition to recording the UID associated with the ID, I record the IP it was created on and the most recent IP that used the session ID)

I'm fairly certain though the issue is how session_regenerate_id(); works.
It actually makes sense - you can't send/modify a cookie after you start sending data, so it makes sense for any changes to the session to be done after the page loads - as the cookie with the session ID won't be immediately modified anyway until the current script has run. So it just keeps it all in memory until then.

Forums | MacLife

#1 2009-01-14 7:07 pm

weird php/mysql issue

Code:

#2 2009-01-14 9:28 pm

Re: weird php/mysql issue

#3 2009-01-15 12:07 am

Re: weird php/mysql issue

#4 2009-01-15 1:29 am

Re: weird php/mysql issue

#5 2009-01-16 1:31 am

Re: weird php/mysql issue

Code:

#6 2009-01-16 9:43 pm

Re: weird php/mysql issue

Board footer