INSERT… SELECT and then GetLastInsertID…

Continuation of the theme.

In general, I add a new row to the table in this way:

INSERT INTO `table` (f1,f2,f3,f4) 
SELECT 4,f2,f3,f4 FROM `table` WHERE f1=3;

After that I want to get the id (AUTOINCREMENT)of this line. If you just call insert, then idreturns correctly, and if you add selectto insert, then it returns 0 ;

What can be done and how can I get the last id?

SOLVED

In general, I solved the problem like this:

$db->Query("SELECT LAST_INSERT_ID()");
$row=$db->RowArray();
$id=$row[0];

But still, it’s somehow strange to everything Well, if the last-id is not needed in the script code itself, then you can stupidly call the last_insert_id () function in the next request, as I did.


Answer 1, authority 100%

INSERT INTO `vizits` (`date`, `direction_id`, `empl_id`, `establishment_id`, `doctor_id`) SELECT `date`, `direction_id`, `empl_id`, `establishment_id`, `doctor_id` from `vizits` where empl_id=2;
SELECT LAST_INSERT_ID();

It doesn’t seem to return 0


Answer 2

I think autoincrementworks if we insert NULL, otherwise it doesn’t.

Example from How to Get the Unique ID for the Last Inserted Row

INSERT INTO foo (auto,text)
    VALUES(NULL,'text');         # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
    VALUES(LAST_INSERT_ID(),'text');  # use ID in second table

Answer 3

Check if the entry is accurately added when 0 is returned. Perhaps selectreturns an empty result and insertis not executed?

On which column are primary keyand auto_increment?


Answer 4

It appears to be returning the id modified by the last request. The last request is select, so id = 0.
Read more mysql-insert-id.