PDO: Insert example.

This is a beginners tutorial on how to insert rows into a MySQL database using PHP’s PDO object. Note that I have also written a tutorial on how to do a multi-insert with PDO. For this tutorial, however, I will be sticking to the basics.

Example MySQL table:

CREATE TABLE IF NOT EXISTS `cars` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `make` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `model` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

For the purpose of this example, I have created a simple table called cars. Be sure to import the SQL above into your own database if you plan on playing around with the PDO extension.

PDO Insert example.

Firstly, we need to connect to MySQL with the PDO object.

//MySQL connection details.
$host = 'localhost';
$user = 'root';
$pass = '';
$database = 'test';

//Custom PDO options.
$options = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
);

//Connect to MySQL and instantiate our PDO object.
$pdo = new PDO("mysql:host=$host;dbname=$database", $user, $pass, $options);

Once we have connected to MySQL and selected our database, we can execute a prepared insert statement:

//Create our INSERT SQL query.
$sql = "INSERT INTO `cars` (`make`, `model`) VALUES (:make, :model)";


//Prepare our statement.
$statement = $pdo->prepare($sql);


//Bind our values to our parameters (we called them :make and :model).
$statement->bindValue(':make', 'Nissan');
$statement->bindValue(':model', 'Primera');


//Execute the statement and insert our values.
$inserted = $statement->execute();


//Because PDOStatement::execute returns a TRUE or FALSE value,
//we can easily check to see if our insert was successful.
if($inserted){
    echo 'Row inserted!<br>';
}

The process of inserting a row into MySQL with the PDO object is actually pretty simple:

  1. We created our INSERT SQL statement. Note how we created “placeholders” such as :make and :model. Later on, these placeholders will allow us to “bind” our data to the statement. Remember that in a prepared statement, there are two rounds of communication with the MySQL server. First, we tell the MySQL about the structure of our statement by “preparing it”. Then, we send the data through by “binding” our data and executing the statement.
  2. We prepared our statement.
  3. We binded the data that we want to insert into our statement.
  4. After that, we executed the statement (thereby executing the SQL query).
  5. Finally, we checked to see if the result of PDOStatement::execute was a boolean TRUE value. If it is TRUE, then it means that the new row was inserted successfully. If it FALSE, then it means that MySQL could not insert the row. In cases like this, you will most likely encounter a PDOException error.

Inserting multiple rows with PDO.

Note that you can re-use the same statement to insert multiple rows:

//Create our SQL query.
$sql = "INSERT INTO `cars` (`make`, `model`) VALUES (:make, :model)";


//Prepare our statement using the SQL query.
$statement = $pdo->prepare($sql);


//Bind our values to our parameters (we called them :make and :model).
$statement->bindValue(':make', 'Nissan');
$statement->bindValue(':model', 'Primera');


//Execute the statement and insert our values.
$inserted = $statement->execute();

//Because PDOStatement::execute returns a TRUE or FALSE value,
//we can easily check to see if our insert was successful.
if($inserted){
    echo 'Row inserted!<br>';
}

//For our next row, we're going to insert a Honda Civic.
//We will re-use the statement that we already prepared.
$statement->bindValue(':make', 'Honda');
$statement->bindValue(':model', 'Civic');


//Execute the statement and insert our values.
$statement->execute();

Note how we executed the same statement twice, without having to prepare a new one for each insert.

Shorthand / Quick insert with PDO.

There is also a shorthand version.

//Create our SQL query.
$sql = "INSERT INTO `cars` (`make`, `model`) VALUES (?, ?)";

//Prepare the statement.
$statement = $pdo->prepare($sql);

//Execute it
$statement->execute(array('Honda', 'Civic'));

As you can see, this method is a lot quicker.

Related reading for PDO beginners: