INSERT query using PDO

  1. INSERT query with positional placeholders
  2. INSERT query with named placeholders
  3. INSERTing multiple rows
  4. Comments (4)

Running INSERT query with PDO is not different from running any other query:

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]);

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 (?,?,?)";
$pdo->prepare($sql)->execute($data);

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:

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: