SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Member macworks's Avatar
    Join Date
    May 2004
    Location
    Minneapolis, MN
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Problems with mysql_affected_rows and sessions

    I've built a site that is storing sessions into a MySQL table and I've been encountering some errors with updating/inserting session records. For the most part the sessions are working. I've built in some error logging and reporting functions and on occasion, I get an error report about a duplicate entry while trying to insert a new record. I'm using mysql_affected_rows to determine if the update succeeded and then based on it's success or failure, it either returns or inserts a new record.

    I guess what I'm wondering about is the scope of mysql_affected_rows. Let's say I've got more than one person using the site simultaneously and their actions are resulting in queries to the database. Does mysql_affected_rows only ask about the last transaction to the database (in general) or does it look at the last transaction to the database within the scope of the currently executing script block?

    Following is my SessionWrite function:

    PHP Code:
        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_sqltrue );

                if ( !
    $session_res ) return false;
                if ( 
    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;

            } 
    // END FUNCTION 
    I know I can specify the result resource handle in the mysql_affected rows call like this -- mysql_affected_rows( $session_res ) -- but that consistently produces an error if the session does not already exist.

    NOTE: The statement -- if ( !$session_res ) return false; -- only returns false if there was an error with issuing the query altogether and is not a test to determine if rows were affected or not, thus the mysql_affected_rows statement.

    On the whole, the functions works properly (most of the time), that's why I'm thinking that mysql_affected_rows is asking the MySQL daemon how many rows were affected by the last query issued to the daemon, not necessarily the last query issued by the given instance of the running php script. And therefore is only a problem when there are multiple simultaneous users.

    Can someone help clarify this for me and/or perhaps offer some suggestions as to how this could be done differently? I guess I could throw in a third query (SELECT) to see if a session already exists, but it's more queries and I would like to keep it as efficient as possible.

  2. #2
    SitePoint Member macworks's Avatar
    Join Date
    May 2004
    Location
    Minneapolis, MN
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To add some clarification to the above questioning, the error I'm getting is as follows:

    query = 'INSERT INTO sessions (`ses_id`, `ses_time`, `ses_start`, `ses_value`) VALUES ('2d2cafa20e020f837d0e5c5c78c12c4f', '1106332492', '1106332492', '')' error = 1062 : Duplicate entry '2d2cafa20e020f837d0e5c5c78c12c4f' for key 1


    I guess I also have another question to pile on:

    When PHP starts a new session, it creates a random session ID. But does it check to see if another session with the same ID already exists? And if so, does it continue to try creating new session IDs until it finds and ID that doesn't already exists?

    Another way of putting it is: Is it possible for two different people to have the same session ID -- even though they shouldn't.

    I realize it's a long-shot considering that the session IDs are so long and there are literally millions of unique ID possibilities.

    Perhaps adding the third SELECT query is the only way to do it right.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •