How to use PDO to insert data into the database?

By | February 4, 2019

The SQL code for the users table:

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

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

$sql = "INSERT INTO `users`(`name`, `phone`, `city`, `date_added`)
VALUES(:name,:phone,:city,:date)";

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:

$query = $dbh -> prepare($sql);

3) Bind the placeholders to the variables:

$query->bindParam(':name',$name);

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

$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);
  • 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.

$name = "Amardeep Dubey";
$phone = "1234567890";
$city = "Bokaro";
$date = date('Y-m-d');

5) Execute the query:

$query -> execute();

6) Check that the insertion really worked:

$lastInsertId = $dbh->lastInsertId();
if($lastInsertId>0)
{
echo "OK";
}
else
{
echo "not OK";
}

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

$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"; }

Leave a Reply