PDO lastInsertID always returns zero for MySQL tables

[Ken Tozier <kentozier@xxxxxxxxxxx>]

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();
	}
}

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php