5

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

EricP
  • 1,459
  • 6
  • 33
  • 55

4 Answers4

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

This worked.

EricP
  • 1,459
  • 6
  • 33
  • 55
3

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;
EricP
  • 1,459
  • 6
  • 33
  • 55
2

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();
Krishna Srikanth
  • 21
  • 1
0

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';
Gabriel Solomon
  • 29,065
  • 15
  • 57
  • 79
  • Thanks solomongaby. It's giving me this error: Fatal error: Call to undefined method Zend_Db_Statement_Pdo::lastInsertId() The insert works fine. and it gives me the rowsAdded fine also. Strange! – EricP Jan 19 '10 at 14:51
  • I got it! $lastId=$contactsTable->getAdapter()->lastInsertId(); I still don't know why it didn't work the other way. – EricP Jan 19 '10 at 15:02