CODEWITHSUNDEEP
zend-frameworkinsert-id
EricP
EricP

Reputation: 1449

zend framework get last insert id of multi row insert using execute

How would I get the last inserted ID using a multirow insert? Here is my code:

$sql='INSERT INTO t (col1, col2, col3) VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)'; // example
$stmt = $contactsTable->getAdapter()->prepare($sql);
$stmt->execute(); 
$rowsAdded=$stmt->rowCount(); // mysql_affected_rows
$lastId=$stmt->lastInsertId();
echo '<br>Last ID: '.$lastId;

Also, is there a method in ZF to get the next insert id before an insert?

thanks

Upvotes: 5

Views: 15822

Answers (4)

Krishna Srikanth
Krishna Srikanth

Reputation: 21

An alternate solution. Move off sql code from controllers and place them in models. That is what they are for.

If you are using models, you can given the name of table which has auto increment column, in class variable.

protected $_name="<table_name>";

Then in your model class method, you can get last insert id from 

$insertID= $this->getAdapter()->lastInsertId();

Upvotes: 2

EricP
EricP

Reputation: 1449

$lastId=$contactsTable->getAdapter()->lastInsertId();

This worked.

Upvotes: 10

EricP
EricP

Reputation: 1449

So, here is the full working code I'm using to create a multi-row insert, getting the rows added and the last inserted id:

$contactsTable = new Contacts_Model_Contacts();
$sql='INSERT INTO t (col1, col2, col3) VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)'; // example
$stmt = $contactsTable->getAdapter()->prepare($sql);
$stmt->execute(); 
$rowsAdded=$stmt->rowCount(); // mysql_affected_rows
$lastId=$contactsTable->getAdapter()->lastInsertId(); // last inserted id
echo '<br>Last ID: '.$lastId;

Upvotes: 3

Gabriel Solomon
Gabriel Solomon

Reputation: 30085

that code should work, but it will only get you the id of your last insert.

you can get the next autoincrement with this mysql query:

SELECT Auto_increment FROM information_schema.tables WHERE TABLE_SCHEMA = 'your_db_name' AND TABLE_NAME='the_table_you_want';

Upvotes: 0

Related Questions