PDO Tutorial in PHP to Connect MySql Database

PDO refers to PHP Data Objects. It is a database access layer providing a uniform method of access to multiple databases.PHP Data Objects is written to provide consistent API. It means in future if you change your database, the code changes is very minimal.

Why PDO is better than MySql

1. It supports multiple database using single interface.

2. PHP Data Objects uses prepared statement so you don’t need to worry about sql injection.

3. It provides consistent error handling.

4. Most important MySql extension is deprecated as of PHP 5.5.0, and will be removed in the future

If you worked in PHP, then you are familiar with mysql syntax.

// In mysql, when we connect to database

mysql_connect('localhost', 'username', 'password') or die('Could not connect: ' . mysql_error());

In mysql if you want to escape special characters to prevent your code from sql injection, you need to use mysql_real_escape_string function. But in Prepared Statement you don’t need to worry about Sql Injection.

Let’s check the PDO syntax

// Creating $DatabaseConn object

$DatabaseConn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);

In the PHP Data Objects syntax first i used driver name which is mysql and then the required details such as host,dbname,username and password.

Error Handling in PDO

It has three error handling strategies

1. PDO::ERRMODE_SILENT – This is default mode. If you don’t set any error mode it will set this as default.

2. PDO::ERRMODE_WARNING – throws PHP Warnings

3. PDO::ERRMODE_EXCEPTION – throws Exception when they occur. Always use this mode during development.

 
try {

    $DatabaseConn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
    $DatabaseConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Setting ERRMODE

    // Query some Data

    $result = $DatabaseConn->query("select employeeId from employee");

    while($values = $result->fetch(PDO::FETCH_ASSOC)){

            echo $values['employeeId']."
";         // Print the values
      }

} catch(PDOException $e) {

    echo  $e->getMessage();  // Display error message
    echo  $e->getCode();    // Display error code

}

PDO Fetch Modes

To Fetch a result, It provides following choices.

1. PDO::FETCH_ASSOC – It returns an array. In above example i use this one.

    while($values = $query->fetch(PDO::FETCH_ASSOC)){
               print_r($values['employeeId']);
     }

output:

    Array ( [employeeId] => 56)

2. PDO::FETCH_NUM

while($values = $query->fetch(PDO::FETCH_NUM)){
               print_r($values['employeeId']);
     }

output:

Array ( [0] => 56)

3. PDO::FETCH_BOTH

while($values = $query->fetch(PDO::FETCH_BOTH)){
               print_r($values['employeeId']);
     }

output:

Array ( [employeeId] => 56 [0]=>56)

4. PDO::FETCH_OBJ

while($values = $query->fetch(PDO::FETCH_OBJ)){
  print_r($values['employeeId']);
}

output:

stdClass Object(

[employeeId] => 56

)

Fetch Mode Complete Documentation

Insert Operation

Inserting new data or updating a data using PHP Data Objects works in three steps first it prepares, then bind and finally execute.

i) Prepare
ii) Bind
iii) Execute

// Take two inputs

$username = $_POST['username'] = raj;
$password = $_POST['password'] = '12345';

try {
  $DatabaseConn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
  $DatabaseConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Setting ERRMODE

  $sql = "INSERT INTO users (username,password) VALUES (:username,:password)";
  $insert = $DatabaseConn->prepare($sql);
  $insert->execute(array(':username' => $username,
':password' => $password));

 } catch(PDOException $e) {
       echo $e->getMessage();
}

Conclusion

If you are working on some PHP projects, try to use Prepared Statements as it is much better than MySql api. If you have any comments or suggestions for the improvement please let us know.

admin

About admin

I am technology lover who loves to keep updated with latest technology. My interest field is Web Development.
Tagged , , . Bookmark the permalink.