PDO database connection in PHP

PDO database connection in PHP

PHP Data Object is a Database Connection Abstraction Library for PHP 5.
In this article i will explain you how to connect MySQL database using PDO connection and explain few things in this tutorial like database connection, insert, delete, update and select queries with prepare statement,multiple insert and error handling.

Connection
The old style of connecting to MySQL database:
<?php
# Connect
mysql_connect(‘localhost’, ‘database_user’, ‘database_password’) or die(‘Could not connect: ‘ . mysql_error());
?>

Using PDO we create a new instance of the class, and describe the name of driver,
database name, database user and database password:
<?php
#connect
$conn = new PDO(‘mysql:host=localhost;dbname=myDB’, $db_username, $db_password);
?>
We specify mysql as name of driver in this case and followed by database credential required.

Errors

If there is an error in database connection how can we identify so here is try/catch for error handling.
<?php
try {
$conn = new PDO(‘mysql:host=localhost;dbname=myDB’, $db_username, $db_password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo ‘ERROR: ‘ . $e->getMessage();
}
?>
PDO::ERRMODE_EXCEPTION will fire exceptions when occur. Using this procedure we can handle any exception.
There is two ways to fetch data query and execute we explain both.

1. Query
<?php
$string = ‘teststring’; # user submitted data
try {
#connection
$conn = new PDO(‘mysql:host=localhost;dbname=myDB’, $db_username, $db_password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$data = $conn->query(‘SELECT * FROM myTable WHERE name = ‘ . $conn->quote($string)); // $conn->quote used to protect SQL injection
foreach($data as $rows) {
print_r($rows);
}
} catch(PDOException $e) {
echo ‘ERROR: ‘ . $e->getMessage();
}
?>
In this code we used query and manually escaping data with $conn->quote this method is equivalent to mysql_real_escape_string; these both function escape quote user send in data.

2. Execute
<?php
$id = 10;
try {
#connection
$conn = new PDO(‘mysql:host=localhost;dbname=myDB’, $db_username, $db_password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$data = $conn->prepare(‘SELECT * FROM users WHERE user_id = :user_id’);
$data->execute(array(‘user_id’ => $id));

while($rows = $data->fetch()) {
print_r($rows);
}
} catch(PDOException $e) {
echo ‘ERROR: ‘ . $e->getMessage();
}
?>
In this example we used prepare statement and execute it after that. In this example SQL injection is almost impossible, because $id never direct get into the query. we used :user_id as a placeholder.

$data->fetch() simply shows array of records and you can make it more stylish with FETCH_OBJ like below.
while($row = $data->fetch(PDO::FETCH_OBJ)) {
print_r($row);
}
Few fetch styling parameters:

PDO::FETCH_ASSOC: return array with column names.

PDO::FETCH_BOTH: Default fetch style is BOTH it return array with column name and index start with 0.

PDO::FETCH_OBJ: return anonymous object with property name.

PDO::FETCH_NUM: return array with index by column number.

Result validation with count if there is no records found in given query:
$data->execute(array(‘user_id’ => $id));
$result = $data->fetchAll();

#count records
if ( count($result) ) {
foreach($result as $row) {
print_r($row);
}
} else {
echo “data not found.”;
}
Full code of fetching:
<?php
$id = 10;
try {
#connection
$conn = new PDO(‘mysql:host=localhost;dbname=myDB’, $db_username, $db_password);
$data = $conn->prepare(‘SELECT * FROM users WHERE user_id = :user_id’);
$data->execute(array(‘user_id’ => $id));

$result = $data->fetchAll();

if ( count($result) ) {
foreach($result as $row) {
print_r($row);
}
} else {
echo “data not found.”;
}
} catch(PDOException $e) {
echo ‘ERROR: ‘ . $e->getMessage();
}
?>
Insert
This example shows you how to insert record in a table and also include multiple records insertion with different data.
<?php
try {
#connection
$conn = new PDO(‘mysql:host=localhost;dbname=myDB’, $db_username, $db_password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$data = $conn->prepare(‘INSERT INTO users VALUES(:user_name)’);
$data->bindParam(‘:user_name’, $name);

$name = ‘test name’;
$data->execute();

#if you want to insert multiple records add new code block like this repeat this for multiple records.
$name = ‘webcomers.com’;
$data->execute();

$name = ‘demo’;
$data->execute();

#exception handiling
} catch(PDOException $e) {
echo $e->getMessage();
}
?>
Prepare query once and execute it multiple time as you required in your script.

Insert single record and show affected rows:
$data = $conn->prepare(‘INSERT INTO users VALUES(:user_name)’);
$data->execute(array(
‘:user_name’ => ‘Huzoor Bux’
));

# get the number of affected rows.
echo $data->rowCount(); // 1
Update
<?php
$id = 10;
$name = “test name”;

try {
$conn = new PDO(‘mysql:host=localhost;dbname=myDB’, $db_username, $db_password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$data = $conn->prepare(‘UPDATE users SET user_name = :user_name WHERE user_id = :user_id’);
$data->execute(array(
‘:user_id’   => $id,
‘:user_name’ => $name
));

echo $data->rowCount(); // 1
} catch(PDOException $e) {
echo ‘Error: ‘ . $e->getMessage();
}
?>
Delete
<?php
$id = 10; // select id to delete from users table

try {
$conn = new PDO(‘mysql:host=localhost;dbname=myDB’, $db_username, $db_password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$data = $conn->prepare(‘DELETE FROM users WHERE user_id = :user_id’);
$data->bindParam(‘:user_id’, $id); // we used bindParam method
$data->execute();

echo $data->rowCount(); // 1
} catch(PDOException $e) {
echo ‘Error: ‘ . $e->getMessage();
}
?>.