To connect to MySQL databases in PHP, the age old mysql_connect API is no longer used. It has been deprecated and will not be supported in future PHP versions. So, there are recommendations everywhere for us to use either PDO (PHP Data Object) or mysqli_connect.
Using PDO (PHP Data Objects)
PDO supports multiple database types. You can connect to different databases like MySQL and SQLite based on the database driver available in your system. Switching between database types is also easy. Security is also high.
Checking for the Available Drivers
You can check for the supported databases by finding out the drivers that are installed. Just run the following code:
<?php print_r(PDO::getAvailableDrivers()); ?>
Based on the drivers you have, you will get an output like:
Array ( [0] => mysql [1] => sqlite )
For Connecting to the Database
<?php
try{
$connection = new PDO('mysql:host=localhost;dbname=myDatabase', 'username', 'password');
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
echo 'Error in database connection:<br />';
echo $e->getMessage();
die();
}
?>
Querying the database
To query the database using PDO, we need to add the query line after the connection has been made. Here’s how a simple database query looks like
<?php
$query = $connection->query('Select * FROM table');
?>
Databases in PDO are usually queried by using the prepare statement followed by execute. The data can be user supplied and is safe from SQL injection when queries are prepared as opposed to when it is directly executed.
Example:
<?php
$id = 1; //Creating parameters
$query = $connection->prepare('Select * FROM table WHERE id = :id'); //Preparing the query
$query->execute(array('id' => $id)); //Binding the parameters
?>
Using the Output of the Query
After the query has been made, we can use the output from the statements to extract certain data. To use such data, we use the fetch statement which is usually used with a while or foreachloop.
Example:
<?php
while($row=$query->fetch()){
print_r($row);
}
?>
Output: All the contents of your table is printed. You'll see that the results are presented in both associative as well as numeric array.
Different Ways of Fetching
fetch(PDO::FETCH_NUM) – Fetching the results as a numeric array.
fetch(PDO::FETCH_ASSOC) – Fetching the results as an associative array.
fetch(PDO::FETCH_BOTH) – The default option, fetches both numberic as well as associative array.
fetch(PDO::FETCH_OBJ) – Fetches the results as an anonymous object.
fetch(PDO::FETCH_CLASS) – Fetches the results as a class.
There are more ways to fetch the results in the official PHP documentation.
Checking Whether the Row Results Exist
You can also add in an additional line before accessing the results of the query. This line will check or count whether there are rows that result after a query.
Example:
if($query->rowCount()){
//$query->fetch or fetchAll statements go here.
}else{
echo "No Results";
}
Closing PDO
At the end of all the queries, you should close it. Just assign NULL to the connection and you’re done.
Example:
<?php $connection = null; ?>
So, a full PDO connection, query, fetching output and finally closing it looks like:
<?php
try{
$connection = new PDO('mysql:host=localhost;dbname=myDatabase', 'username', 'password');
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
echo 'Error in database connection:<br />';
echo $e->getMessage();
die();
}
$id = 1; //Creating parameters
$query = $connection->prepare('Select * FROM table WHERE id = :id'); //Preparing the query
$query->execute(array('id' => $id)); //Binding the parameters
if($query->rowCount()){
while($row=$query->fetch()){
print_r($row);
}
}else{
echo "No Results.";
}
$connection = null;
?>
More Examples
The database name is myDatabase and the table name is table for convenience. Here’s the diagrammatic represenations of the MySQL table and its values.
A normal PDO Query
<?php
try{
$connection = new PDO('mysql:host=localhost;dbname=myDatabase','root','');
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
echo 'Error';
echo $e->getMessage();
die();
}
$id = 1;
$query = $connection->prepare('SELECT * FROM `table` WHERE id=:id');
$query->execute(array(':id'=>$id));
if($query->rowCount()){
while($row=$query->fetch()){
echo '<pre>';
print_r($row);
echo '</pre>';
}
}else{
echo "No Results";
}
?>
Output: Array ( [id] => 1 [0] => 1 [name] => Ashish [1] => Ashish [address] => Kathmandu [2] => Kathmandu )
Using PDO::FETCH_NUM
<?php
try{
$connection = new PDO('mysql:host=localhost;dbname=myDatabase','root','');
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
echo 'Error';
$e->getMessage();
die();
}
$id = 1;
$query = $connection->prepare('SELECT * FROM `table` WHERE id=:id');
$query->execute(array('id' => $id));
if($query->rowCount()){
while($row=$query->fetch(PDO::FETCH_NUM)){
echo '<pre>';
print_r($row);
echo '</pre>';
}
}else{
echo "No Results";
}
?>
Output: Array ( [0] => 1 [1] => Ashish [2] => Kathmandu )
Using PDO::FETCH_ASSOC
<?php
try{
$connection = new PDO('mysql:host=localhost;dbname=myDatabase','root','');
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
echo 'Error';
$e->getMessage();
die();
}
$id = 1;
$query = $connection->prepare('SELECT * FROM `table` WHERE id=:id');
$query->execute(array('id' => $id));
if($query->rowCount()){
while($row=$query->fetch(PDO::FETCH_ASSOC)){
echo '<pre>';
print_r($row);
echo '</pre>';
}
}else{
echo "No Results";
}
?>
Output: Array ( [id] => 1 [name] => Ashish [address] => Kathmandu )
Using PDO::FETCH_OBJ
<?php
try{
$connection = new PDO('mysql:host=localhost;dbname=myDatabase','root','');
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
echo 'Error';
$e->getMessage();
die();
}
$id = 1;
$query = $connection->prepare('SELECT * FROM `table` WHERE id=:id');
$query->execute(array('id' => $id));
if($query->rowCount()){
while($row=$query->fetch(PDO::FETCH_OBJ)){
echo '<pre>';
print_r($row);
echo '</pre>';
echo '<br />';
echo $row->name; //accessing like a class property
}
}else{
echo "No Results";
}
?>
Output: stdClass Object ( [id] => 1 [name] => Ashish [address] => Kathmandu ) Ashish
Using PDO::FETCH_CLASS
This is a special method of fetching with endless possibilities. When you fetch as a class, you have to create a class to go in the query. This allows for maximum code re-usability.
<?php
try{
$connection = new PDO('mysql:host=localhost;dbname=myDatabase','root','');
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
echo 'Error';
$e->getMessage();
die();
}
class newQuery{
public $id;
public $name;
public $address;
public $details;
public function __construct(){
$this->details = $this->name." lives in ". $this->address;
}
}
$id = 1;
$query = $connection->prepare('SELECT * FROM `table` WHERE id=:id');
$query->execute(array('id' => $id));
$query->setFetchMode(PDO::FETCH_CLASS,'newQuery');
if($query->rowCount()){
while($row=$query->fetch()){
echo '<pre>';
print_r($row);
echo '</pre>';
echo '<br />';
echo $row->details;
}
}else{
echo "No Results";
}
?>
Output: newQuery Object ( [id] => 1 [name] => Ashish [address] => Kathmandu [details] => Ashish lives in Kathmandu ) Ashish lives in Kathmandu
