Tutorials, PHP & MySQL

It's been coming for some time but mysql_ has been officially depreciated and will be removed completely from future versions of PHP. Instead you can use either Mysqli or PDO personally I prefer PDO it also seems more popular in the development community. This tutorial will cover how to connect to PDO and will briefly go over exceptions.

You can connect to PDO directly but a better way is to create a class then it can extend PDO which is a much better approach as you will be able to use all existing PDO methods as well as making your own.

You create the class by using the name class followed by the name you want to call the class. it is a best practice to start the class name with a capital letter, to extend the class use extends then what you want to extend in most cases it would be a parent controller or class in this case its the PDO class.

I've chosen to call the class Database:

<?php

class Database extends PDO {

}

Next I want a connection to be made to the database as soon as the class is instantiated (gets called) so I will make use of a magic function called __construct this method that will automatically be called when the class is ran.

function __construct(){

}

To connect to the database we will need a way to pass the connection details to the connection, there's a few ways to go about this I've chosen to create an array of the details that will be passed to the construct. 

function __construct($config){

}

Next make a connection, since were extending PDO The connection details need to be passed to the parent class of PDO this is done by using parent:: then to call the parent constructer parent::__construct.

Pass in the config array which consists of the connection type, host, database name, username and password:

parent::__construct($config['db_type'].':host='.$config['db_host'].';dbname='.$config['db_name'],$config['db_username'],$config['db_password']);

If any of the details are wrong an exception will automatically be created and displayed whilst exceptions are useful they contain a lot of useful information to help identify the problem they also give sensitive information such as server paths etc so its best to use a try catch method.

It goes like this:

try {
    //make the connection
} catch (){
    //otherwise there was an error
}

To catch the error use PDOException followed by the var to catch it in such as $e then the $e will contain an array of information.

To display the relevant error and not any sensitive information once the error has been caught you can display it by calling the getMessage method of the exception:

try {
    //make the connection
} catch (PDOException $e){
    //otherwise there was an error
    die('ERROR: '. $e->getMessage());
}

Okay lets put the connection to the construct inside a try block:

try {
    parent::__construct($config['db_type'].':host='.$config['db_host'].';dbname='.$config['db_name'],$config['db_username'],$config['db_password']);
    $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){                
    die('ERROR: '. $e->getMessage());
}

Notice the new line: $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

What I'm doing here is setting an attribute of ATTR_ERRMODE which is the error mode then setting it to use exceptions, it can be set the any of the following:

  • PDO::ERRMODE_SILENT: Just set error codes.
  • PDO::ERRMODE_WARNING: Raise E_WARNING.
  • PDO::ERRMODE_EXCEPTION: Throw exceptions.

$this-> refers to the class name. I'm setting the error mode to use exceptions they can be seen when there's an error otherwise it could be set to silent in which case you see nothing if something is set incorrectly.

That's all we need for the class the next thing to do is include the class into a page where you want to run the class and instantiate it, save the class as database.php then in another page include the class create a config array that holds the server connection information.

Next create a new variable to hold the object once its instantiated by using = new Database($config); passing in the config array:

<?php
include('database.php');

$config = array(
    'db_type' => 'mysql',
    'db_host' => 'localhost',
    'db_name' => 'mydatabase',
    'db_username' => 'myusername',
    'db_password' => 'mypassword'
);

$db = new Database($config);

Save the page and run this in your browser if the page is blank everything is working correctly you will only see something it there is an error.

From there you can then use $db-> to call PDO methods such as query which is the equivalent to mysql_query:

$sql = 'SELECT name FROM table';
foreach ($db->query($sql) as $row) {
    echo $row['name'];
}

Here's the full class:

<?php 

class Database extends PDO{

    function __construct($config){

        try {
            parent::__construct($config['db_type'].':host='.$config['db_host'].';dbname='.$config['db_name'],$config['db_username'],$config['db_password']);
            $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e){                
            die('ERROR: '. $e->getMessage());
        }

    }

}
</pre>

<p>Also using the class:</p>

<pre lang="php">
<?php
include 'database.php';

$config = array(
    'db_type' => 'mysql',
    'db_host' => 'localhost',
    'db_name' => 'mydatabase',
    'db_username' => 'myusername',
    'db_password' => 'mypassword'
);

$db = new Database($config);