Arash Mousavi Arash Mousavi - 6 months ago 97
PHP Question

pdo lastInsertId returns zero(0)

All queries execute successfully, when I check table in MySQL row inserted successfully without any error, but

lastInsertId()
returns 0. why?

My code:

// queries executes successfully, but lastInsetId() returns 0
// the menus table has `id` column with primary auto_increment index
// why lastInsertId return 0 and doesn't return actual id?


$insertMenuQuery = "
SELECT @rght:=`rght`+2,@lft:=`rght`+1 FROM `menus` ORDER BY `rght` DESC limit 1;
INSERT INTO `menus`(`parent_id`, `title`, `options`, `lang`, `lft`, `rght`)
values
(:parent_id, :title, :options, :lang, @lft, @rght);";
try {
// menu sql query
$dbSmt = $db->prepare($insertMenuQuery);

// execute sql query
$dbSmt->execute($arrayOfParameterOfMenu);
// menu id
$menuId = $db->lastInsertId();

// return
return $menuId;

} catch (Exception $e) {
throw new ForbiddenException('Database error.' . $e->getMessage());
}

Answer

With PDO_MySQL we must use

$DB->setAttribute(PDO::ATTR_EMULATE_PREPARES,TRUE); // there are other ways to set attributes. this is one

so that we can run multiple queries like:

$foo = $DB->prepare("SELECT * FROM var_lst;INSERT INTO var_lst (value) VALUES ('durjdn')");

but sadly, doing so relieves the $DB from returning the correct insert id. You would have to run them separately to be able to retrieve the insert id. This returns the correct insert id:

$DB->setAttribute(PDO::ATTR_EMULATE_PREPARES,TRUE);
$foo = $DB->prepare("INSERT INTO var_lst (value) VALUES ('durjdn')");
$foo->execute();
echo $DB->lastInsertId();

but this won't:

$DB->setAttribute(PDO::ATTR_EMULATE_PREPARES,TRUE);
$foo = $DB->prepare("SELECT * FROM var_lst;INSERT INTO var_lst (value) VALUES ('durjdn')");
$foo->execute();
echo $DB->lastInsertId();

and this won't even run the two queries:

$DB->setAttribute(PDO::ATTR_EMULATE_PREPARES,FALSE); // When false, prepare() returns an error
$foo = $DB->prepare("SELECT * FROM var_lst;INSERT INTO var_lst (value) VALUES ('durjdn')");
$foo->execute();
echo $DB->lastInsertId();
Comments