CODEWITHSUNDEEP
phpmysql
unknownbits
unknownbits

Reputation: 2885

mysql_insert_id() in php , not return last insert id?

This is my Mysql Query. In my table Id is Auto-Increment type.

$sql ="INSERT INTO product_detail (master_detail_id,Product_code,Product_label) 
values
('8330','TOTAL','products'),('8330','010290','demo'),('8330','010639','Live demo'),
('8330','020900','demo fat'),('8330','030344','demos')";

 $insertDetails = mysql_query( $sql);
echo "last insert id-->".mysql_insert_id();

I am getting last insert id--->1 but I should get it 5. I don't know why it is happening?

table structure

     id            bigint(250)  No  None    AUTO_INCREMENT  
Product_code       varchar(20)  latin1_swedish_ci       No  
Product_label      varchar(500) latin1_swedish_ci       No  
master_detail_id   bigint(250)          No  None

Upvotes: 1

Views: 1815

Answers (5)

Starx
Starx

Reputation: 78971

Since you are TRUNCATING your table, the auto-increment value is reset to 1. So whenever you run the query and ask for the last inserted id it will return 1.

Upvotes: 0

Mostafa Berg
Mostafa Berg

Reputation: 3239

it will only return the ID of the first item inserted, also mysql_insert_id() is deprecated as of 5.5.0 as per docs.

consider using mysqli_insert_id() instead

To workaround this behaviour you can try using the LAST_INSERT_ID() Mysql Function

Quoting the Doc:

Performing an INSERT or UPDATE statement using the LAST_INSERT_ID() function will also modify the value returned by the mysqli_insert_id() function.

A Simple way to use that would be to select the last id after inserting your data probably this is wrong, if someone can point out the correct way?

$sql ="INSERT INTO product_detail (master_detail_id,Product_code,Product_label) 
values
('8330','TOTAL','products'),('8330','010290','demo'),('8330','010639','Live demo'),
('8330','020900','demo fat'),('8330','030344','demos') select LAST_INSERT_ID() from `product_detail`";

Important

Note that if last insert fails, LAST_INSERT_ID() will be undefined, so you'll need to add a check !

Upvotes: 0

blackout2063
blackout2063

Reputation: 13

For multi inserts mysql_insert_id() returns the first one, you would either need to do 5 seperate queries, running mysql_insert_id() at the end, or do 1 additional query of 

SELECT MAX(master_detail_id) FROM product_detail or 
SELECT master_detail_id FROM product_detail 
ORDER BY master_detail_id DESC LIMIT 1

Upvotes: 1

shankhan
shankhan

Reputation: 6571

In the case of a multiple-row INSERT statement, mysql_insert_id() returns the first automatically generated AUTO_INCREMENT value; if no such value is generated, it returns the last explicit value inserted into the AUTO_INCREMENT column.

Upvotes: 4

kjurkovic
kjurkovic

Reputation: 4104

mysql_insert_id() will return id if you specified id column as AUTO_INCREMENT. You should be aware that if your id column is BIGINT type result which function returns might be wrong (this is also specified in php docs: http://php.net/manual/en/function.mysql-insert-id.php)

Upvotes: 2

Related Questions