Home » Code » Roll your own PDO PHP Class

Roll your own PDO PHP Class

Posted by on October 1st, 2012

Roll your own PDO PHP Class
Last week I introduced PDO, a database abstraction layer that you can use in your applications to allow for future flexibility in your database choice and protect you from SQL Injection.

Whilst the class we looked at is great for getting started with PDO quickly, it does have some restrictions if you want to do more complicated things than simple selects, inserts, updates and deletes.

I think it is also far more beneficial to know exactly what is going on under the hood of your code. By knowing exactly how something works you will gain a much deeper sense of understanding and you will be able to troubleshoot and advance your code when the time comes.

So if you haven’t read last week’s post, go back and read that as an introduction to PDO, Prevent PHP SQL Injection with PDO Prepared Statements.

Setting up the files

The first thing to do is to create the files we are going to need to work with.

Firstly we need a file to hold our class. I will be referring to this as databass.class.php.

Secondly we need a file to use our new class, I will be referring to this as tutorial.php.

Next, in the database.class.php we need to create the class. Copy and paste the following into that file.

<?php
class Database{

}

Here we are simply defining a new class called Database.

Now in the tutorial.php file we need to include the class. Copy and paste the following line to include the database class in your tutorial file.

// Include database class
include 'database.class.php';

Next, still in tutorial.php, we need to define our connection configuration.

// Define configuration
define("DB_HOST", "localhost");
define("DB_USER", "username");
define("DB_PASS", "password");
define("DB_NAME", "database");

Back in database.class.php, we need to set up the variables in the class.

private $host = DB_HOST;
private $user = DB_USER;
private $pass = DB_PASS;
private $dbname = DB_NAME;

And finally we need to instantiate a new instance of the database class.

// Instantiate database.
$database = new Database();

Hopefully you understand all of the above as there is nothing specifically PDO related so far.

Construct

The first method we will look at is the construct method.

public function __construct(){

}

When a class has a construct method, it is automatically called on all newly-created objects. This enables you to set up any initialisation you might need on the new object before it is used.

Database Source Name

The next thing to do is to set the Database Source Name.

When using PDO, you need to set the database connection method because PDO can be used with many different types of database. Each different database has a slightly different connection string. To set a particular database connection type, we just set the specific string.

// Set DSN
$dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;

In this example I’m setting the DSN to MySQL and I’m using the host and the database name we defined earlier.

If you know you are going to be using MySQL for the foreseeable future, this is probably going to be fine. However, it does take away some of the flexibility and reusability of PDO by hardcoding the connection into a string like this. To make this more flexible and resizable you would use Dependency Injection. I think Dependency Injection deserves it’s own tutorial, so I’ll leave that for another day. Just be aware that you could make your construct a lot more future proof.

Options

When connecting to the database we can set a couple of different attributes. To set these options on connection, we simply include them as an array.

// Set options
$options = array(
    PDO::ATTR_PERSISTENT => true, 
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);

PDO::ATTR_PERSISTENT

This option sets the connection type to the database to be persistent. Persistent database connections can increase performance by checking to see if there is already an established connection to the database.

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

PDO can use exceptions to handle errors. Using ERRMODE_EXCEPTION will throw an exception if an error occurs. This then allows you to handle the error gracefully.

To read more about PHP exceptions, take a look at The ins and outs of PHP exceptions.

Handling exceptions in PHP deserves it’s own tutorial and is out of the scope of this tutorial. I’ll write a tutorial for handling exceptions in the coming weeks.

Try/Catch

Next we use a try/catch block to attempt to make a connection, or handle any exceptions if an error occurs.

First we need to create a new variable, $dbh (Database Handler).

Declare a new variables at the top of your class for the Database Handler and any errors.

private $dbh;
private $error;

Back in the construct method we attempt a new PDO instance.

try {
    $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
}

Next we catch any errors that might occur.

// Catch any errors
catch (PDOException $e) {
    $this->error = $e->getMessage();
}

Now whenever you create a new instance of your database class, a new PDO instance will be created. If any errors occur when trying to create a new PDO instance, they will be handled gracefully.

So far your class should look like this:

class Database{
    private $host      = DB_HOST;
    private $user      = DB_USER;
    private $pass      = DB_PASS;
    private $dbname    = DB_NAME;

    private $dbh;
    private $error;

    public function __construct(){
        // Set DSN
        $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;
        // Set options
        $options = array(
            PDO::ATTR_PERSISTENT    => true,
            PDO::ATTR_ERRMODE       => PDO::ERRMODE_EXCEPTION
        );
        // Create a new PDO instanace
        try{
            $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
        }
        // Catch any errors
        catch(PDOException $e){
            $this->error = $e->getMessage();
        }
    }
}

Query

The next method we will be creating is the query method.

The query method introduces the $stmt variable to hold the statement.

So firstly, at the top of the class, declare the new variable.

private $stmt;

Prepare

The query method also introduces the PDO::prepare function.

The prepare function allows you to bind values into your SQL statements. This is important because it takes away the threat of SQL Injection because you are no longer having to manually include the parameters into the query string.

Using the prepare function will also improve performance when running the same query with different parameters multiple times.

Copy and paste the following method into your class:

public function query($query){
    $this->stmt = $this->dbh->prepare($query);
}

Bind

The next method we will be looking at is the bind method. In order to prepare our SQL queries, we need to bind the inputs with the placeholders we put in place. This is what the Bind method is used for.

The main part of this method is based upon the PDOStatement::bindValue PDO method.

Firstly, we create our bind method and pass it three arguments.

public function bind($param, $value, $type = null){

}

Param is the placeholder value that we will be using in our SQL statement, example :name.

Value is the actual value that we want to bind to the placeholder, example “John Smith”.

Type is the datatype of the parameter, example string.

Next we use a switch statement to set the datatype of the parameter:

if (is_null($type)) {
	switch (true) {
		case is_int($value):
			$type = PDO::PARAM_INT;
			break;
		case is_bool($value):
			$type = PDO::PARAM_BOOL;
			break;
		case is_null($value):
			$type = PDO::PARAM_NULL;
			break;
		default:
			$type = PDO::PARAM_STR;
	}
}

Finally we run bindValue.

$this->stmt->bindValue($param, $value, $type);

The final bind method is:

public function bind($param, $value, $type = null){
    if (is_null($type)) {
        switch (true) {
            case is_int($value):
                $type = PDO::PARAM_INT;
                break;
            case is_bool($value):
                $type = PDO::PARAM_BOOL;
                break;
            case is_null($value):
                $type = PDO::PARAM_NULL;
                break;
            default:
                $type = PDO::PARAM_STR;
        }
    }
    $this->stmt->bindValue($param, $value, $type);
}

Execute

The next method we will be look at is the PDOStatement::execute. The execute method executes the prepared statement.

public function execute(){
    return $this->stmt->execute();
}

Result Set

The Result Set function returns an array of the result set rows. It uses the PDOStatement::fetchAll PDO method. First we run the execute method, then we return the results.

public function resultset(){
    $this->execute();
    return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
}

Single

Very similar to the previous method, the Single method simply returns a single record from the database. Again, first we run the execute method, then we return the single result. This method uses the PDO method PDOStatement::fetch.

public function single(){
    $this->execute();
    return $this->stmt->fetch(PDO::FETCH_ASSOC);
}

Row Count

The next method simply returns the number of effected rows from the previous delete, update or insert statement. This method use the PDO method PDOStatement::rowCount.

public function rowCount(){
    return $this->stmt->rowCount();
}

Last Insert Id

The Last Insert Id method returns the last inserted Id as a string. This method uses the PDO method PDO::lastInsertId.

public function lastInsertId(){
    return $this->dbh->lastInsertId();
}

Transactions

Transactions allows you to run multiple changes to a database all in one batch to ensure that your work will not be accessed incorrectly or there will be no outside interferences before you are finished. If you are running many queries that all rely upon each other, if one fails an exception will be thrown and you can roll back any previous changes to the start of the transaction.

For example, say you wanted to enter a new user into your system. The create new user insert worked, but then you had to create the user configuration details in a separate statement. If the second statement fails, you could then roll back to the beginning of the transaction.

Transactions also prevent anyone accessing your database from seeing inconsistent data. For example, say we created the user but someone accessed that data before the user configuration was set. The accessing user would see incorrect data (a user without configuration) which could potentially expose our system to errors.

To begin a transaction:

public function beginTransaction(){
    return $this->dbh->beginTransaction();
}

To end a transaction and commit your changes:

public function endTransaction(){
    return $this->dbh->commit();
}

To cancel a transaction and roll back your changes:

public function cancelTransaction(){
    return $this->dbh->rollBack();
}

Debug Dump Parameters

The Debut Dump Parameters methods dumps the the information that was contained in the Prepared Statement. This method uses the PDOStatement::debugDumpParams PDO Method.

public function debugDumpParams(){
    return $this->stmt->debugDumpParams();
}

Using your PDO class

Now that we’ve finished writing the database class, it’s time to test it out.

The remaining bit of this tutorial will be using the tutorial.php file you created at the very start.

I’m going to be using the same MySQL table that I created in last week’s tutorial. If you haven’t read last week’s tutorial, you can create the table by running the following SQL.

CREATE TABLE mytable (
    ID int(11) NOT NULL AUTO_INCREMENT,
    FName varchar(50) NOT NULL,
    LName varchar(50) NOT NULL,
    Age int(11) NOT NULL,
    Gender enum('male','female') NOT NULL,
    PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

Insert a new record

Firstly you need to instantiate a new database.

$database = new Database();

Next we need to write our insert query. Notice how I’m using placeholders instead of the actual data parameters.

$database->query('INSERT INTO mytable (FName, LName, Age, Gender) VALUES (:fname, :lname, :age, :gender)');

Next we need to bind the data to the placeholders.

$database->bind(':fname', 'John');
$database->bind(':lname', 'Smith');
$database->bind(':age', '24');
$database->bind(':gender', 'male');

And finally we run execute the statement.

$database->execute();

Before running the file, echo out the lastInsertId function so you will know that the query successfully ran when viewed in the browser.

echo $database->lastInsertId();

Insert multiple records using a Transaction

The next test we will try is to insert multiple records using a Transaction so that we don’t have to repeat the query.

The first thing we need to do is to begin the Transaction.

$database->beginTransaction();

Next we set the query.

$database->query('INSERT INTO mytable (FName, LName, Age, Gender) VALUES (:fname, :lname, :age, :gender)');

Next we bind the data to the placeholders.

$database->bind(':fname', 'Jenny');
$database->bind(':lname', 'Smith');
$database->bind(':age', '23');
$database->bind(':gender', 'female');

And then we execute the statement.

$database->execute();

Next we bind the second set of data.

$database->bind(':fname', 'Jilly');
$database->bind(':lname', 'Smith');
$database->bind(':age', '25');
$database->bind(':gender', 'female');

And run the execute method again.

$database->execute();

Next we echo out the lastInsertId again.

echo $database->lastInsertId();

And finally we end the transaction

$database->endTransaction();

Select a single row

The next thing we will do is to select a single record.

So first we set the query.

$database->query('SELECT FName, LName, Age, Gender FROM mytable WHERE FName = :fname');

Next we bind the data to the placeholder.

$database->bind(':fname', 'Jenny');

Next we run the single method and save it into the variable $row.

$row = $database->single();

Finally, we print the returned record to the screen.

echo "<pre>";
print_r($row);
echo "</pre>";

Select multiple rows

The final thing we will do is to run a query and return multiple rows.

So once again, set the query.

$database->query('SELECT FName, LName, Age, Gender FROM mytable WHERE LName = :lname');

Bind the data.

$database->bind(':lname', 'Smith');

Run the resultSet method and save it into the $rows variable.

$rows = $database->resultset();

Print the return records to the screen.

echo "<pre>";
print_r($rows);
echo "</pre>";

And finally display the number of records returned.

echo $database->rowCount();

Conclusion

And there you have it, your first rolled PDO database abstraction class. Hopefully this tutorial has introduced the main aspects of PDO and how it actually works. I think actually creating something like this is far more beneficial than using someone else’s library because you need to fully understand how all of this works, even if it takes longer in the long run.

I’ve missed out, or brushed over a number of important areas throughout this tutorial to try and keep it on topic. If you are interested in learning more about these related areas, subscribe to our RSS feed or follow us on Twitter to keep up to date with the latest posts and tutorials.

If you have any questions, or you can a think of a way to improve this class, please leave a comment!

Philip Brown

Hey, I'm Philip Brown, a designer and developer from Durham, England. I create websites and web based applications from the ground up. In 2011 I founded a company called Yellow Flag. If you want to find out more about me, you can follow me on Twitter or Google Plus.

Join the Culttt

Become an insider and join the Culttt

  • anon

    I was writing my own DB class to use with PDO and this helped me a lot. Thnx.

    • http://culttt.com/ Philip Brown

      That’s great! Glad to hear its helping people!

  • Pingback: Introduction to jQuery | Culttt()

  • Damien

    Thanks for these great tutorials, they are helping me a lot

    • http://culttt.com/ Philip Brown

      No problem Damien, glad it helped!

  • Dries

    Hi Philip.
    For some reason, i get this error “Fatal error: Call to a member function bindValue() on a non-object” at this line: $this->stmt->bindValue($param, $value, $type); Any ideas?

    • http://culttt.com/ Philip Brown

      Hi Dries, it sounds like you haven’t instantiated the object correctly.

      Try something simple, like this first:

      $database = new Database();
      $database->query(‘SELECT age FROM users WHERE name = :name’);
      $database->bind(‘:name’, ‘John’);
      print_r($database->single();

      Can you get something like this working on your database?

      • Dries

        fixed :)
        Stupid me.
        Wrong table name.
        tnx

        • http://culttt.com/ Philip Brown

          Glad to hear it!

          If you have any trouble, give me a shout!

  • John

    thank you so much for this post – very informative.

    how would you instantiate this database class in another class?

    i am looking at something like –

    private $_db;
    public function __construct(Database $db){

    $this->_db = $db;

    }

    would be very interested to see how you use it –

    many thanks in advance

    :)

    • http://culttt.com/ Philip Brown

      Hi John, I’m glad to hear it helped you!

      You would simply instantiate as you normally would.

      $this->_db = new Database;

      This is assuming you have some kind of autoload in place.

  • Guest

    thanks phillip,

    so you would call the new Database in the class and not on the page – as i have seen suggested?

    • http://culttt.com/ Philip Brown

      You can either create it within the class, or you are right in saying you could pass it in as an argument when the class is constructed. This gives the benefit of being able to easily switch out the database Class, or for use in Dependency Injection.

      Either way will work. I would suggest you do whatever feels right for your project.

      • John

        thanks philip :)

  • Tom K

    This class is a great tool to have! Thank you very much for posting this, I’ve been using it and it’s great! Is there a method you could make to pass the bind method an array instead of having multiple bind (sometimes 6+) statements?

    • http://culttt.com/ Philip Brown

      Hi Tom, that’s awesome! I’m so happy to hear that you have been using it.

      That’s a great suggestion. I would make a new method that takes an array as an argument, and then iterates through each value and call the bind method. This way you leave the original method in tact should you want to use, and you can simply build your new method on top of the single bind method.

      Something like this…

      $array = (
      “first” => “Philip”
      “last” => “Brown”
      );

      $database->bindArray($array);

      public function bindArray($array){
      foreach($array as $key => $value){
      $this->bind($key, $value);
      }
      }

      Use this as a starting point, I’m sure you could probably make it even better to fit your purposes. If you have any trouble, feel free to give me a shout!

      Thanks Tom!

      • Tom K

        Thanks! This is what I came up with too… and it works! A useful addition to this great class.
        I will try to keep finding useful methods to add to this class.

        • http://culttt.com/ Philip Brown

          Hi Tom, that’s great! Let me know what you come up with. The best ideas always come from solving real world problems!

  • Trae Moore

    Fricking awesome tutorial.. I learned quite a bit from it.. here is a custom insertquery that i made from it. it is fully functional… although i have been writing php for about 2 weeks now im not off to a bad start.. all you have to do is pass it 3 params ( tablename, arr of fields to edit, array of values for the fields).. THANKS again..!!!!!

    code:

    public function insertQuery($table, $fields, $values) {

    $holders = $fields;

    for($i = 0;$i query($query);

    $this->bindArray($array);

    $this->execute();

    }

    • traemoore

      i forgot to error catch and i have to change some stuff for if you only need to change one field..

      heres the fix

      public function insertQuery($table, $fields, $values) {

      try {

      if (gettype($fields) != ‘array’) {

      $fields = (array) $fields;

      $values = (array) $values;

      }

      $holders = $fields;

      for ($i = 0; $i query($query);

      $this->bindArray($array);

      $this->execute();

      } catch (PDOException $e) {

      $this->error = $e->getMessage();

      }

      }

      • Trae

        please remember im a noob :P – correction:

        if ((gettype($fields) != ‘array’) || (gettype($values) != ‘array’))

        • http://culttt.com/ Philip Brown

          That’s awesome Trae! It’s really good that you are digging into the code to find your own ways to improve it. Copy and pasting will only get you so far, so you are already on the way to becoming a really good developer!

          • Trae Moore

            Thank you, Philip. I look forward to reading some more of your publications.

  • harrison

    Thanks Philip this awesome tutorial!!

    I have some problem if I want to update in database
    should be code like below?
    something wrong with this code? Thanks..

    $database->query(‘UPDATE table SET password = : password, activate = activate WHERE id = id’);

    $database->bind(‘: id’, ‘4’);
    $database->bind(‘: password’, ‘c’);
    $database->bind(‘: activate’, ‘0’);
    $database->execute();

    • http://culttt.com/ Philip Brown

      Hi Harrison, glad you found it helpful.

      Make sure you use :password, :activate and :id as the placeholders. You’ve missed the : off activate and id and there shouldn’t be a gap.

      Hope this helps!

  • Dries

    Hi Philip,

    I’m having troubles with an update query.

    $email=test@test.be
    $conn = new Conn();
    $conn->query(‘UPDATE user SET password = :pass, activate = :str_activate WHERE email = :email’);
    $conn->bind(‘:pass’,$pass);
    $conn->bind(‘:str_activate’,$str_activate);
    $conn->execute();

    Not sure what is wrong here:

    Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens’ in url

    Stack trace: #0 : url

    PDOStatement->execute() #1

    Conn->execute() #2 {main} thrown in url on line 64

    • http://culttt.com/ Philip Brown

      Hi Dries

      “Invalid parameter number: number of bound variables does not match number of tokens” means you are not using the right number of placeholders.

      You are using :padd, :str_activate and :email, but you have only used bind on two of them. You need to also bind email.

      Don’t worry, I do that all the time :D

      • Dries

        Me so stupid.
        There should be an autobind(); :))))

        • http://culttt.com/ Philip Brown

          That’s a really good idea, you should write one!

  • Fastbit Informatica

    Hi Very nice Work
    Update Sample

    $database=new DBConnection();

    $UserID = 20;
    $database->query(‘UPDATE mytable SET FName = :fnname, LName = :lname WHERE ID = :Userid’);

    $database->bind(‘:Userid’, $UserID);
    $database->bind(‘:fname’, ‘Fastbit’);
    $database->bind(‘:lname’, ‘Informatica’);
    $database->execute();

    /*SHOW THE UPDATE RECORD*/

    $database->query(‘SELECT * FROM mytable WHERE ID= :Userid’);
    $database->bind(‘:Userid’,$UserID);
    $rows = $database->resultset();
    echo ”; print_r($rows); echo “”;

    /*fastbi.pt*/

  • Mandy

    Hi Philip,

    Thanks for your great tutorial.
    I always use $this->dbh = null to close the connection.
    Do you think it is necessary when using this class?

    • http://culttt.com/ Philip Brown

      Hi Mandy,

      Thank you, I’m glad you found it useful! :)

      It’s not necessary to close the connection, but it is good practice to do so. This is probably even more important if you have a lot of processing after you have finished with the connection.

      Hope this helps.

  • George Carabus

    Does “$database = new Database();” go in the database.class.php or tutorial.php?

    • http://culttt.com/ Philip Brown

      Hi George, that line would go into tutorial.php

  • http://www.facebook.com/thomas.v.blomberg Thomas Vincent Blomberg

    Hi Phillip I would like to know your opinion on this. I created a Database class extending PDO. I wrote an insert function which I pass it a two dimensional array. The array will consist of the field from the database and the value. I wanted to be able to use the insert function without actually having to write the code for the INSERT statement and so that you would not actually have to know the columns or values. Someone basically ripped me apart on Stackoverflow for writing it this way, but gave me know good feedback. I am fairly knew to the language. Do you think this is a bad way to do it?

    public function insert($insertArray,$table) {

    $this->insertArray = $insertArray;

    $this->table = $table;

    $length = count($this->insertArray);

    if($this->tableExists($table)) {

    /*Create a for loop to run through fields and binded values*/

    for($i = 0;$i field .= $this->insertArray[$i][0];

    $this->bindValues .= ‘:’ . $this->insertArray[$i][0];

    if($i == $length – 1) {

    }

    else

    {

    $this->field .= ‘,';

    $this->bindValues .= ‘,';

    }

    }

    /*Prepare Insert Statement*/

    $insert = “INSERT INTO $this->table ($this->field) VALUES ($this->bindValues)”;

    $statement = $this->prepare($insert);

    /*For loop to run through the actual values and bind the values*/

    for($j = 0;$j bindValue(‘:’ . $this->insertArray[$j][0],$this->insertArray[$j][1]);

    }

    /*Check to see if insert executed correctly if not send error code*/

    if($statement->execute()) {

    return true;

    }

    else {

    return $statement->errorCode();

    }

    }

    else {

    return false;

    }

    }

    Thank you in advance for your help. I really enjoyed your article.

    • http://culttt.com/ Philip Brown

      Hi Thomas, I would probably split up that one method into many methods so the logic isn’t all in once place. I believe it is better if you can distil methods down so they are only concerned with one thing. This makes the code a lot easier to understand for a new developer or when you come back to it at some point in the future.

      You also want to ensure your code can easily be tested. Take a look at these two posts:
      http://culttt.com/2013/03/11/what-is-test-driven-development/
      http://culttt.com/2013/03/13/getting-started-with-phpunit/

      By splitting down this method it will make it much easier to write good tests to ensure your code is working as you expect it.

      Another important thing to consider is dependancy injection. This basically means de-coupling your code so that any dependant objects can be easily replaced. For example, you should write your code so that if you ever wanted to replace the database, it would be very easy to do so.

      And finally, I see what you are trying to achieve with your code. You are wanting to provide a simple API so you class has to do all of the heavy lifting? This is a great way of thinking about your code. By abstracting everything away, you again make it much easier to switch out components in the background, but still use the same API within your actually application. Have a read into the Facade Pattern. I’m going to write a PHP Facade Pattern tutorial very soon as Laravel 4 (http://laravel.com/) makes great use of it and it solves a number of problems. It’s really good that you are already thinking of writing your code at this level.

      Check back soon for my Facade Pattern tutorial, and take a look at Laravel for some good practices when writing PHP (https://github.com/illuminate) You will pick up a lot by reading through a framework like Laravel, breaking it down and understanding how it works and why it was written like that.

      Hope this helps Thomas!

      • http://www.facebook.com/thomas.v.blomberg Thomas Vincent Blomberg

        This was extremely helpful. Thank you for the kind words as well. I love your blog! I look forward to future posts.

        • http://culttt.com/ Philip Brown

          Thank you Thomas, that means a lot to me :D

  • http://www.facebook.com/ekeyte Eric Keyte

    Philip, terrific article. You’ve answered some questions for me.

    One brief question, though:

    When developing your construct:
    public function __construct(){
    // Set DSN
    $dsn = ‘mysql:host=’ . DB_HOST . ‘;dbname=’ . $this->dbname;

    You already created a private variable for DB_HOST. See: private $host = DB_HOST

    Why are you using the constant in your $dsn variable I just quoted? What am I missing here?

    Thanks!

    • http://www.facebook.com/ekeyte Eric Keyte

      I hate to tack this on, but with the PDO object, you can run a standard query such as $dbh->query(“SELECT * FROM table”);

      In the query method that you’re describing in the article, it seems to only allow for use as a prepared statement. Would it be better to build methods to extend all PDO methods or is that futile?

      Thanks again!

      • http://culttt.com/ Philip Brown

        Another great question Eric, yes you’re right, if you were going to be using this within a larger application that is something you could definitely do. I wanted to keep this tutorial as simple as possible to show how PDO is much better than using a raw mysql connection.

        Rather than reinventing the wheel, you might want to look at Laravel’s Fluent Query Builder http://laravel.com/docs/database/fluent and Eloquent ORM. Both provide great examples of how to construct these classes and how they can be effective in small and large scale websites.

        If you still want to write your own, take a look at the source of github. It’s really well commented and should provide you with a good understanding of how this all works.

    • http://culttt.com/ Philip Brown

      Hi Eric, great spot, that was a mistake by me :P

      You’re right in thinking it should be the private variable

  • alfonsocaponi

    Hi, I’ve three simple questions (sorry for my poor english):

    1) into __construct, is there a “clean way” to stop the rest of page if I’ve an error in db connection? I don’t want continue with select etc..
    2) having multiple tables, where can I declares it? If I’ve define(“DB_TABLE”,”tbl_name”); in config.php, how can I use $tbl_name in tutorial.php?
    3) using $rows = $database->resultset(); what is the best way to do a “foreach”? I would check every value etc..

    Thank you very much

    • http://culttt.com/ Philip Brown

      Hi Alfonso,

      1. That’s good thinking. There is already a try…catch set up that will catch any errors so you can handle them gracefully.

      2. I wouldn’t define your tables names in a config file because as you say, as soon as you want to use more than one table this will become difficult. I would just use the table name in the query string.

      3. You could simply do something like this

      $value){
      echo $key . ” ” . $value . “”;
      }

      • alfonsocaponi

        Very good. And if I have a “select count(*)…”? I’m trying to add this method but it seem doesn’t works:

        public function selectCount(){
        return $this->stmt->fetchColumn();
        }

        Thank you very much.

        p.s. have you thought of making a new tutorial, the continuation of this with a real case? (simple address book with results splitted into pages (a-c,d-f,…))

        • alfonsocaponi

          Ok, maybe miss “$this->execute();” into the rowCount and selectCount functions

          • http://culttt.com/ Philip Brown

            Haha, no problem.

            Yeah, I’ll probably write some full application tutorials that incorporate it in the future. Nothing concrete planned at the minute though. Keep checking back for new PHP tutorials though!

  • step

    Is there a better way to use rowCount? I’m trying something along these lines.

    $db = new Database();

    $db->query(‘select * from customers’);

    $majors = $db->resultSet();

    echo $majors->rowCount(); //this doesn’t work. I wanted to try saving this for repeated use on the page, but only $db->rowCount() works. I have several queries that are done, so by the time I get to calling the rowCount again, the query is a different number. Suggestions?

    • step

      actually, it appears I can use a variable before calling another query. nvrmnd.

      • http://culttt.com/ Philip Brown

        I would just store the counts in an associative array. You can then just access them by

        echo $counts['majors'];

  • JGP

    Hi,

    Is there a way to extend this class so it can handle multiple repeated placeholders as outlined in this article: http://stackoverflow.com/questions/7603896/php-pdo-prepare-repetitive-variables

    I’m trying to get past the issue of getting the error: Invalid parameter number: parameter was not defined’

    Thanks,
    JGP

    • http://culttt.com/ Philip Brown

      Is the StackOverflow accepted answer not what you were looking for? I would perhaps look at an ORM like Laravel’s Eloquent if you are trying to do more advanced things http://laravel.com/docs/database/eloquent. It’s often easier than reinventing the wheel.

  • bob the nom nom

    so im doing the multiple row selector but how can i do a while loop and assign each array value to a variable?

    small “example below”

    $rows = $database->resultset();

    while (????) {
    $email = $rows[“email”];
    }

    thx! this article is amazing

    • bob the nom nom

      Actually, I figured I’ll just use this:

      $rows = $database->resultset();

      foreach($rows as $array){
      $email = $array[‘email’];
      }

      idk works for me lol

      • http://culttt.com/ Philip Brown

        Hi Bob, that’s exactly what I would do! Great job in working it out for yourself :D

  • bob the nom nom

    I know this is really to keep everything organized and object-oriented but…

    // traditional way — all I do for this is the try & catch thing to connect to the db ($con)
    $get_signup = ‘INSERT INTO users (email, username) VALUES (:email, :username)';
    $signup=$con->prepare($get_signup);
    $signup->bindParam(‘:email’,$email);
    $signup->bindParam(‘:username’,$username);
    $signup->execute();

    // with this database class
    $db->query(‘INSERT INTO users (email, username) VALUES (:email, :username)’);
    $db->bind(‘:email’,$email);
    $db->bind(‘:username’,$username);
    $db->execute();

    yea, it looks a little cleaner but…
    Is it worth it for the performance lost with the database class?

    • http://culttt.com/ Philip Brown

      Hi Bob,

      I’m not sure to be honest, I would probably just go with which every way you prefer. Performance is important, but also readability, testing etc, etc should also be considered. I try to write code where it won’t be a pain to change in the future because the lessons I learn tomorrow will usually always make today’s code more efficient.

  • Björn Grunde

    Thanks for a great tutorial, it helped me understand OOP and PDO. Something that would very interesting to see would be a tutorial that contiues this one with secure login/register and sessions using PDO. Trying to reconstruct my cms from procedural code (that is a mess) to a clean oop solution, not sure tho how to create the login so it is secure :)

    • http://culttt.com/ Philip Brown

      Hi Björn, I’m glad it helped you :)

      Look out for my upcoming massive tutorial on building a complete Web Application for scratch series. I’m going to cover everything you need to turn an idea into a successful online product!

    • http://www.facebook.com/profile.php?id=523341528 Afonso Gomes

      I was gonna comment to say what a fantastic tutorial this one is, to use as an introduction to PDO for a noob like me and along the way learn some more about OOP. But then I saw this comment so my comment is now a 2-in-1.

      Björn Grunde: Try hybridauth! It’s a secure open-source login system with self database and/or social login with more than 25 providers available (I’m using Self database, Facebook, Twitter, Linkedin and Google+) :) You wont regret it trying it out.

      @philip_brown:disqus : Amazing work! Thanks for this. Already learnt a lot about PDO and I’m inplmenting this solution!!

      • http://culttt.com/ Philip Brown

        Thanks Afonso :)

  • bob

    if i have say 3 other classes that all require a database connection how do i do that? would it mean having different connections open at the one time.

  • weiloon

    why im getting this error by using the insert query

    Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens’ in C:wampwwwcommondatabase.class.php on line 55

    PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:wampwwwcommondatabase.class.php on line 55

    and here is the code

    $database->beginTransaction();

    $database->query = “INSERT INTO member_info (username, password, salt, email, contact, point, credit, birthdate, banned) VALUES (:username, :password, :salt, :email, :contact, :point, :credit, :bdate, :banned)”;

    $salt = dechex(mt_rand(0, 2147483647)) . dechex(mt_rand(0, 2147483647));

    $password = hash(‘sha256′, $password . $salt);

    for($round = 0; $round bind(‘:username’, $username); // here is the error line
    $database->bind(‘:password’, $password);
    $database->bind(‘:salt’, $salt);
    $database->bind(‘:email’, $email);
    $database->bind(‘:contact’, $contact);
    $database->bind(‘:point’, ‘0’);
    $database->bind(‘:credit’, ‘0’);
    $database->bind(‘:bdate’, $bdate);
    $database->bind(‘:banned’, ‘0’);
    $database->execute();

    $database->query = “INSERT INTO member_data (username, registerip, registerlocation) VALUES (:username, :registerip, :registerlocation)”;

    $database->bind(‘:username’, $username);

    //Test if it is a shared client
    if (!empty($_SERVER[‘HTTP_CLIENT_IP’]))
    {
    $registerip=$_SERVER[‘HTTP_CLIENT_IP’];
    //Is it a proxy address
    }
    elseif (!empty($_SERVER[‘HTTP_X_FORWARDED_FOR’]))
    {
    $registerip=$_SERVER[‘HTTP_X_FORWARDED_FOR’];
    }
    else
    {
    $registerip=$_SERVER[‘REMOTE_ADDR’];
    }
    $database->bind(‘:registerip’, $registerip);

    $registerlocation = get_meta_tags(‘http://www.geobytes.com/IpLocator.htm?GetLocation&template=php3.txt&IpAddress=’.$registerip);
    $database->bind(‘:registerlocation’, $registerlocation);

    $database->excute();
    $database->endTransaction();

    $response_array[‘status’] = ‘registersuccess';
    unset($row);

    i have these set in the beginning

    $contact = ”;
    $bdate = ”;

    $response_array = array();
    $username = mysql_real_escape_string($_POST[‘regusername’]);
    $password = mysql_real_escape_string($_POST[‘regpassword’]);
    $confirmpassword = mysql_real_escape_string($_POST[‘regconfirmpassword’]);
    $email = mysql_real_escape_string($_POST[‘regemail’]);
    $contact = mysql_real_escape_string($_POST[‘regcontact’]);
    if(isset($_POST[‘regcontact’]))
    {
    $contact = mysql_real_escape_string($_POST[‘regcontact’]);
    }
    if(isset($_POST[‘regbdate’]))
    {
    $bdate = mysql_real_escape_string($_POST[‘regbdate’]);
    }
    $captcha = mysql_real_escape_string($_POST[‘reg_captcha_code’]);
    unset($_POST);

    • http://culttt.com/ Philip Brown

      That error means you have a mismatch in the number of parameters you have bound, and the number you are using in the query. You are only using :username, :registerip, :registerlocation, but you’ve bound more than that.

      If you remove the bindings on the other parameters, it should work.

    • idiot

      why is it you’re still using mysql_ ? PDO prepare statements will do that kind of stuff.. so dont bother to use mysql_ when ur using PDO :))

      • Idus

        Weiloon’s comment implicitly indicates the use of PDO through Phillip’s helper class. What are you talking about?

  • john

    Best post I’ve found regarding PDO classes. Thank you very much for your time and effort on writing this awesome posts.

    • http://culttt.com/ Philip Brown

      Thanks John :D

      • John

        I work with UTF-8 content (Japanese) and so I found this very important option to add to the options array.

        It took me awhile to sort it out so maybe this will help someone else too :)

        Link: http://stackoverflow.com/questions/584676/how-to-make-pdo-run-set-names-utf8-each-time-i-connect-in-zendframework

        Here is the whole options array. The last one is the one I added:

        // Set options
        $options = array(
        PDO::ATTR_PERSISTENT => true,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::MYSQL_ATTR_INIT_COMMAND => “SET NAMES utf8″
        );

        • http://culttt.com/ Philip Brown

          Ah cool, thanks John! Yeah, I can definitely see that being of use to others

  • Brett

    Best article I have found yet regarding PDO classes. However, I did run into a question. I have a search form with several filters: name, email, zip, etc. I’m not sure how I would bind the values depending on which one value is set. Thanks.

    • http://culttt.com/ Philip Brown

      Thanks Brett! Glad to hear it helped you out!

      You could just pass the filter into a method to execute the required query. For example:

      public function search($filter){
      switch ($filter) {
      case "name"
      // Bind and run query
      break;
      case "email"
      // Bind and run query
      break;
      }
      }

      Or you could even do something fancier where you dynamically replace the bind values and the query string depending on the filter. There’s probably a few ways you could do it. It all depends on your preference and what you think will work best within your project.

      Hope that helps!

      • Brett

        Hi Philip, thanks for getting back to me so quickly. I guess what I’m a bit confused on is how I can append to the query. For example if I want to search by name and zip code: I would have AND name LIKE :userName AND zip = :zip. Would I append and bind each one then run the completed query at the end? Hope what I wrote makes sense, lol. Thanks again.

        • http://culttt.com/ Philip Brown

          Ahh, I see what you mean Brett.

          You could write a function that accepts the raw data, and then automatically forms the query depending on what it was given.

          Something like this:
          $input1 = array("username" => "philipbrown");
          $input2 = array("username" => "philipbrown", "email" => "phil@ipbrown.com");
          public function create_query($input){
          $query = "SELECT * FROM USERS WHERE";
          // Iterate through each item of the input array
          foreach($input as $key => $value){
          $this->bind($key, $value);
          $condition[] = "key = :value";
          }
          return $query . implode("AND", $$condition);
          }

          I’ve just wrote that off the top of my head, so you will probably want to rewrite it a bit. Would that work?

          • Brett

            Thank you so much! I was thinking something like that, but I couldn’t wrap my head around it logically. Thanks again and keep you the great work, really enjoying the site.

          • http://culttt.com/ Philip Brown

            Thanks Brett :D

  • 0Neji

    Really good tutorial, just a quick questions from a PDO noob.

    I’m not 100% sure how the PDO::ATTR_PERSISTENT part works. Say that I define $db = new Database; in my index file but then do the same thing elsewhere, will it return the same connection, create a new one or just fail?

    Also, how should we be using the cancelTransation method? Is there some code we can use to test that everything went okay and if there were errors, then rollBack or if it went okay, endTransaction and commit the changes.

    Sorry if these have been answered – couldn’t see them and the comments list is looong! Thanks for any help.

    • http://culttt.com/ Philip Brown

      If you create a new database object, you will be creating a new connection, so it depends on how you instantiate objects.

      You need to write your own tests to ensure that your database queries ran successfully. The beginTransaction method simply stops the queries from running automatically (see http://www.php.net/manual/en/pdo.begintransaction.php)

      You can then roll them back, or commit them (http://www.php.net/manual/en/pdo.commit.php)

      Have a look at those two links for some good examples. The PHP docs can be a bit overwhelming to begin with. But once you get used to reading them they will be an invaluable resource!

      Let me know if you have any trouble, and feel free to ask any other questions that you have :)

  • Jeremy

    I think you should add a zipfile on your tuts :p

    • http://culttt.com/ Philip Brown

      Haha, all you have to do is copy and paste it into a new file :p

      I think going forward I’m going to create github repos for some of the bigger tutorials. Then readers can just grab a copy of the files from there.

  • http://www.facebook.com/profile.php?id=523341528 Afonso Gomes

    Hey Philip Brown

    I’m having trouble here trying to use this class of yours inside another class to manage users I’m creating. What would be the best way to call it? I have this so far:

    require_once(“../../class.pdo.php”);

    class user {

    private $db;
    private $error;

    public function __construct(){

    try{
    $this->db = new db();
    $db = $this->db;
    }

    catch(PDOException $e){
    $this->error = $e->getMessage();
    }
    }

    public function find_by_email($email){
    $db->query(‘SELECT * FROM users WHERE email = :email LIMIT 1′);
    $db->bind(‘:fname’,$email);
    $row = $db->single();
    return $row;
    }

    }

    But I’m getting a Fatal error: Call to a member function query() on a non-object in

    Could you help me out here? Thanks in advance

    • http://culttt.com/ Philip Brown

      Hi Afonso, it should be $this->db->query(); The find_by_email function has no idea what the $db variable is because it is out of the scope of the method.

      Does that make sense?

  • Radeček

    good, but how can I print example username from mysql. The result contains only array() and foreach not found!

    • http://culttt.com/ Philip Brown

      You have to put the array through the foreach function, for example,
      foreach($results as $result){
      echo $result['username'];
      }

  • Smith

    Hi, thanks for the epic tutorial.
    But i have a small doubt in my mind , Should we manually destroy the pdo object in destruct method or can we simply get away with it since we are using PDO::ATTR_PERSISTENT …

    i’m really new to PDO & PHP.. i apologize if the question is stupid :D

    • http://culttt.com/ Philip Brown

      Hi, I wouldn’t worry about destroying the connection, it won’t be a big deal if you don’t

  • Rocks

    Hi Philip,

    I am having a problem here;

    I have class Database in in database.class.php and I have this function in functions.php where the database.classe.php is included.

    function db_select_states()
    {
    $sql = query(“SELECT * FROM state”);
    $database->execute();
    $rows = $database->resultset();
    return $rows;
    }

    I call this function from index.php like this:

    $states = db_select_states();
    if(is_array($states))
    {
    foreach($states as $row)
    {
    $id = (int) htmlentities($row[‘id’]);

    $statename= htmlentities($row[‘statename’]);
    }

    I don’t where to instantiate the class, I’ve tried everywhere but I keep getting Fatal error: Call to undefined function query() in

    Thanks

    • http://culttt.com/ Philip Brown

      It should be $database->query(); because query(); is a method on the database object.

  • Lucas

    Thank you very much!

    Merci :)

    • http://culttt.com/ Philip Brown

      Glad it helped you Lucas!

  • Koojad

    I want to thank you very much sir for this tutorial. Good luck !

    • http://culttt.com/ Philip Brown

      Thank you :)

  • Wei Loon So

    keep getting mysql server has gone away , anyway to fix?

  • dealtek

    Great post!

    question: example below searches for like ‘J’ – how do I make it search for more than 1 character?

    //SELECT #3 w/Prepared Statement
    $search = “J”;
    $bind = array(
    “:search” => “%$search”
    );
    $results = $db->select(“mytable”, “FName LIKE :search”, $bind);

    • http://culttt.com/ Philip Brown

      Hmm, it depends what you mean. Do you mean “Ja” or “J OR A”?

  • Anton Vroemans

    Where I can download this? :s

    • http://culttt.com/ Philip Brown

      Hi Anton, you are probably best just copy and pasting it from the tutorial. I haven’t made a repo of this.

      • Anton Vroemans

        Ok, thanks anyway

  • rocks

    Hi there, I have like 50 thinks to check so I came up with something like this:

    This is for a search form–

    $to_bind = array();

    $input = $_GET[‘input’];
    $qq = ” SELECT * FROM table where confirm=’0′ “;

    if(!empty($input)) {
    $qq .= “AND title LIKE :input “;
    $to_bind[‘:input’] = $input;
    }

    How do I do this with this class?

    Thanks

    • http://culttt.com/ Philip Brown

      $db = new Database;
      $db->bind(':input', $input);
      $db->query('SELECT * FROM table WHERE confirm=0 AND title LIKE :input');
      var_dump($db->resultset());

      Do you mean like that?

      • rocks

        Thanks for replying, here is what I need to convert to your class.

        $to_bind = array();
        $qq = ” SELECT * FROM classified where confirm=’0′ “;

        if(!empty($input)) {
        $qq .= “AND title LIKE :input “;
        $to_bind[‘:input’] = $input;
        }

        if (!empty($categories) )
        {
        $qq .= “AND id_cat = :categories “;
        $to_bind[‘:categories’] = $categories;
        }

        if (!empty($state) )
        {
        $qq .= “AND id_state = :state “;
        $to_bind[‘:state’] = $state;
        }

        if(!empty($zipcode) ) {
        $qq .= “AND zipcode = :zipcode “;
        $to_bind[‘:zipcode’] = $zipcode;
        }

        if(!empty($price_Min)) {
        $qq .= “AND price >= :price_Min “;
        $to_bind[‘:price_Min’] = $price_Min;
        }

        if(!empty($price_Max)) {
        $qq .= “AND price = :cylin_Min “;
        $to_bind[‘:input’] = $input;
        }

        if(!empty($cylin_Max)) {
        $qq .= “AND cylinder = :year_Min “;
        $to_bind[‘:year_Min’] = $year_Min;
        }

        if(!empty($year_Max)) {
        $qq .= “AND year = :k_Min”;
        $to_bind[‘:k_Min’] = $k_Min;
        }

        if(!empty($k_Max)) {
        $qq .= “AND kilo = :s_Min “;
        $to_bind[‘:s_Min’] = $s_Min;
        }

        if(!empty($s_Max)) {
        $qq .= “AND surface = :r_Min “;
        $to_bind[‘:r_Min’] = $r_Min;
        }

        if(!empty($r_Max)) {
        $qq .= “AND room prepare($qq);

        $qq->execute($to_bind);

        $result = $qq->fetchAll();

        • http://culttt.com/ Philip Brown

          Something like this?

          class build {

          private $query;

          private $bindings;

          public function __construct(){
          $this->query = "";
          $this->bindings = array();
          }

          public function add($placeholder, $input, $query){
          if($this->notEmpty($input)){
          $this->addToQuery($query);
          $this->bind($placeholder, $input);
          }
          }

          private function notEmpty($input){
          if(!empty($input)) return true;
          }

          private function addToQuery($string){
          $this->query .= $string;
          }

          private function bind($placeholder, $input){
          $this->bindings[$placeholder] = $input;
          }

          public function query(){
          return $this->query;
          }

          public function bindings(){
          return $this->bindings;
          }
          }

          $build = new build;
          $build->add('input', $input, 'AND title LIKE :input ');
          $build->add('categories', $categories, 'AND id_cat = :categories ');
          $db = new database;
          $db->query("SELECT * FROM classified where confirm='0'" . $build->query() . "ORDER BY date DESC");
          foreach($build->bindings() as $key $value){
          $db->bind($key, $value);
          }
          var_dump($db->resultset());

          You could probably refactor this so it works better within the rest of your code. Also you might be better off using an ORM>

          • rocks

            It started coming all together, thanks for your help.

            I might have some issues adding pagination, but I will try it my own, and if I couldn’t I ask for help.
            Thanks

          • http://culttt.com/ Philip Brown

            No problem :)

            Have a look at Laravel for an idea of how things like pagination work in popular and well used frameworks :)

            Often it’s better to just use the same well used patterns!

  • paul

    Nice tutorial Phillip :) good work! What benefits are there of creating the database handler opposed to just extending this class from PDO?

    • http://culttt.com/ Philip Brown

      Thanks Paul!

      I’m not 100% sure, but I got told that I should create a wrapper rather than extending PDO.

      • paul

        Awh thats cool, i suppose this keeps the PDO object in a local scope in your database class, allowing you greater flexibility in your db class, and by mapping the functions against the PDO object like you have done makes its a great way to learn PDO, good work :)

        • http://culttt.com/ Philip Brown

          Thanks Paul :)

  • http://www.cihanblog.com/ Cihan Küsmez

    Thank you very much. I am at beginner stage. But it is very easy to understand this article.

  • AJB

    Excellent tutorial, thank you for this. I just wanted to note that cutting-and-pasting from this page seems to include a whack of invisible characters that will throw PHP syntax errors.

    Took me a couple hours to figure out what was happening. So, a download might be helpful in the future.

    Thanks again.

    • http://culttt.com/ Philip Brown

      Yeah I was thinking about going back through my tutorials and making Github repos for them. Hopefully if I get some time soon I can do it

  • Nader Minaie

    hi
    this tut was one of the best , i searched the hole internet and made my own db class, but your class give me more idea’s to write better code :) i will soon share my codes with you and think it will be very usefull

    • http://culttt.com/ Philip Brown

      Thank you sir, I’m glad it helped you :)

  • james

    Hi, thank you for nice tut :)

    I have a problem with catching the exceptions of the queries. I use your functions and if I type the wrong query, it gives me an error like this: “uncaught exception ‘pdoexception'”. How can I write valid functions with exceptions handling?

    • http://culttt.com/ Philip Brown

      Thank you :)

      You just need to “catch” the exception. Have a read of this http://php.net/manual/en/language.exceptions.php

      • james

        Thanks for reply. Whether this function will be good?

        public function query($query){
        try {
        $this->stmt = $this->dbh->query($query);
        } catch (PDOException $e) {
        $this->error = $e->getMessage();
        }
        }

        • http://culttt.com/ Philip Brown

          Yeah, something like that. I tend to think these things depend on the bigger picture of your project, but that should be good for a start.

          • james

            Thank you Philip :)

          • http://culttt.com/ Philip Brown

            No problem :)

  • ociugi

    I’m wondering, why did you assign constant variable (DB_HOST; DB_USER; DB_PASS; DB_NAME;) then the constant variable assigned to regular variable($host; $user; $pass; $dbname)?
    What is the avantage of doing this instead of using the constant variable directly?
    .

    • http://culttt.com/ Philip Brown

      I only did it like that to because you would normally define those config items once in your application and then make a reference to them.

  • Lor

    thank you very much for the great tutorial. I would like to ask you why making this change the code continues to work fine:
    public function resultset(){
    //$this->execute(); stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    Excuse my english.

    • http://culttt.com/ Philip Brown

      Hi Lor, I would take a look through the PHP docs for some examples that might describe how each component specifically works http://php.net/manual/en/pdostatement.fetchall.php

      • Lor

        Sorry Philip I was using your class in a wrong way.
        I was calling the execute method after binding data.

        $db_conn->beginTransaction();
        $db_conn->query_prepare(‘INSERT INTO …) VALUES (…)’);
        $db_conn->bind(‘:name’, $val);
        $db_conn->execute(); // endTransaction();

        • http://culttt.com/ Philip Brown

          Hmm, I’m not sure. Throw your code up on Github and I’ll take a look

  • Wei Loon So

    any idea to solve this mysql server has gone away?

    Warning: PDO::__construct(): MySQL server has gone away in C:wampwwwutilitiesdb.php on line 26

  • Sakil Imran

    Finally I got an excellent post on PDO. Thanks a lot for your great effort Philip Brown.

    • http://culttt.com/ Philip Brown

      Thank you sir :)

  • Zeffer

    Hi I’m having some problems with this class when I bind to the query it will call an error but if I hand code the query not using the bind function it works.

    $database = new Database();
    $database->query(“SELECT * FROM users WHERE userID = :Userid “);
    $database->bind(‘:Userid’,37);

    $row = $database->single();

    print_r($row);

    // Working
    $database = new Database();
    $database->query(“SELECT * FROM users WHERE userID =’37’ ” );
    // $database->bind(‘:Userid’,37);

    $row = $database->single();

    print_r($row);

    // Bind Function

    public function bind($param, $value, $type = null){
    if (is_null($type)) {
    switch (true) {
    case is_int($value):
    $type = PDO::PARAM_INT;
    break;
    case is_bool($value):
    $type = PDO::PARAM_BOOL;
    break;
    case is_null($value):
    $type = PDO::PARAM_NULL;
    break;
    default:
    $type = PDO::PARAM_STR;
    }
    }
    $this->stmt->bindValue($param, $value, $type);
    }

    • http://culttt.com/ Philip Brown

      What is the error?

  • jerome vidal

    Hi,
    Little issue with BindValue…
    Have $param=':user_name';
    $value=’test';
    If i try to watch what kind of PDO type is in $type it always screen ‘2’=> int(2)
    But it’s STR… What’s the problem?

    • http://culttt.com/ Philip Brown

      Hmm, throw your code up on GitHub and I’ll take a look

  • Bruno de Almeida

    Congratulations on your great explanation.

    I learned a lot from your tutorial, it is a very important tool for my learning.

    I just have one question:

    This result ( echo $database->endTransaction(); ) returns a number, here 21 returned ok.

    What does that mean?

    Thank you!

    • http://culttt.com/ Philip Brown

      Thank you sir, glad it helped :D

      Hmm, I’m not sure, it should return true or false. Take a look at the docs, for a more in-depth explanation :) http://www.php.net/manual/en/pdo.commit.php

  • scadoo

    Excellent tutorial Thanks

    I’m just having a little trouble with bind values for LIMIT and OFFSET

    the code is

    $db->query(“SELECT * FROM table WHERE O = :new ORDER BY Order_Date DESC LIMIT :perpage OFFSET :move”);
    $db->bind(‘:new’, $new);
    $db->bind(‘:perpage’, (int)$perpage);
    $db->bind(‘:move’, (int)$offset);
    $table = $db->many();

    its returning an empty value

    if I remove the (int) i get a PDO Exception

    if I remove the binds and replace the :perpage and :move in the sql with numbers it works

    Any Thoughts?

    • http://culttt.com/ Philip Brown

      If you aren’t accepting those values from the user I would just use numbers.

      • scadoo

        the values are used to paginate the result so need to be dynamic

        • http://culttt.com/ Philip Brown

          Hmm, what exception are you getting? It should just work without the (int)

          • scadoo

            Sorry. Just var dumped all the the variables and found per page was always passing as null. So my mistake. Works perfectly now its corrected binding the variable without assigning the (int). Thank you for taking the time to respond

          • http://culttt.com/ Philip Brown

            No problem! Glad you got it sorted :)

  • Jordy

    Thanks for this amazing tutorial. I have one question. Sometimes I execute two query’s and at the end of these two I check if the numRows are > 0. Something like this:

    $query_1 = “SELECT Something FROM table”;
    $result_1 = $db->query($query_1);
    $query_2 = “SELECT Something FROM table”;
    $result_2 = $db->query($query_2);
    if ($result_1->num_rows > 0 && $result_2->num_rows > 0){
    //do something
    }

    But in your script I can only check that for the last executed query. How can I rewrite that?

    • http://culttt.com/ Philip Brown

      Thanks, glad it helped you :)

      You could just count the number of rows in each result:
      if(count($result_1) > 0 && count($result_2) > 0)

      • Jordy

        $result_1 always returns NULL

        • http://culttt.com/ Philip Brown

          Because of an error? Or because there are no results?

          • Jordy

            There are no results. This is my script:

            $result = $db->query(“SELECT * FROM general”);

            var_dump($result);

            The var_dump returns NULL, always.

          • http://culttt.com/ Philip Brown

            I don’t understand? Is the query correct? Are there results in the database?

          • Jordy

            The query is correct, and the results are in the database. The problem is that the query function doesn’t return anything. I found a solution for it. I made 4 functions (select, update, insert and delete) and all of them returns a value (select returns fetchAll rows, update the rowCount, insert the lastInsertId and delete also the rowCount).

          • http://culttt.com/ Philip Brown

            Ah I see. Well, glad you got it sorted :)

  • Rotelando

    Thank you very very much. Its really worth the patience. :)

    • http://culttt.com/ Philip Brown

      No problem :) glad it helped you!

  • shibin

    i got an error like this.
    Fatal error: Call to a member function prepare() on a non-object

    public function query($query){

    $this->stmt = $this->dbh->prepare($query);

    }
    whats wroing in this???

  • shibin

    Fatal error: Call to a member function prepare() on a non-object in database.php line 52

    line 52 is

    public function query($query){

    $this->stmt = $this->dbh->prepare($query);

    }

    in my view page this is my code

    include ‘database.php';

    $database = new Database();

    //$sql = “SELECT * FROM batches”;

    $database->query(‘SELECT * FROM batches WHERE course_id = :lname’);

    $database->bind(‘:lname’, ‘1’);

    $rows = $database->resultset();

    echo $database->rowCount();

    echo “”; print_r($rows); echo “”;

    whats wrong wiith this??????

    • http://culttt.com/ Philip Brown

      It means the PDO object $this->dbh is not being created properly. Make sure you have the PDO extension loaded correctly.

      • Jesse Harlan

        I am having the same issue but for me I seem to have pdo loaded without issues. it works on my test server but not on my production server. phpinfo() on the production server is showing mysql pdo extensions loaded just fine but im still getting the same error

        • Jesse Harlan

          scratch that. turns out it throws the same error when connecting with bad connection info. im all good now :)

          • http://culttt.com/ Philip Brown

            Glad you got it sorted Jesse :)

  • persona

    Your class recieved some criticism on SO http://stackoverflow.com/questions/18679448/pdo-class-is-this-technically-correct/18679947
    Thought maybe you wanted to know about this.

    • http://culttt.com/ Philip Brown

      I wrote this post a long time ago. Even I wouldn’t write it the same way now.

  • idiot

    really amazing ! =) thanks a lot author :)) . i just copy whole of the content of the page , so that if the server willl down (hope not) i have my own copy. thanks again :))

    • http://culttt.com/ Philip Brown

      haha thanks. Don’t worry, Culttt isn’t going anywhere haha

  • Art2

    I like this tutorial. There is one thing however, which you leave a bit too vague. First, you try catch the errors only in constructor and leave the other functions without error handling. You also dont return the errors in any part – now they are just initialized in error variable. Of course it’s up to the ones preferences how to handle and display the errors, but this could be worth mentioning in the article.

    • http://culttt.com/ Philip Brown

      Yeah error handling is very vague in this example.

      To be honest, I think if I were to write this from scratch today I would probably change quite a bit to make it easier to use and understand.

      • ukenpachi

        Hi Philip, It will be useful if you did this again in away you will do it yourself because as Art2 and Guest pointed out this code has too much limitations and a few of your follows are using this codes in live websites. Something like this needs to be updated once in a while and also to be done in the right way. People need to learn to do it in the right way not a simple way because simple is not always right.

        • http://culttt.com/ Philip Brown

          Yeah I might look into writing a more update tutorial on working directly with PDO. It would be impossible for me to go back and keep every tutorial up to date, so it’s really up to the readers judgement of the code and the date that it was posted whether they want to use it or not. I wouldn’t advise using code in production if you don’t fully understand what’s going on though.

  • Guest

    kanske det inte behöver vara förknippat med så mycket risk

    I like your idea but this class actually limit the powerful use of PDO, one example is when using the Database::query method, the statement will be set in your class’s private $stmt variable and this will limit you to have several prepared statements at once because this method doesn’t return anything to set in an own variable.

    And if you look futher in the class, all the functions actually do exactly the same as the original PDO methods, but some with different names. PDO already is very easy to use.

    It’s not wrong to build own database functions or simplify the usage, but that functions should do something over the functionallity of the original methods and the core (in this case PDO) should not be bound to the new class, like build a connection in the constructor or be extended.

    Why? If you use this class type of structure, you have to declare a “call-method” for every method you want to use from PDO in you own class OR you can call it directly from PDO by setting up the standard usage of it, BUT what if you already have done the PDO setup in your own class constructor? You got multiple connections.

    If you choose another structure where you extend PDO to your own class, your own methods can be in conflict if PDO gets methods with same name in the next version/update. Then you may be have a whole system which using this method-names, but associated to your own methods. If you would extend PDO and use method names with a special prefix, it may not need to be associated with so much risk, but you can never be 100% percent safe ;)

    What i’m trying to say is that it exists a lot of PDO wrappers out there, but why wrap a class which already is so easy to use when the wrapper just do the same? If you want to make a wrapper, do something that minimize the write of SQL questions (ORM) but NOT limit any use of the default methods.

    I seem negative ? Much work in programming but we have to do everything in a very broad perspective. A bit like writing a book, starting from the end. Good luck!

  • Guest

    I like your idea but this class actually limit the powerful use of PDO, one example is when using the Database::query method, the statement will be set in your class’s private $stmt variable and this will limit you to have several prepared statements at once because this method doesn’t return anything to set in an own variable.

    And if you look futher in the class, all the functions actually do exactly the same as the original PDO methods, but some with different names. PDO already is very easy to use.

    It’s not wrong to build own database functions or simplify the usage, but that functions should do something over the functionallity of the original methods and the core (in this case PDO) should not be bound to the new class, like build a connection in the constructor or be extended.

    Why? If you use this class type of structure, you have to declare a “call-method” for every method you want to use from PDO in you own class OR you can call it directly from PDO by setting up the standard usage of it, BUT what if you already have done the PDO setup in your own class constructor? You got multiple connections.

    If you choose another structure where you extend PDO to your own class, your own methods can be in conflict if PDO gets methods with same name in the next version/update. Then you may be have a whole system which using this method-names, but associated to your own methods. If you would extend PDO and use method names with a special prefix, it may not need to be associated with so much risk, but you can never be 100% percent safe ;)

    What i’m trying to say is that it exists a lot of PDO wrappers out there, but why wrap a class which already is so easy to use when the wrapper just do the same? If you want to make a wrapper, do something that minimize the write of SQL questions (ORM) but NOT limit any use of the default methods.

    I seem negative ? Much work in programming but we have to do everything in a very broad perspective. A bit like writing a book, starting from the end. Good luck!

  • Guest

    I like your idea but this class actually limit the powerful use of PDO, one example is when using the Database::query method, the statement will be set in your class’s private $stmt variable and this will limit you to have several prepared statements at once because this method doesn’t return anything to set in an own variable.

    And if you look futher in the class, all the functions actually do exactly the same as the original PDO methods, but some with different names and shortcuts. PDO already is very easy to use.

    It’s not wrong to build own database functions or simplify the usage, but that functions should do something over the functionallity of the original methods and the core (in this case PDO) should not be bound to the new class, like build a connection in the constructor or be extended.

    Why? If you use this class type of structure, you have to declare a “call-method” for every method you want to use from PDO in you own class OR you can call it directly from PDO by setting up the standard usage of it, BUT what if you already have done the PDO setup in your own class constructor? You got multiple connections.

    If you choose another structure where you extend PDO to your own class, your own methods can be in conflict if PDO gets methods with same name in the next version/update. Then you may be have a whole system which using this method-names, but associated to your own methods. If you would extend PDO and use method names with a special prefix, it may not need to be associated with so much risk, but you can never be 100% percent safe ;)

    What i’m trying to say is that it exists a lot of PDO wrappers out there, but why wrap a class which already is so easy to use when the wrapper just do the same? If you want to make a wrapper, do something that minimize the write of SQL questions (ORM) but NOT limit any use of the default methods.

    I seem negative ? Much work in programming but we have to do everything in a very broad perspective. A bit like writing a book, starting from the end. Good luck!

  • Andreas

    I like your idea but this class actually limit the powerful use of PDO, one example is when using the Database::query method, the statement will be set in your class’s private $stmt variable and this will limit you to have several prepared statements at once because this method doesn’t return anything to set in an own variable.

    And if you look futher in the class, all the functions actually do exactly the same as the original PDO methods, but some with different names and shortcuts. PDO already is very easy to use.

    It’s not wrong to build own database functions or simplify the usage, but that functions should do something over the functionallity of the original methods and the core (in this case PDO) should not be bound to the new class, like build a connection in the constructor or be extended.

    Why? If you use this class’s type of structure, you have to declare a “call-method” for every method you want to use from PDO in you own class OR you can call it directly from PDO by setting up the standard usage of it, BUT what if you already have done the PDO setup in your own class constructor? You got multiple connections.

    If you choose another structure where you extend PDO to your own class, your own methods can be in conflict if PDO gets methods with same name in the next version/update. Then you may be have a whole system which using this method-names, but associated to your own methods. If you would extend PDO and use method names with a special prefix, it may not need to be associated with so much risk, but you can never be 100% percent safe ;)

    What i’m trying to say is that it exists a lot of PDO wrappers out there, but why wrap a class which already is so easy to use when the wrapper just do the same? If you want to make a wrapper, do something that minimize the write of SQL questions (ORM) but NOT limit any use of the default methods.I seem negative ? Much work in programming but we have to do everything in a very broad perspective. A bit like writing a book, starting from the end. Good luck!Leave a message…

    • http://culttt.com/ Philip Brown

      Hi Andreas, thank you for your comment :)

      I actually agree with 100% of the things you say! To be honest, I wrote this a long time ago now, so even I wouldn’t do it like this any more.

      Hopefully this tutorial is a not so scary introduction to PDO for total newbies, but yes I totally agree with what you’re saying :)

      • Andreas

        I actually saw after that I posted my comment it was a time ago since you wrote this toturial. It isn’t supposed to sit and explain things to you as you probably already know :p Tip! Mark in the beginning of the post that is was a long time ago you did this and that there are some limitations in the use so people know :)

        • http://culttt.com/ Philip Brown

          Haha, no way, you can read my mind! I’m going to add a notification to the top of code posts that have been published for a certain amount of time, I just haven’t got around to it yet! :)

  • steve

    Great article.

    Its works perfectly on my testing server but I’m getting a fatal error

    Call to a member function prepare() on a non object on

    public function query($query){$this->stmt = $this->dbh->prepare($query);}

    • http://culttt.com/ Philip Brown

      Hmm, are you instantiating the Database object correctly?

      • steve

        __construct is the same and I’m using settings that work with msqli on both testing and production servers

        • http://culttt.com/ Philip Brown

          What do you get when you dump out $this->dbh?

  • Idus

    Good article, and obviously well received. People like you, who contribute to a vibrant open source community, are the principle ingredient in the success of languages like PHP. For this, I thank you :)

    Your class is very tidy and intuitive example of a function package. A few constructive comments, if I may.

    Re: your bind() method which wraps PDO’s native bind(). You increase the risk of SQL injection by ‘Automagically’ setting the parameter type (with your switch) when calling PDO’s bind() method. You are nullifying the effort of specifying the parameter datatype (PDO::PARAM_NAME), so you may as well skip it. I will give you points for considering an optional datatype argument in your wrapper method, but it still encourages lazy and less secure coding. Personally I’d force the specification in your method.

    Can you include an example wrapper for bindParam()? While PDO’s bindValue() is a bit more intuitive, it lacks the flexibility of bindParam() when iterating through prepared statements. Passing values by reference greatly improve the legibility of iterative statements and fosters cleaner code. We are working with databases, after all, and iteration is the engine which empowers dynamically driven applications. It also promotes object oriented design by getting people familiar with memory references.

    New coders should also be made to understand that this isn’t an object oriented approach nor is it an abstraction. This is an excellent example of loosely encapsulating an existing and well documented class (PDO) for housekeeping purposes.

    Thanks again for your contribution,
    Idus

    • http://culttt.com/ Philip Brown

      Hey, thanks for your comment :) I totally agree with your suggestions. To be honest I wrote this a long time ago, so even I wouldn’t do it like this anymore. I’ve only really left this up as it seems to be an easy introduction to the concept of PDO, but as you suggest, it could be improved a great deal.

  • YuriKolovsky

    I love to write wrappers for native classes and I think your post is very educational, but
    there is a good reason as to why PDO::prepare returns a PDOStatement instead of returning PDO with a set query, this prevents the db handle variable from being mutable and also allows us to prepare multiple statements before executing them. While your class seems to remove this possibility entirely without a good reason.

    I also made a wrapper for PDO long ago that I called PDO2 without much originality, to allow using the same named bound param in multiple places across the sql string.

    Overall good job on the article.

    • http://culttt.com/ Philip Brown

      Thank you :)

  • Danny Corder

    Thank you, found this genuinely useful building on some previously viewed tutorials introducing me to OOP and the need for a database class.

    • http://culttt.com/ Philip Brown

      Thanks Danny :) Glad you found this useful!

  • kc

    Great tutorial and introduction into PDO.

    As you mentioned you wrote this a long time ago, how would you go about doing this today?

    • http://culttt.com/ Philip Brown

      I would use an ORM. There are many different PHP ORM packages available where you can simply drop them in to an existing PHP project.

      If you are new to ORMs, take a look at this article http://culttt.com/2013/02/11/what-is-object-relational-mapping/

      • kc

        Fantastic, thanks for the link and pointer.

        • http://culttt.com/ Philip Brown

          No problem :)

  • Mathieu Decaffmeyer

    This was really helpful, thank you so much. I have a question though:
    Wouldn’t it be better to make the connection in the “query” method instead of the constructor ?
    This way, the connection is instantiated only when needed. Whereas if you create the connection in the constructor, you will make a connection to the database even in the case when you don’t need it.
    For example:

    public function query($query) {
    if(is_null($this->dbh)) $this->dbh = $this->connect();
    $this->stmt = $this->dbh->prepare($query);
    }
    public function connect() {
    // some code
    $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
    // some code
    }

    • http://culttt.com/ Philip Brown

      Hmm, I’m not sure. You would only need the object when you actually needed the connection.

  • Response to your Stupid Commen

    It’s 2013 and I still find your tutorial very helpful and straight-forward. Thank you!

    • http://culttt.com/ Philip Brown

      That’s awesome! Glad it helped you out :D

  • Chris Jones

    This post has made my day. Thanks Philip for the very simple and straightforward introduction to PDO. I’ve been fighting mysqli for hours, and this post has answered all the problems I’ve had so far. Well done.

    • http://culttt.com/ Philip Brown

      Thanks Chris! Glad it helped you out! Once you go PDO, you never go back :)

  • http://mo7sin.com/ Muhammad Mohsen

    That’s Awesome tutorial, But i’m wondering where is “bind” came from?
    i know
    bindColumn
    bindParam
    bindValue

    could you just explain to me please? Thanks :)

    • http://culttt.com/ Philip Brown

      Thank you Muhammad :)

      I’m sorry, I’m not sure what you mean?

      • http://mo7sin.com/ Muhammad Mohsen

        I mean is there any method called bind? i know only bindColumn, bindParam and bindValue .. got it? :)

        • http://culttt.com/ Philip Brown

          Ah right, no it’s just an arbitrary name that I chose. You are right in thinking it is not on this (http://php.net/manual/en/class.pdostatement.php) class.

          • http://mo7sin.com/ Muhammad Mohsen

            Aha, my bad :D, i was following the code very carefully until i thought it was a new method.

  • Abaddon Ormuz

    Hi Philip,

    I just create a new method to bind values from an
    array, but I’m pretty new PHP and PDO, so I hope you can tell me if this
    is correct and/or if there’s a better way to do it, I used your bind
    method as example.

    $param_array = array(
    ‘:title’ => ‘Some value’,
    ‘:data’ => ‘Another value here’
    );

    public function bind_all($param_array) {
    array_map(array($this, ‘bind’), array_keys($param_array), array_values($param_array));
    }

    Is it correct? Is it safe and? is there other way to do it if this isn’t the best method?

    Regards.

    • http://culttt.com/ Philip Brown

      Yeah that looks pretty good to me. Does it work correctly for you? And yeah, it would be totally safe because you are passing the values into the bind method. Nice work :)

  • Dale

    Awesome post! Will definitely be recommending this to others!

    • http://culttt.com/ Philip Brown

      Thank you Dale :)

  • Michael Stelly

    Well, I tried your code verbatim. Can’t get the db connect working. My web service returns
    xhr error: status – 500, responseText – null

    My straight call to the db works fine, like this
    $dbh = new PDO(‘mysql:host=localhost;dbname=kpl’, ‘root’, ‘root’);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    Was hoping to get this working because I want to encapsulate a number of queries for a reporting dashboard.

  • redfro

    this is awesome, very helpful. Thanks

    • http://culttt.com/ Philip Brown

      No problem, glad it helped you!

  • Ben

    Hello and thank you for your excellent class abstraction.

    Off, I created a fetchObject method in this class, then I read the results with a loop (while), so in this loop, I redo call to fetchObject method, I lose my first statement. How can I do to fix this?

    Thanking you in advance.

    PS: sorry for my english by google translation

    • http://culttt.com/ Philip Brown

      Do you have a code example?

      • Ben

        namespace db;

        /**

        * Db class

        */

        class Db {

        private $driver;

        public function __construct($driver) {

        $this->driver = $driver;

        }

        public function query($query){

        return $this->driver->query($query);

        }

        public function prepare($query){

        return $this->driver->prepare($query);

        }

        public function execute($param = null){

        return $this->driver->execute($param);

        }

        public function fetchAll(){

        return $this->driver->fetchAll();

        }

        public function fetchAssoc(){

        return $this->driver->fetchAssoc();

        }

        public function fetchObject($stmt) {

        return $this->driver->fetchObject($stmt);

        }

        }

        /**
        * Driver Class
        */
        namespace dbdriver;
        Use PDO;

        class PdoMysqlDriver {

        private $_stmt;
        public $_dbh;
        private $_query = ”;

        public function __construct() {

        $options = array(/*PDO OPTIONS*/);

        try {

        $dsn = ‘mysql:host=’ . PDO_HOST . ‘;dbname=’ . PDO_DBNAME;
        $this->_dbh = new PDO($dsn, PDO_USER, PDO_PASS, $options);

        return $this;

        } catch(PDOException $e) {

        echo $e->getMessage();

        }

        }

        public function query($query){

        $this->_query = $query;
        $this->_stmt = $this->_dbh->query($this->_query);
        return $this->_stmt;

        }

        public function prepare($query){

        $this->_query = $query;
        $this->_stmt = $this->_dbh->prepare($this->_query);
        return $this;

        }

        public function execute($params = null){

        if(is_array($params)) {

        $this->bind($params);

        }

        $this->_stmt->execute();
        return $this->rowCount();

        }

        public function bind(){/*Bind function*/}

        public function rowCount(){

        return $this->_stmt->rowCount();

        }

        public function fetchAll(){

        return $this->_stmt->fetchAll(PDO::FETCH_ASSOC);

        }

        public function fetchAssoc(){

        return $this->_stmt->fetch(PDO::FETCH_ASSOC);

        }

        public function fetchObject() {

        return $this->_stmt->fetch(PDO::FETCH_LAZY);

        }

        }

        /**
        * Exemple
        */

        define(‘DB_TYPE’,’mysql’);//odbc mysql

        /**
        * Select driver type connexion
        */

        $type = strtolower(ucfirst(DB_TYPE));
        $driver = ‘Pdo’ . $type . ‘Driver';
        $class = “\db\Driver\$driver”;

        $base = new dbDb(new $class);

        $query1 = “SELECT * FROM table WHERE …”;

        $sth_one = $base->query($query1);

        while($obj = $sth_one->fetchObject()){

        $query2 = “SELECT * FROM table WHERE …”;

        $sth_two = $base->query($query2);

        /*
        * Now, the first statement is empty!
        */

        $obj2 = $sth_two->fetchObject();

        }

        Thank you.

  • Emily

    Thank you!!! This is a best post! I really needed an explanation like this! Just one question, this form does not use the singleton pattern, right?

    • http://culttt.com/ Philip Brown

      No, that is correct!

      Glad it helped you out! :)

  • ukenpachi

    I know this is about 2 years old what are the improvements that you suggest can be made to your beautiful code? if any.

  • http://paul-m-jones.com/ Paul M. Jones

    Hey, this looks a lot like Aura.Sql v2! https://github.com/auraphp.Aura.Sql

    • http://harikt.com/ Hari K T

      Nice catch Paul.

      • http://culttt.com/ Philip Brown

        That looks much better, I’d use that instead

    • F Cyrus

      It’s quite unfair to say that…

  • Arthur

    Hi Philip, add public function __destruct(){and null connection here}

    • http://culttt.com/ Philip Brown

      Yeah that’s a good suggestion, but I think you can get away without it in PHP

  • Ahmad Rio

    thanks bro :) … its the best simple class PDO Statement … thanks very much :D

    • http://culttt.com/ Philip Brown

      No problem :)

      • Ahmad Rio

        Hello bro,
        sorry bro, i little english language.
        now, i get some problem on fetch() and fetchAll().

        in function:

        function allData() {
        $db = new connection();
        $db->query(“SELECT * FROM tb_admin ORDER BY id_admin DESC LIMIT 5″);
        $db->execute();
        return $this->db->fetch();
        }

        and i call this function:

        $allData = allData();
        foreach($allData as $k) {
        echo $k[‘name_admin’];
        }

        i reload and i get error.
        please bro, help me .. :D
        thankz ..

        • http://culttt.com/ Philip Brown

          What error are you getting?

          • http://www.indoaspacmicro.com Ahmad Rio

            this error :
            Warning: Illegal string offset ‘nama_kategori’ ..
            what is mean mr ?

          • http://culttt.com/ Philip Brown

            That means the key nama_kategori is not present in the array. Check to see if you’ve spelt everything correctly and that you’ve selected the right fields from the database.

          • http://www.indoaspacmicro.com Ahmad Rio

            Oh ok mr, I see and work. thanks a lot for your response.

          • http://culttt.com/ Philip Brown

            No problem Ahmad :)

  • br0ther

    Thanks a bunch Philip! This was just what I needed to kickstart and get things going.
    Is it ok to include the database class in a AGPL project? I’ll add a notice with your name and a link back here in that case ofc.

    • http://culttt.com/ Philip Brown

      No problem :)

      Absolutely. No need to include attribution, you can use this to do anything you like :)

  • Erikas

    Awesome, that is best thing i can found in internet about PDO :)
    time to drop mysql, i heard that php drops mysql support in 5.5 ? its true?

    • http://culttt.com/ Philip Brown

      Hmm, perhaps the old methods for connection, but not MySQL all together.

      But yeah, PDO is definitely the way to go :)

  • cj

    Thanks for the great share. I am having trouble with bind method
    “Caught exception: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

    $this->db->query(‘SELECT 1 as ctr FROM users_directory WHERE usernam_e = :username and passwor_d = :passwor_d’);
    $this->db->bind(‘:username’, $usernam_e);
    $this->db->bind(‘:password’, $passwor_d);
    $row = $this->db->single();
    return $row[‘ctr’];

    But if i use direct query
    $this->db->query(“SELECT 1 as ctr FROM users_directory WHERE usernam_e = ‘cj@email.com’ and passwor_d = ‘password'”) its working perfectly.

    Any suggestion?

    • http://culttt.com/ Philip Brown

      You’ve used :password_d in your query, but the parameter is :password.

  • arun

    HAI… I hav a doubt. How could I close the database connection in this code??

    • http://culttt.com/ Philip Brown

      Hey, I wouldn’t worry about, PHP will automatically close it for you.

  • Saad Berrada

    i put the code as you wrote but it seems not working

    • http://culttt.com/ Philip Brown

      You’ve made a mistake where you are binding the parameters.

  • http://abbe98.github.io/ Albin Larsson

    What about SQL Injections if you do not Bind values?

    For example:
    $database->query(“SELECT * FROM `users` WHERE `username` = ‘$username'”);

    Is this secure?

    • http://culttt.com/ Philip Brown

      No, that’s why you bind the values.

  • Saad Berrada

    hi thank you for pdo class, i want to display records from database by using this class, this my code to this

    query(“select poste,client,type_contrat,desc_annonce from ‘annonce’ “)->fetchAll(); ?>

    xxxx

    Voir plus

    i got this message ” Fatal error: Call to a member function fetchAll() on a non-object in C:wampwwwCVthequeViewannonces.php on line 4″, the fourth line is the line of the query

    could you help me ?

    • http://culttt.com/ Philip Brown

      You need to call resultset() rather than chaining fetchAll() to the query() method. Have another read through that section of the tutorial.

  • Guest

    i use these script for select row by ID taken from the URL
    query(“select * from annonce limit 20 where id”.$_GET[‘code’]);
    $row = $c->single();
    }
    ?>

    back

    |
    |

  • Guest

    i use these method for select row by ID taken from the URL

    query(“select * from annonce limit 20 where id”.$_GET[‘code’]);

    $row = $c->single();

    }
    ?>

    back

    |

    |

  • Saad Berrada

    i use these method for select row by ID taken from the URL

    query(“select * from annonce where id” . $_GET[‘code’]);

    $row = $c->single();

    }

    ?>

    back

    |

    |

  • Saad Berrada

    query(“select * from annonce where id=”.$_GET[‘code’]);
    $row = $c->single();
    } ?>

    back

    |
    |

  • Saad Berrada

    i use these code for select and display row by ID taken from the URL

    query(“select * from annonce where id=”.$_GET[‘code’]);
    $row = $c->single();
    } ?>

    back

    |
    |

  • Uhelliton Andrade

    Thank you my friend, for this great post. May God bless you greatly, giving him wisdom and knowledge every day.

    • http://culttt.com/ Philip Brown

      No problem :) Glad you found it useful! :D

  • Maoelana Noermoehammad

    thank you Philip. god Job. very good article.
    /www.kartatopia.com

  • Maoelana Noermoehammad

    This is really help me. Good article. thank you.
    http://www.kartatopia.com

  • F Cyrus

    Thanks for this useful tutorial… I need to go through this class again to understand it better :)
    Cheers

    • http://culttt.com/ Philip Brown

      Yeah I usually find myself going over new things again and again until it clicks :D

  • brian

    PHP Fatal error: Call to a member function prepare() on a non-object in /my/path/to/tutorial/database.class.php on line 33

    32. public function query($query) {
    33. $this->stmt = $this->dbh->prepare($query);
    34. }

    Any ideas?

    • Brian

      Solved. I was including the port number when defining the DB_HOST.

      • http://culttt.com/ Philip Brown

        Good stuff Brian, glad you got it sorted! :)

  • Denny Dzulkarnaen

    Thanks for the tutorials. May you be blessed with all the good things life has to offer.

    • http://culttt.com/ Philip Brown

      Haha, thank you Denny :)

  • Antonio Angel

    I congratulate you on having written this great post! I have not only learnt how to correctly use PDO, I have even learnt how to start using PHP the object oriented way. So I thank you for your post and your very easy to understand way of explaining.

    • http://culttt.com/ Philip Brown

      Thanks Antonio :) Glad it helped you out!

  • muratbastas

    Thx very much..

    • http://culttt.com/ Philip Brown

      No problem :)

  • Saad Berrada

    i use a query to select items but it’s seems not working
    here is mys code:

    $fonc = $_POST[‘fonc’];
    $nex = $_POST[‘nex’];
    $dispo = $_POST[‘dispo’];
    $tech = $_POST[‘tech’];
    $c = new Connexion();
    $c->query(“select nom_complet,fonction,email,nbre_exp,dispo,tech from candidat ”
    . “where fonction like :fonc ‘%’ and nbre_exp like :nexp ‘%’ and dispo like :dispo ‘%’ and tech like :tech ‘%'”);

    $c->bind(‘:fonc’, ‘$fonc’);/

    $c->bind(‘:nexp’, ‘$nex’);

    $c->bind(‘:dispo’, ‘$dispo’);

    $c->bind(‘:tech’, ‘$tech’);/

    $rows = $c->resultset();

    • http://culttt.com/ Philip Brown

      I don’t think your SQL is correct. Make sure it works in phpMyAdmin before trying to write a PDO query

  • http://www.arafath.me/ Ärafath Mohamed

    how to bind all result to HTML table?

  • Samy Ramesh, Chennai

    Awesome, Philip. Quite explanatory, Great article on PHP PDO abstraction layer. I’m going to use it for sure.

    • http://culttt.com/ Philip Brown

      Thanks Samy :)

      • Guest

        thanks (Y)

  • tj_gumis

    Thank you.

    • http://culttt.com/ Philip Brown

      No problem :) Glad you found it useful!

Supported by