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]
- DevOps Certified Professionals (DCP)
- Site Reliability Engineering Certified Professionals (SRECP)
- Master in DevOps Engineering (MDE)
- DevSecOps Certified Professionals (DSOCP)
URL - https://www.devopsschool.com/certification/
My Linkedin - https://www.linkedin.com/in/rajeshkumarin
- Best AI tools for Software Engineers - November 4, 2024
- Installing Jupyter: Get up and running on your computer - November 2, 2024
- An Introduction of SymOps by SymOps.com - October 30, 2024
