CODEWITHSUNDEEP
phpmysql
Tom
Tom

Reputation: 53

MySQL - Return generated AUTO_INCREMENT value on INSERT

Hopefully an easy one - I can't work out a robust solution.

I'm building a cart mechanism which generates a id_cart AUTO_INCREMENT Primary Key. The cart item has another table with cart product variables like size. I want to attribute the same id_cart in the size table.

CREATE TABLE
-> id_size_cart NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> id_cart //THIS WILL BE THE AI value from the product cart
-> size_name

I've seen SELECT LAST_INSERT_ID().

How solid will this be to ensure it's the last inserted ID? Don't want customers getting the wrong size!

__ EDIT ___

Thank you for the input. Still, I'm having trouble. See actual code below;

//add product to cart table
$addtocart_sql = "INSERT INTO store_cart (id_cart, id_session) VALUES ('', '".$_COOKIE["PHPSESSID"]."')";
$addtocart_res = mysqli_query($dbConnect, $addtocart_sql) or die(mysqli_error($dbConnect));

$last_row_id = mysqli_query($dbConnect, "SELECT LAST_INSERT_ID()") or die(mysqli_error($dbConnect));

echo 'INSERT worked!<br/>';
echo $last_row_id;

Error shows as

Parse error: syntax error, unexpected T_STRING in /websites/LinuxPackage05/4v/35/xy/4v35xy-55415.webfusion-hosting.co.uk/public_html/noff-group.com/dev/add_to_cart.php on line 27

Thanks in advance.

Upvotes: 2

Views: 4142

Answers (4)

Tom
Tom

Reputation: 53

My syntax was off a little - see the code below returning a correct LAST ID.

//add product to cart table
$addtocart_sql = "INSERT INTO store_cart (id_cart, id_session) VALUES ('', '".$_COOKIE["PHPSESSID"]."')";
$addtocart_res = mysqli_query($dbConnect, $addtocart_sql) or die(mysqli_error($dbConnect));

// REQUIRED 'i' + '$dbConnect' for MYSQLI function
echo 'INSERT worked!<br/>';
echo mysqli_insert_id($dbConnect);

Thanks for all the help - again!

Upvotes: 0

Code Poet
Code Poet

Reputation: 11437

I have read somewhere, I can be absolutely certain of that and the source where I read it, that if you want to be dead sure DON'T rely on LAST_INSERT_ID. Use the same parameters that you used to insert the record to select the newly created record and get its id. It has something to do with mysql's implementation and threading etc.

That said, I've never seen such an implementation, I don't use that method either. However, it helps to keep this in mind.

Upvotes: 0

GordonM
GordonM

Reputation: 31730

If you're using InnoDB as the table type, then you'll be able to make absolutely certain you get the correct id with by simply wrapping the whole thing in a transaction.

MyISAM doesn't have transactions, but it's behaviour when getting the last insert ID is based on the connection. If several connections are open simultaneously and they all insert into the same table, each connection will get back the ID for the record they inserted. It's less robust than the true transaction support innoDB offers but it is still unlikely that any one user will get the wrong ID.

Upvotes: 2

jensgram
jensgram

Reputation: 31508

From the manual:

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.

Upvotes: 8

Related Questions