INSERT query using PDO
- INSERT query with positional placeholders
- INSERT query with named placeholders
- INSERTing multiple rows
- Comments (30)
First of all make sure you've got a properly configured PDO connection variable that needs in order to run SQL queries with PDO and to inform you of the possible errors.
In order to run an INSERT query with PDO just follow the steps below:
- create a correct SQL INSERT statement
- replace all actual values with placeholders
- prepare the resulting query
- execute the statement, sending all the actual values in the form of array.
INSERT query with positional placeholders
As usual, positional placeholders are more concise and easier to use
$sql = "INSERT INTO users (name, surname, sex) VALUES (?,?,?)";
$stmt= $pdo->prepare($sql);
$stmt->execute([$name, $surname, $sex]);or you can chain execute() to prepare():
$sql = "INSERT INTO users (name, surname, sex) VALUES (?,?,?)";
$pdo->prepare($sql)->execute([$name, $surname, $sex]);Important! You don't have to check the result of
execute()(as it is often shown in low-quality tutorials). Such a condition will make no sense, as in case of error, a PDOException will be thrown and the script execution will be terminated, which means such a condition will never reach theelsepart.
Neither atry ... catchoperator should be used, unless you have a specific scenario to handle the error, such as a transaction rollback shown below. Please see the article about error reporting for the details.
INSERT query with named placeholders
In case you have a predefined array with values, or prefer named placeholders in general, the code would be
$data = [
'name' => $name,
'surname' => $surname,
'sex' => $sex,
];
$sql = "INSERT INTO users (name, surname, sex) VALUES (:name, :surname, :sex)";
$stmt= $pdo->prepare($sql);
$stmt->execute($data);or you can chain execute() to prepare():
$sql = "INSERT INTO users (name, surname, sex) VALUES (:name, :surname, :sex)";
$pdo->prepare($sql)->execute($data);Important! The only characters allowed in the placeholder names are Latin letters, numbers and underscores. No umlauts or dashes ar any other characters are allowed. It's also a good idea to keep all the letters lowercase.
INSERTing multiple rows
As it's explained in the main article, a once prepared statement could be executed multiple times, slightly reducing the overhead on the query parsing. So it makes sense to use this feature when we need to insert multiple rows into the same table. Just a couple notes before we begin:
- make sure that the emulation mode is turned off, as there will be no speed benefit otherwise, however small it is.
- it's a good idea to wrap our queries in a transaction. In some circumstances it will greatly speed up the inserts, and it makes sense overall, to make sure that either all data has been added or none.
So in the end our code would be like
$data = [
['John','Doe', 22],
['Jane','Roe', 19],
];
$stmt = $pdo->prepare("INSERT INTO users (name, surname, age) VALUES (?,?,?)");
try {
$pdo->beginTransaction();
foreach ($data as $row)
{
$stmt->execute($row);
}
$pdo->commit();
}catch (Exception $e){
$pdo->rollback();
throw $e;
}Related articles:
- SELECT query with PDO
- UPDATE query using PDO
- How to connect to MySQL using PDO
- PDO Examples
- Authenticating a user using PDO and password_verify()
- How to check if email exists in the database?
- Select the number of rows using PDO
- How to create a WHERE clause for PDO dynamically
- DELETE query using PDO
- How to create a prepared statement for UPDATE query
- Getting a nested array when multiple rows are linked to a single entry
- How to execute 1000s INSERT/UPDATE queries with PDO?
- Adding a field name in the ORDER BY clause based on the user's choice
- INSERT helper function for PDO Mysql
- PDO Examples
- PDO Examples
Got a question?
I am the only person to hold a gold badge in
,
and
on Stack Overflow and I am eager to show the right way for PHP developers.
Besides, your questions let me make my articles even better, so you are more than welcome to ask any question you have.
Add a comment
Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator's review.
Markdown is now supported:
>before and an empty line after for a quote