Database Insert

Having looked at delete and update, we'll now consider the code for inserting to the database.

As with the delete and update examples, we'll look at the object oriented mySQLi version and then at the PDO equivalent.

Here's the mySQLi version:

$stmt = $db->prepare("INSERT INTO mytable (field1,field2,field3,field4,field5) VALUES(?,?,?,?,?)");
$stmt->bind_param('sssss',$field1, $field2, $field3, $field4, $field5);
$stmt->execute();
$affected_rows = $stmt->affected_rows;

and here is the PDO equivalent:

$stmt = $db->prepare("INSERT INTO mytable (field1,field2,field3,field4,field5) VALUES(:field1,:field2,:field3,:field4,:field5)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2, ':field3' => $field3, ':field4' => $field4, ':field5' => $field5));
$affected_rows = $stmt->rowCount();

Again as with the earlier examples, there is little difference between the mySQLi and DO versions. In this example, all of the fields to be inserted are strings and so we can use a shortcut with the PDO version and supply the fields directly to the execute call. The PDO version also looks slightly longer because we have used named placeholders.

One thing that makes inserting slightly more complicated than deleting or updating is that we may need to apply additional code specifically if the insert fails due to a duplicate key error. With all of the calls we have looked at the $affected_rows value tells us whether the call worked or not but it doesn't tell us why it failed.

We can test specifically for a duplicate key error causing an insert to fail using the following if statements.

mySQLi:

if ($stmt->errno == 1062) { ... }

PDO (if PDO::ERRMODE_EXCEPTION not set):

if($stmt->errorCode() == 1062) { ... }

With PDO if PDO::ERRMODE_EXCEPTION is set then you need to wrap your database calls in a try block and test for error codes in the catch block. We'll look at that in the next tutorial.

 

This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow
Donate