PHPGurukul

How to use PDO to insert data into the database?

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 = "Anuj kumar";
$phone = "999857868";
$city = "New Delhi";
$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 = "Anuj";
$phone = "1231234567";
$city = "New Delhi";
$date = date('Y-m-d');
$query -> execute();
$lastInsertId = $dbh->lastInsertId();
if($lastInsertId>0)
{
echo "OK";
 }
else {
echo "not OK"; }

For localhost Database name is : pdoanuj Sql file available inside the package.

View Demo

Anuj Kumar

This is Anuj Kumar. I’m a professional web developer with 4+ year experience. I write blogs in my free time. I love to learn new technologies and share with others.
I founded PHPGurukul in September 2015. The main aim of this website to provide PHP, Jquery, MySQL, PHP Oops and other web development tutorials.
.

Recommended Tutorials for you


2 comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Web Hosting with a FREE domain. On sale ₹99.00/mo

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.