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]

Rajesh Kumar

I’m a DevOps/SRE/DevSecOps/Cloud Expert passionate about sharing knowledge and experiences. I have worked at Cotocus. I share tech blog at DevOps School, travel stories at Holiday Landmark, stock market tips at Stocks Mantra, health and fitness guidance at My Medic Plus, product reviews at TrueReviewNow , and SEO strategies at Wizbrand.

Do you want to learn Quantum Computing?

Please find my social handles as below;

Rajesh Kumar Personal Website
Rajesh Kumar at YOUTUBE
Rajesh Kumar at INSTAGRAM
Rajesh Kumar at X
Rajesh Kumar at FACEBOOK
Rajesh Kumar at LINKEDIN
Rajesh Kumar at WIZBRAND

Rajesh Kumar DailyLogs