Every developer should know that mysql functions for PHP are being depreciated and we must know use mysqli or PDO (PHP Data Objects). So, my today’s tutorial is about use PDO database connection in PHP. In the coming days, the PHP’s version 7 will be preferred in development.

What is in the next lines?

In the next lines, we will make a CRUD for PDO with database in PHP. So, let’s start the main part. Our main parts are

Creating the Database connection Fetching/reading the data-set Insert the new entries in the database Update the existing records Delete the records

1-Creating the Database connection

We all are familiar with the old way of database connection; but this is now useless.

<?php // Old way to connect with database mysql_connect('localhost', 'db_user', 'db_password') or die('Could not connect: ' . mysql_error()); ?> In PDO, we make a instance of the class, and give the name of driver, database name, db user and db password. <?php // New way to connect with database $dbcon = new PDO('mysql:host=localhost;dbname=myDB', $db_username,$db_password); ?> 1 2 3 4 5 6 7 8 9 10 11 <?php // Old way to connect with database mysql_connect ( 'localhost' , 'db_user' , 'db_password' ) or die ( 'Could not connect: ' . mysql_error ( ) ) ; ?> In PDO , we make a instance of the class , and give the name of driver , database name , db user and db password . <?php // New way to connect with database $dbcon = new PDO ( 'mysql:host=localhost;dbname=myDB' , $db_username , $db_password ) ; ?>

In term of the finding the errors, PDO is alsp the most powerful tool. We use try/catch method for handing with the errors.

<?php try { $dbcon = new PDO('mysql:host=localhost;dbname=myDB', $db_username, $db_password); $dbcon ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(PDOException $e) { echo 'ERROR: ' . $e->getMessage(); } ?> 1 2 3 4 5 6 7 8 <?php try { $dbcon = new PDO ( 'mysql:host=localhost;dbname=myDB' , $db_username , $db_password ) ; $dbcon -> setAttribute ( PDO:: ATTR_ERRMODE , PDO:: ERRMODE_EXCEPTION ) ; } catch ( PDOException $e ) { echo 'ERROR: ' . $e -> getMessage ( ) ; } ?>

PDO::ERRMODE_EXCEPTION will help us to firing the exceptions when they occer.

2-Fetching/reading the data set

PDO gives us the two ways for fetching the data.

a) query

b) execute

The Query way

In this way, we use query and manually escaping data with $dbcon->quote this method is equivalent to mysql_real_escape_string.

<?php $string = 'phpclicks'; try { // db connection $dbcon = new PDO('mysql:host=localhost;dbname=myDB', $db_username, $db_password); $dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $data = $conn->query('SELECT * FROM myTable WHERE name = ' . $dbcon->quote($string)); // $dbcon->quote used to protect SQL injection foreach($data as $rows) { print_r($rows); } } catch(PDOException $e) { echo 'ERROR: ' . $e->getMessage(); } ?> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 <?php $string = 'phpclicks' ; try { // db connection $dbcon = new PDO ( 'mysql:host=localhost;dbname=myDB' , $db_username , $db_password ) ; $dbcon -> setAttribute ( PDO:: ATTR_ERRMODE , PDO:: ERRMODE_EXCEPTION ) ; $data = $conn -> query ( 'SELECT * FROM myTable WHERE name = ' . $dbcon -> quote ( $string ) ) ; // $dbcon->quote used to protect SQL injection foreach ( $data as $rows ) { print_r ( $rows ) ; } } catch ( PDOException $e ) { echo 'ERROR: ' . $e -> getMessage ( ) ; } ?>

The Execute way

<?php $id = 33; 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(); } ?> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 <?php $id = 33 ; 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 ( ) ; } ?>

In the above code, we are using the prepare statement and after that we are executing it. Here, the SQL injection seems impossible. The reason is that $id will never directly get into the query. So, 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);

}

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

print_r($row);

}

print_r($row); } PDO::FETCH_ASSOC : It returns the array with its column names.

: It returns the array with its column names. PDO::FETCH_BOTH : It returns array with column name and its index starts with 0. The fefault fetch style is BOTH

: It returns array with column name and its index starts with 0. The fefault fetch style is BOTH PDO::FETCH_OBJ : It returns anonymous object with the property name.

: It returns anonymous object with the property name. PDO::FETCH_NUM: It returns the array with index by column number.

3. Insert the new entries in the database

Now, we will learn that how to insert the records in a table. We can also include multiple records insertion in same time with different data.

<?php try { // db connection $dbcon = new PDO('mysql:host=localhost;dbname=myDB', $db_username, $db_password); $ddcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $data = $dbcon->prepare('INSERT INTO users VALUES(:user_name)'); $data->bindParam(':user_name', $name); $name = 'Zubair Mushtaq'; $data->execute(); //for adding the multiple reocrds, follow this pattren $name = 'phpclicks.com'; $data->execute(); $name = 'demo.phpclicks.com'; $data->execute(); // exception handiling } catch(PDOException $e) { echo $e->getMessage(); } ?> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 <?php try { // db connection $dbcon = new PDO ( 'mysql:host=localhost;dbname=myDB' , $db_username , $db_password ) ; $ddcon -> setAttribute ( PDO:: ATTR_ERRMODE , PDO:: ERRMODE_EXCEPTION ) ; $data = $dbcon -> prepare ( 'INSERT INTO users VALUES(:user_name)' ) ; $data -> bindParam ( ':user_name' , $name ) ; $name = 'Zubair Mushtaq' ; $data -> execute ( ) ; //for adding the multiple reocrds, follow this pattren $name = 'phpclicks.com' ; $data -> execute ( ) ; $name = 'demo.phpclicks.com' ; $data -> execute ( ) ; // exception handiling } catch ( PDOException $e ) { echo $e -> getMessage ( ) ; } ?>

Updating the records is not so difficult. Here is the sample code snippet for it.

<?php $id = 33; $name = "Zubair Mushtaq"; try { $dbcon = new PDO('mysql:host=localhost;dbname=myDB', $db_username, $db_password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $data = $dbcon->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(); } ?> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 <?php $id = 33 ; $name = "Zubair Mushtaq" ; try { $dbcon = new PDO ( 'mysql:host=localhost;dbname=myDB' , $db_username , $db_password ) ; $conn -> setAttribute ( PDO:: ATTR_ERRMODE , PDO:: ERRMODE_EXCEPTION ) ; $data = $dbcon -> 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 ( ) ; } ?>

5.Delete the records

Now, the last thing that how we can delete the record with PDO in database.

<?php $id = 33; try { $dbcon= 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(); } ?> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 <?php $id = 33 ; try { $dbcon = 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 ( ) ; } ?>

So, that was the basic tutorial. Keep coding it and share it.