How I can get an auto incremented value

Question

I have here a table that corresponds to the orders of the customers. I use AUTO_INCREMENT to determine the ID of the order. I have this SQL code to the orders table:

CREATE TABLE IF NOT EXISTS `orders` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `customer_name` varchar(500) NOT NULL,
  `order_total_price` decimal(20, 2) NOT NULL,
  `order_date` varchar(100) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB

What I need is to insert each of the products of that order in another table with a Foreign Key order_id to specify what order that products belongs to. The SQL code for the purchased_products table is:

CREATE TABLE IF NOT EXISTS `purchased_products` (
  `order_id` int (11) NOT NULL,
  FOREIGN KEY (`order_id`) REFERENCES orders(`order_id`),
  `product_name` varchar(500) NOT NULL,
  `product_price` decimal(20, 2) NOT NULL,
  `product_quantity` int(11) NOT NULL,
  PRIMARY KEY (`order_id`)
)

When the user buy something, I use this to insert the data in the orders table:

INSERT INTO orders (customer_id, customer_name, order_total_price, order_date)
VALUES ('{$customer_id}', '{$customer['customer_name']}', '{$order_total_price}', '{$order_date}')";

And here is my problem. I need to insert in the purchased_products table the products with the Order ID generated:

INSERT INTO purchased_products (order_id, product_name, product_price, product_quantity)
VALUES ('*/The ID of the order need to goes here*/', '{$product['product_name']}', '{$product['product_price']}', '{$product['quantity']}')";

This is giving me a headache. I'm not really knowing how to do it. This should be done by a different way? How do I associate the order ID to the products belonging to it?

There are ways to determine "just assigned" auto-ids, but pretty much each RDBMS has their own methods. Thus, the standard question: what RDBMS are you using? — Philip Kelley, Apr 22 '15 at 17:55
Just a suggestion .. use `MAX()` method.. This will figure out the last inserted `id` .. But note that this will help only if no other `insert` has been done on the orders table .. This is just a way — Magesh Kumaar, Apr 22 '15 at 17:57
The RDBMS knows what was the last inserted auto incremented id into the particular table. You can get it. In mysql it is called LAST_INSERT_ID as far as I remember. However, as @Philip Kelley mentioned it depends on the RDBMS you are using. — AndrasCsanyi, Apr 22 '15 at 18:01
fwiw, postgres sql supports the returning keyword, so you would add **returning order_id** at the end of your **. But this is mysql and this question would benefit greatly by the use of the **mysql** tag. — JL Peyret, Apr 22 '15 at 19:36
Thanks all for the help. Without you I would not have been able to solve the problem. I've posted an answer with my own solution. — Dyan, Apr 22 '15 at 20:33

score 1 · Answer 1 · answered Apr 22 '15 at 19:42

1

use function last_insert_id(). it will give you value that was auto-incremented as last one before call to it.

answered Apr 22 '15 at 19:42

murison

3,640
2
23
36

score 0 · Answer 2 · answered Apr 22 '15 at 17:56

You can get the get the last inserted primary key value by using @@IDENTITY

Here's the MSDN article: https://msdn.microsoft.com/en-us/library/ms187342.aspx

USE AdventureWorks2012;
GO
--Display the value of LocationID in the last row in the table.
SELECT MAX(LocationID) FROM Production.Location;
GO
INSERT INTO Production.Location (Name, CostRate, Availability, ModifiedDate)
VALUES ('Damaged Goods', 5, 2.5, GETDATE());
GO

SELECT @@IDENTITY AS 'Identity';
GO
--Display the value of LocationID of the newly inserted row.
SELECT MAX(LocationID) FROM Production.Location;
GO

I would also recommend wrapping the statement in a TRANSACTION so that if any errors occur you can rollback.

But I need to get the last primary key value from the table `orders` and then add it to `oder_id` in `purchased_products` table. — Dyan, Apr 22 '15 at 19:54

score 0 · Answer 3 · edited May 23 '17 at 12:14

0

you can use SCOPE_IDENTITY() to retrieve the last identity you inserted within the current sql session.

here is another question with a great description of all the differences:

identity scope Question

edited May 23 '17 at 12:14

Community

1
1

answered Apr 22 '15 at 18:15

Nathan Tregillus

6,006
3
52
91

score 0 · Answer 4 · answered Apr 22 '15 at 19:15

As others have commented it depends on the RDBMS. In Oracle you typically use sequences. You create and store the sequence on the database and can use it on an INSERT by doing sequencename.nextval().

Sequences let you control starting values, increment/decrement size, caching and a lot more.

score 0 · Accepted Answer · answered Apr 22 '15 at 20:30

I did it by using PDO lastInsertId() to get the ID of last inserted order:

$sql = "INSERT INTO orders (customer_id, customer_name, order_total_price, order_date)
        VALUES ('{$customer_id}', '{$customer['customer_name']}', '{$order_total_price}', '{$order_date}')";

$query = $connection->prepare($sql);    
$query->execute();

$respective_order_id = $connection->lastInsertId();

And then:

INSERT INTO purchased_products (order_id, product_name, product_price, product_quantity)
VALUES ('{$respective_order_id}', '{$product['product_name']}', '{$product['product_price']}', '{$product['quantity']}')";

Thanks for all who tried to help! They put me in the right way!

How I can get an auto incremented value

5 Answers5