Hi Everyone, I am trying to get to grips with php prepared statements, I am getting there slowly...

My question is how do I ge the last insert ID from the following.

if ($stmt = $mysqli->prepare("INSERT INTO tbl_name (value1, value2, value3, value4, value5, value6, value7)  
             values (?, ?, ?, ?, ?, ?, ?)")) {

            // Bind paramaters
            $stmt->bind_param('sssssss', $var1, $var2, $var3, $var4, $var5, $var6, $var7);

            //form values
            $var1           = "$var1";      //1
            $var2           = "$var2";      //2
            $var3           = "$var3";      //3
            $var4           = "$var4";      //4
            $var5           = "$var5";      //5 
            $var6           = "$var6";      //6
            $var7           = "$var7";      //7 
            // # Execute the prepared Statement
            $stmt->execute();
            // # get the last insert id 
            $newId = $mysqli->lastInsertId();           

Each time I try this, either with $stmt or $mysqli I get the following error
"Call to undefined method mysqli::lastInsertId()"

Member Avatar

You say you're doing PDO, but you present code for mysqli. Confused.

I think that is the problem as I not 100% sure what is the best route to go down...

I Have tried changing mysqli to pdo and I start getting different error messages...

Member Avatar

Although comparing mysqli and PDO is more like oranges and apples rather than strains of apple, for most basic uses there is little difference. I tend to prefer PDO for most uses as I don't get "mysqlnd" (mysql native driver) issues, that is, some remote servers seem not to have mysqlnd installed, so some mysqli functions cannot be used and you have to find dirty workarounds. I haven't come across those issues with PDO (yet!).

PDO-ified:

//PDO object as $db

$stmt = $db->prepare("INSERT INTO tbl_name (value1, value2, value3, value4, value5, value6, value7) VALUES (?, ?, ?, ?, ?, ?, ?)");
$paramArray = array($var1, $var2, $var3, $var4, $var5, $var6, $var7);
$stmt->execute($paramArray);
$newId = $db->lastInsertId();

That's the simplest way I can think of - but you'll notice no conditionals / error-checking

Hi @Diafol, mate, once again, thanks very much...

I have just found the following link, it meant me re-writing my code, but I feel I understand it a bit more, thanks very much.

Member Avatar

Da nada. I know this is solved, but I think this link may be useful too:

http://www.daniweb.com/web-development/php/threads/479673/mysqli-versus-pdo

It also contains the link to Pritaeas' tutorial on using PDO here on Daniweb - also worth a look.

I really, really like PDO, but why the hell did the connection parameters have to be so bloody complicated? I have to look up a reference every time :(