PDO Tutorial – 03: Insert data into a database with PDO

After the connection to database is successfully created and the PDO object instance is set, the object can be used to perform SQL queries.

There are two ways in which you can make SQL queries with PDO:
– directly using “exec()“, and “query()” methods,
– or with the “prepare()” … “execute()” statement.

Exec() and query()

The queries that modify rows in the table, without returning a result set with rows and columns (INSERTUPDATE, and DELETE), are sent with exec(). This method returns the number of affected rows, or FALSE on error.

<?php
$sth = $db->exec("SQL Query");
?>

The queries that select rows (SELECT) and return a result set with rows and columns are sent with the query() method. In case of error, returns FALSE.

<?php
$sth = $db->query("SQL Query");
?>

How do I INSERT data into table with PDO

In its simplest form, the way to insert data into a table is as follows:

<?php
$db->exec("INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)");
?>

But you could have done that just as well without PDO, and just using the standard MySQL extension.

As I mentioned in the first lesson, the best thing about PDO is that you won’t have to deal with the sanitizing of the queries.

So, if you want to enjoy the main benefit of PDO, security, you should always use prepared statements, because the values you introduce is always sanitized. And that is only one advantage.

The other advantage is speed (about which I will talk in the next title: “Execute prepared statements in a loop”).

<?php
$sth = $db->prepare("INSERT INTO table(column1,column2,column3,column4,column5) VALUES(:field1,:field2,:field3,:field4,:field5)");
$sth->execute(array(':field1' => $field1, ':field2' => $field2, ':field3' => $field3, ':field4' => $field4, ':field5' => $field5));
$affected_rows = $sth->rowCount();
?>

What you saw earlier was using named placeholders, i.e. naming each parameter with placeholders. In this example, the placeholders are named with “:field1″,”:field2″ etc.

If you don’t want to use named placeholders, you can use positional placeholders, like this:

<?php
$sth = $db->prepare("INSERT INTO table(column1,column2,column3,column4,column5) VALUES(?,?,?,?,?)");
$sth->execute(array($field1, $field2, $field3, $field4, $field5));
$affected_rows = $sth->rowCount();
?>

As you see in the query above, we use question marks to designate the position of values in the prepared statement. These question marks are called positional placeholders. We must take care of proper order of the elements in the array that we are passing to the PDOStatement::execute() method.

Another way of doing things is by binding parameters:

<?php
$sth = $db->prepare("INSERT INTO table(column1,column2,column3) VALUES(:field1,:field2,:field3)");
$sth->bindValue(':field1','whatever string i want to insert', PDO::PARAM_STR); //if i want to just insert a string without putting it first into a variable or...
$sth->bindValue(':field2','50', PDO::PARAM_INT); //if i want to just insert a number without putting it first into a variable or...
$field3 = 'just another value';
$sth->bindParam(':field3',$field3,PDO::PARAM_STR) // if I want to insert a variable
$sth->execute();
$affected_rows = $sth->rowCount();
?>

Worth mentioning…

With bindParam(), you can only pass variables, not values. With bindValue(), you can pass both values, obviously, and variables. bindParam works only with variables because it allows parameters to be given as input/output, by “reference” (and a value is not a valid “reference” in PHP) : it is useful with drivers that (quoting the manual): support the invocation of stored procedures that return data as output parameters, and some also as input/output parameters that both send in data and are updated to receive it.

With some DB engines, stored procedures can have parameters that can be used for both input (giving a value from PHP to the procedure) and ouput (returning a value from the stored proc to PHP) ; to bind those parameters, you’ve got to use bindParam, and not bindValue.

(http://stackoverflow.com/questions/1179874/pdo-bindparam-versus-bindvalue)

Execute prepared statements in a loop

Prepared statements excel in being called multiple times in a row with different values. We are talking about speed here. When you use prepared statements, the PDO is first “preparing the statement”, i.e. the sql statement is compiled, and it can be called multiple times in a row having different arguments. That means a faster way to execute statements in PDO vs calling mysql_query over and over again!

Typically this is done binding parameters with bindParam. As I said earlier, BindParam() is much like bindValue() except instead of binding the value of a variable, it binds the variable itself, so that if the variable changes, it will be read at the time of execute.

<?php
$values = array('bob', 'alice', 'lisa', 'john');
$name = '';
$sth = $db->prepare("INSERT INTO table(`name`) VALUES(:name)");
$sth->bindParam(':name', $name, PDO::PARAM_STR);
foreach($values as $name) {
$sth->execute();
}
?>

Get number of returned rows

If you want to get the number of returned rows, you can use rowCount():

<?php
$row_count = $sth->rowCount();
?>

Get last inserted ID

There are some times when you need to find out the ID of the last inserted row. For this you have the lastInsertId() method:

<?php
$insertId = $db->lastInsertId();
?>

Further reading

PHP PDO – exec (INSERT, UPDATE, DELETE) MySQL

 

Leave a Reply

Your email address will not be published. Required fields are marked *

No spam? * Time limit is exhausted. Please reload CAPTCHA.