Re: PDO lastInsertID always returns zero for MySQL tables [SOLVED]

Disregard. After more head scratching I found that I was branching to the 'insert' handler without calling this->db->query($query). On Aug 5, 2007, at 9:18 PM, Ken Tozier wrote:

Hi In the past, I always used mysql_insert_id() and never had a problem, but now that I'm using PDO, I'm finding that it always returns zero. The tables are identical between the two methods but where mysql_insert_id works $pdo_connection->lastInsertID() doesn't. The PDO documentation states that you need to feed lastInsertID something called a "sequence object." I'd never heard of those before so after some reading here http://www.mckoi.com/database/SQLSyntax.html#7 it looks like you have to create these yourself . But MySQL tables already seem to have a built-in sequence (as evidenced by the auto_increment flag when creating tables) and creating another one for a given table could lead to id collisions. So basically, the question is: Is it possible to get valid "lastInsertIds: using PDO with a MySQL database that mirrors the behavior of mysql_insert_id? Thanks for any help Ken Here' the function. The lastInsertID call comes in the 'else' branch of the "if (strpos($query, 'insert') === false)" test function query_database($inQuery) { $query = $inQuery; $coersions = null; $object_key = null; $group_by_key = null; if (is_array($inQuery)) { $query = $inQuery['query']; $coersions = $inQuery['coersions']; $object_key = $inQuery['object_key']; $group_by_key = $inQuery['group_by_key']; } try { // determine query type if (strpos($query, 'insert') === false) { $rows = array(); $rowCounter = 0; foreach ($this->db->query($query, PDO::FETCH_NAMED) as $row) { $rowFields = array(); $recordKey = $rowCounter; foreach ($row as $key => $value) { // remember this key if it matches the user specified object key if (($object_key != null) && ($key == $object_key)) $recordKey = $value; // perform user specified coersions if ($coersions != null) $value = $this->coerce_value($value, $coersions[$key]); $rowFields[$key] = $value; } // perform grouping if requested if ($group_by_key == null) $rows[$recordKey] = $rowFields; else { $groupKey = $rowFields[$group_by_key]; if ($rows[$groupKey] == null) $rows[$groupKey] = array(); $rows[$groupKey][] = $rowFields; } $rowCounter++; } return $rows; } else { // next line prints OK on inserts echo 'query type was: insert<br>'; // but this always returns zero return ($this->db->lastInsertId()); } } catch (PDOException $error) { print "Error!: " . $error->getMessage() . "<br/>"; die(); } }