mysql: store a DEFAULT value as a variable

Question

I am wondering how to store the value of a primary key, auto-incremented by "DEFAULT" so that I can insert it as a value in another table row.

So far I have:

$sql = "INSERT INTO sales (
            sale_id,
            sale_amt,
            sale_date) 
        VALUES (
            DEFAULT,  # <--- how to store the resulting value of this?
            '$amt',
            '$date'
            )";

I need the specific value created by "DEFAULT" to be stored, so that I can insert it into another table's row. How do I do this with either PHP or MySQL?

i think u need foregin key rather than that

M.chaudhry
– M.chaudhry

2014-05-27 19:26:30 +00:00
Commented May 27, 2014 at 19:26 — M.chaudhry
– M.chaudhry, Commented May 27, 2014 at 19:26

Funk Forty Niner · Accepted Answer · 2014-05-27 19:36:12Z

2

You don't. You use last_insert_id() to retrieve it AFTER you've performed the first insert.

INSERT INTO foo (bar) VALUES (baz)
SELECT @id := last_insert_id();
INSERT INTO other (id, parent) VALUES (null, @id)
INSERT INTO yetanother (id, parent) VALUES (null, @id)

Note that last_insert_id() is not smart and will return the id of the LAST insert you did. If you do two inserts, and then try to get the ids, you'll get the id of the second (last) insert.

edited May 27, 2014 at 19:36

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

answered May 27, 2014 at 19:26

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Archibald · Accepted Answer · 2014-05-27 19:44:27Z

1

MySQL: You can find this in MySQL by using the LAST_INSERT_ID() function, as the last insert ID will be returned that you inserted for an AUTO_INCREMENT value.

$sql = "INSERT INTO sales ( sale_id, sale_amt, sale_date) VALUES ( DEFAULT, # <--- how to store the resulting value of this? '$amt', '$date' )";

sql2 = "INSERT INTO records ( sale_id, customer_name, other_info) VALUES ( LAST_INSERT_ID(), <-------- correct sales_id '$name', '$info');

edited May 27, 2014 at 19:44

answered May 27, 2014 at 19:30

Archibald

4561 gold badge7 silver badges20 bronze badges

2 Comments

Marcus Adams Over a year ago

Consider the warning on the docs for mysql_insert_id(): "This extension is deprecated as of PHP 5.5.0, and will be removed in the future.".

Archibald Over a year ago

You're right. Removing the PHP and sticking solely to mySQL.

Collectives™ on Stack Overflow

mysql: store a DEFAULT value as a variable

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related