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


Add comment

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

Follow us

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