How to use PDO to insert data into the database?

The SQL code for the users table:

[code language=”sql”]
CREATE TABLE IF NOT EXISTS users (id int(11) NOT NULL AUTO_INCREMENT,
name varchar(60) DEFAULT NULL,
phone varchar(12) DEFAULT NULL,
city varchar(60) DEFAULT NULL,
date_added date DEFAULT NULL,
PRIMARY KEY (id)
)
[/code]

1) Write a regular SQL query but, instead of values, put named placeholders. For example:

[code language=”php”]
$sql = "INSERT INTO users(name, phone, city, date_added)
VALUES(:name,:phone,:city,:date)";
[/code]

The use of placeholders is known as prepared statements. We use prepared statements as templates that we can fill later on with actual values.
2) Prepare the query:

[code language=”php”]
$query = $dbh -> prepare($sql);
[/code]

3) Bind the placeholders to the variables:

[code language=”php”]
$query->bindParam(‘:name’,$name);
[/code]

You can add a third parameter which filters the data before it reaches the database:

[code language=”php”]
$query->bindParam(‘:name’,$name,PDO::PARAM_STR);
$query->bindParam(‘:phone’,$phone,PDO::PARAM_INT);
$query->bindParam(‘:city’,$city,PDO::PARAM_STR);
$query->bindParam(‘:date’,$date,PDO::PARAM_STR);
[/code]

  • PDO::PARAM_STR is used for strings.
  • PDO::PARAM_INT is used for integers.
  • PDO::PARAM_BOOL allows only boolean (true/false) values.
  • PDO::PARAM_NULL allows only NULL datatype.

4) Assign the values to the variables.

[code language=”php”]
$name = "Amardeep Dubey";
$phone = "1234567890";
$city = "Bokaro";
$date = date(‘Y-m-d’);
[/code]

5) Execute the query:

[code language=”php”]
$query -> execute();
[/code]

6) Check that the insertion really worked:

[code language=”php”]
$lastInsertId = $dbh->lastInsertId();
if($lastInsertId>0)
{
echo "OK";
}
else
{
echo "not OK";
}
[/code]

If the last inserted id is greater than zero, the insertion worked.
All code together now:

[code language=”php”]
$sql = "INSERT INTO users
(name, phone, city, date_added)
VALUES
(:name,:phone,:city,:date)";
$query = $dbh -> prepare($sql);
$query->bindParam(‘:name’,$name,PDO::PARAM_STR);
$query->bindParam(‘:phone’,$phone,PDO::PARAM_INT);
$query->bindParam(‘:city’,$city,PDO::PARAM_STR);
$query->bindParam(‘:date’,$date);
// Insert the first row
$name = "Amardeep Dubey";
$phone = "1234567890";
$city = "Bokaro";
$date = date(‘Y-m-d’);
$query -> execute();
$lastInsertId = $dbh->lastInsertId();
if($lastInsertId>0)
{
echo "OK";
}
else {
echo "not OK"; }
[/code]