How to use PDO to insert data into the database?
The SQL code for the users table:
|
1 2 3 4 5 6 7 |
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:
|
1 2 |
$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:
|
1 |
$query = $dbh -> prepare($sql); |
3) Bind the placeholders to the variables:
|
1 |
$query->bindParam(':name',$name); |
You can add a third parameter which filters the data before it reaches the database:
|
1 2 3 4 |
$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.
|
1 2 3 4 |
$name = "Anuj kumar"; $phone = "999857868"; $city = "New Delhi"; $date = date('Y-m-d'); |
5) Execute the query:
|
1 |
$query -> execute(); |
6) Check that the insertion really worked:
|
1 2 3 4 5 6 7 8 9 |
$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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
$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 the localhost Database name is: pdo. SQL file available inside the package.
How insert / fetch data using PDO into DB (Full source Code)
Size: 28 KB
Version: V 1.0
