In the last lesson we learnt Traits . In this lesson we will learn mysql database connection using PDO and we will perform some basic sql queries like (insert, select, update, delete). You might be thinking at this time that if mysql, mysqli are already available then why to learn this PDO ? Since you also know for everything there is its expiry date, mysql , mysqli are crossing its expiry date and has been declared deprecated , will be completely removed from upcoming versions.
These technological developments are carried out to remove the bugs ,enhancing the software performance w.r.t the earlier version of the software.
There are some benefits of using the latest PDO extensions.
- PDO is object oriented from its core.
- It is compatible with other databases like postgresSql, Oracle, SQLite e.t.c.
- From an attacker point of view, it is harder to break which means improved security.
How to connect mysql database using PDO
In order to connect to database we will be using credentials like, DB_HOSTNAME, DB_USERNAME, DB_PASSWORD and DB_NAME .
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<?php //Database credentials define("DB_HOSTNAME", "Name of the host like : localhost"); define("DB_NAME", "Name of the Database"); define("DB_USERNAME", "User name"); define("DB_PASSWORD", "Database Password"); // Database connection try { $dbc = new PDO("mysql:host=".DB_HOSTNAME.";dbname=".DB_NAME, DB_USERNAME, DB_PASSWORD, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'")); } catch (PDOException $e) { exit("Error occured while database connection: " . $e->getMessage()); } ?> |
It is best practice to use try-catch block where there are chances of exception occurrence.
Lets create a database named “pdophp” in your phpmyadmin and test your database connection.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<?php define("host","localhost"); define("username","root"); define("password",""); define("database","pdophp"); try { $dbc = new PDO("mysql:host=".host.";dbname=".database, username, NULL, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'")); } catch (PDOException $e) { exit("Error: " . $e->getMessage()); } ?> |
So let’s starts with our first sql query i.e insert query.
Insert data into database using PDO
So first create a table in the pdophp database using the below query.
|
1 2 3 4 5 6 7 8 9 |
// Creating tablename users. CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `phone` bigint(10) NOT NULL, `city` varchar(19) NOT NULL, `date_added` varchar(20) NOT NULL, PRIMARY KEY (id) ) |
I am writing both the PDO and procedural query to understand this easily.
Insert query (PDO) :
$sql = “insert into users (name,phone,citydate_added,) values (:name,:phone,:city,:date)";
Insert query (Procedural mysqli)
$sql = "insert into users (name,phone,city,date_added) values (‘$name’, ‘$phone’, ‘$city’, ‘$date’)”;
If you compare both the queries you will find that in PDO QUERY we used :name,:phone,:city,:date as a template ,
While in PROCEDURAL QUERY we use direct values ‘$name’,’$phone’,’$city’,’$date’.Let’s see complete insertion code using PDO.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
<?php // database connection file. include "db.php"; $sql = "INSERT INTO `users` (`name`, `phone`, `city`, `date_added`) VALUES (:name,:phone,:city,:date)"; // preparing $sql statement as template $query = $dbc-> prepare ($sql); // binding the template value with the variable name. $query-> bindParam(':name',$name,PDO::PARAM_STR); $query-> bindParam(':phone',$phone,PDO::PARAM_STR); $query-> bindParam(':city',$city,PDO::PARAM_STR); $query-> bindParam(':date',$date,PDO::PARAM_STR); // assigning value to the variable. $name = "Rahul"; $phone = 987654321; $city = "Delhi"; $date = date("m-d-Y"); // executing query $query -> execute(); // getting the last inserted id of the row, after successful insertion of data $lastInsertId = $dbc->lastInsertId(); // Checking data inserted or not. if($lastInsertId > 1) { echo "Last inserted id : ".$lastInsertId ; }else { echo "Data not inserted"; } ?> |
In the above insert program you have noticed that PDO::PARAM_STR . This third parameter in bindParam is used to filter the data before sending to database. There are many others filtration parameter like PDO::PARAM_STR as.
• PDO::PARAM_INT ( for integer).
• PDO::PARAM_STR (for string).
• PDO::PARAM_NULL allows only NULL datatype.
• PDO::PARAM_BOOL allows only boolean (true/false) values.
Retrieve data from database (Select query)
Fetching data from database is similar to inserting data into database. Have look at the below program you will understand how easy it is.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
<?php include "db.php"; $sql = "select * from users where name = :name"; $query = $dbc -> prepare($sql); $query->bindParam(':name', $name, PDO::PARAM_STR); $name="Rahul"; $query -> execute(); $results = $query -> fetchAll(PDO::FETCH_OBJ); if ($query -> rowCount() > 0) { foreach($results as $result){ echo $result->name.","; echo $result->phone.","; echo $result->city.","; echo $result->date_added."<br>"; } } ?> |
In the above select example you can understand easily till the step $query -> execute(); This is same illustrated in insert query.
$results = $query -> fetchAll(PDO::FETCH_OBJ);
PDO::FETCH_OBJ is used to fetch data as object. If you want to fetch data in array you can use PDO::FETCH_ASSOC.
Update data in database using PDO
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<?php include "db.php"; $sql = "update `users` SET city=:city where name=:name "; $query = $dbc -> prepare($sql); $query->bindParam('city',$city, PDO::PARAM_STR); $query->bindParam('name',$name, PDO::PARAM_STR); $name="Rahul"; $city="Punjab"; $query -> execute(); ?> |
Delete data from database using PDO
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<?php include "db.php"; $sql = "delete from `users` where name=:name "; $query = $dbc -> prepare($sql); $query->bindParam('name',$name, PDO::PARAM_STR); $name="Rahul"; $query -> execute(); if($query->rowCount()>0){ $count = $query -> rowCount(); echo $count ." rows deleted "; } ?> |
When our query gets executed then you need to close the database connection. Since in PHP the database gets close automatically but if you need to close the database connection at any point during code you may use .
$dbc = null;