after a succesful update, get data from mysql? 2

[altctrldel]

Well this is what I intend to do;

once the php scipt has succesfully updated the database from the user input, i need it to automatically retreive the primary key (id number which increments using the database ang not php) which it has been assigned to and then store it in the session for later use.

so does it go like:

step 1: update
step 2: select the end of the database row and get the id info? (but what if there are 2 or more users who have inputted data almost at the same time?

for example:
9pm: User 1 inputs info A which is assigned an id of A1
9pm: User 2 inputs info B which is assigned an id of B1

So the last row would be B1, user A would take down user 2's id numberB1) which is not good.

thank you for replying.

 

[Itshim]

If you want to capture an auto-increment id you can use the mysql_insert_id() function or the mysqli_insert_id() function depending on what MySQL extension you have installed.

 

[altctrldel]

ok I'll try that out. thank you. thank you. ^^

 

[rob51383]

Unique id's should almost always be set within the database. Using phpMyAdmin simply set the type as INT and the "extra" select box select "auto_incriment". Chances are, because the data is inserted then queried for the last entry in about .02 seconds you will never have to worry about selecting the last record which ends up being someone elses, but in any case here is what you can do:

$q1 = "INSERT into `table` (`text1`, `text2`, `text3`, `username`) VALUES ('$text1', '$text2', '$text3', '$username'";
$r1 = mysql_query($q1) or die('Query failed: ' . mysql_error());
$a1 = mysql_fetch_array($r1, MYSQL_NUM);

$q2 = "SELECT max(`id`) from `table` WHERE `text1`='$text1' and `text2`='$text2' and `text3`='$text3' and `username`='username'";
$r2 = mysql_query($q2) or die('Query failed: ' . mysql_error());
$a2 = mysql_fetch_array($r2, MYSQL_NUM);

$sessionid = $a[0];
print $sessionid\n;

Really, for the second query, if you have a 'username' you can just select max(id) where username=$username because one user will not input 10 things as once.

 

[Itshim]

Both methods described by rob51383 and myself would create a race condition within the code, which is what altctrldel is trying to avoid; because of this I offer another suggestion, although I would consider it overkill...

Some background information first:
To retrieve an id from MySQL, PHP uses the MySQL C API function mysql_insert_id(). Looking at the MySQL documentation:

The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients.

This seems like it would be safe to use since it is connection specific, but by default when using PHP's mysql_connect() function:

If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned.

This causes the race condition when using PHP's mysql_insert_id() function. Luckily PHP offers an optional new_link parameter to the mysql_connect() function:

The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters.

You could change all your connect calls setting the new_link parameter to true, this would guarantee that all interaction between PHP and MySQL would be kept within separate connections as long as you set the optional [resource link_identifier] parameter for function calls within the mysql family of functions.

The down side of this is that establishing a new connection for every connect call that uses the same credentials, could cause a large amount of overhead and would quickly become resource intensive, especially when most of the connections will not need to be exclusive. Because of this two types of connections would be needed; ones which will be using the mysql_insert_id() function (exclusive) and ones that are not.

The basic idea behind my approach is that every script would need to be able to handle the two types of connections previously mentioned. When the script requests a connection to MySQL the connect function would decide which type of connection is required. If the connection must be exclusive then it will call mysql_connect() with the new_link parameter set to true. If the connection does not need to be unique then the function will check if a generic connection already exists, and if so simply return that connection.

This approach would require setting the resource link_identifier parameter for the mysql_* functions, and implementing the connect wrapper function; in all scripts that use the same credentials.

Again this is probably overkill for the situation, but it would guarantee that no race conditions were present.