Insert new data using PDO

Inserting data into database is the most common operation. Using PDO this is just two step process. in this article lets see how to inset data using PDO 

Bellow is very simple insert code.. lets look at it..

//STH means Statement Handler 
$STH = $DBH->prepare("INSERT INTO my_table VALUES('my php pages')");
$STH->execute(); 

This operation can be done using exec() function also. but it is good to use prepare method because it helps to protect you from sql injections attack. for this protection we have use placeholders. it automatically making data used in the placeholders safe from sql injection attacks.

We can divided Prepare placeholders into three types

1. No placeholders
2. Unnamed placeholders
3. Named placeholders

No placeholders

//STH means Statement Handler 
$STH = $DBH->prepare("INSERT INTO my_table(name,id) VALUES('my php pages',123)");
$STH->execute(); 

Unnamed placeholders

//STH means Statement Handler 
$STH = $DBH->prepare("INSERT INTO my_table(name,id) VALUES(?,?)");
$STH->execute(); 

Named placeholders

//STH means Statement Handler 
$STH = $DBH->prepare("INSERT INTO my_table(name,id) VALUES(:name,:id)");
$STH->execute(); 

Setting data values to Unnamed and Named Placeholders

Lets see how to set data values to Unnamed Placeholder. there is two methods we can use, one is assign values one buy one to variables or assign values to array.

Unnamed Placeholders

Assign values to each placeholder using variable

//Add variable to each placeholder
$SDH->bindParam(1,$name);
$SDH->bindParam(2,$id);

//insert new row
$name = 'my php pages PAGE 1';
$id = 1;
$SDH->execute();

//insert another row with different values
$name = 'my php pages PAGE 2';
$id = 2;
$SDH->execute();

Use an array to set data to placeholder

$data = array('my php pages PAGE 1',1);

$STH = $DBH->prepare("INSERT INTO my_table(name,id) VALUES(?,?)");
$STH->execute($data);

Named Placeholders

Assign values to each placeholder using variable

//Add variable to each placeholder
$SDH->bindParam(':name',$name);
$SDH->bindParam(':id',$id);

//insert new row
$name = 'my php pages PAGE 1';
$id = 1;
$SDH->execute();

//insert another row with different values
$name = 'my php pages PAGE 2';
$id = 2;
$SDH->execute();

Use an array to set data to placeholder

$data = array('name' => 'my php pages PAGE 1', 'id' = > 1);

$STH = $DBH->prepare("INSERT INTO my_table(name,id) VALUES(:name,:id)");
$STH->execute($data);