Extending PDO class

lauthiamkok · March 3, 2011, 12:52am

Hi,

Below is the db connection class I came out with so far, but I am going to improve it by extending the PDO class itself,

<?php
    class database
    {
    	protected $connection = null;
    	
    	#make a connection
    	public function __construct($hostname,$dbname,$username,$password)
    	{
    		try
    		{
    			# MySQL with PDO_MYSQL
    			$this->connection = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
    			$this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    		}
    		catch (PDOException $e)
    		{
    			$this->connection = null;
    			die($e->getMessage());
    		}
    	}
    	
    	#get the number of rows in a result
    	public function num_rows($query)
    	{
    		# create a prepared statement
    		$stmt = $this->connection->prepare($query);
    		
    		if($stmt)
    		{
    			# execute query
    			$stmt->execute();
    			
    			return $stmt->rowCount();
    		}
    		else
    		{
    			return self::get_error();
    		}
    	}
    	
    	#display error
    	public function get_error()
    	{
    		$this->connection->errorInfo();
    	}
    	
    	# closes the database connection when object is destroyed.
    	public function __destruct()
    	{
            $this->connection = null;
        }
    }
    ?>

the extended class,

class database extends PDO
    {
    	
    	#make a connection
    	public function __construct($hostname,$dbname,$username,$password)
    	{
    		parent::__construct($hostname,$dbname,$username,$password);
    		
    		try
    		{
    			$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    		}
    		catch (PDOException $e)
    		{
    			die($e->getMessage());
    		}
    	}
    	
    	#get the number of rows in a result
    	public function num_rows($query)
    	{
    		# create a prepared statement
    		$stmt = parent::prepare($query);
    		
    		if($stmt)
    		{
    			# execute query
    			$stmt->execute();
    			
    			return $stmt->rowCount();
    		}
    		else
    		{
    			return self::get_error();
    		}
    	}
    	
    	#display error
    	public function get_error()
    	{
    		$this->connection->errorInfo();
    	}
    	
    	# closes the database connection when object is destroyed.
    	public function __destruct()
    	{
            $this->connection = null;
        }
    }

This is how I instantiate the class,

# the host used to access DB
    define('DB_HOST', 'localhost');

    # the username used to access DB
    define('DB_USER', 'root');

    # the password for the username
    define('DB_PASS', 'xxx');

    # the name of your databse
    define('DB_NAME', 'db_2011');
    	
    include 'class_database.php';

    $connection = new database(DB_HOST,DB_NAME,DB_USER,DB_PASS);
    $sql = "
    	SELECT *
    	FROM root_contacts_cfm
    	ORDER BY cnt_id DESC
    	";
    	
    $connection->num_rows($sql);

But I have errors when I call this extended pdo class,

Warning: PDO::__construct() expects parameter 4 to be array, string given in C:\\wamp\\www\\xx\\class_database.php on line xx

Fatal error: Call to a member function setAttribute() on a non-object in C:\\wamp\\www\\xx\\class_database.php on line xx

I have done some research online, I found this basic structure of extending pdo but I dont understand it…

class myPDO extends PDO
    {
       public function __construct($dsn,
                                   $username=null,
                                   $password=null,
                                   $driver_options=null)
       {
          parent::__construct($dsn, $username, $password, $driver);
       }

       public function query($query)
       {
          $result = parent::query($query);
          // do other stuff you want to do here, then...
          return($result);
       }
    }

What is $dsn variable for? How can I pass my $hostname variable into extended pdo class?

Another questions:
How can I make a method for displaying error in the extended pdo class?
How can I close the connection in the extended pdo class?

It is so difficult to move from mysqli to pdo!

Thanks.

aamonkey · March 3, 2011, 7:35pm

Lots of examples of what the $dsn string should like like in the manual

Basically it would be something like:

public function __construct($hostname,$dbname,$username,$password)
{
    $dsn = 'mysql:host='.$hostname.';dbname='.$dbname;
    parent::__construct($dsn, $username, $password);
    // other stuff
}

How can I make a method for displaying error in the extended pdo class?
You shouldn’t display an error from within the class - better just pass on the exception (or throw a new one) and have your calling code catch the exception and display the appropriate view

How can I close the connection in the extended pdo class?
No need - the connection will be closed upon script termination.

lauthiamkok · March 3, 2011, 8:44pm

Hi there, thank you for the reply.

Do you have a code example how to throw the exception outside the pdo extended class?

Thanks!

aamonkey · March 4, 2011, 2:37pm

It’s fine to throw exceptions within the class - your calling code should then catch any exceptions and handle those accordingly - just don’t directly print out errors from your database class.

lampcms_com · March 4, 2011, 2:45pm

A while ago I was facing the same situation and found the hard way that it’s not a good idea to extend PDO. A better way would be to write a wrapper class for PDO. It’s fine to extends PDOException but not PDO classes.

lauthiamkok · March 4, 2011, 2:50pm

Thank you, lampcms. What do you mean by a wrapper class for PDO?

Do you mean something like this?

class database
{
	# database handler
	protected $connection = null;
	
	# make a connection
	public function __construct($dsn,$username,$password)
	{
		try 
		{
			# MySQL with PDO_MYSQL  
			$this->connection = new PDO($dsn, $username, $password);
			$this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
		}
		catch (PDOException $e) 
		{
			# call the get_error function
			$this->get_error($e);
		}
	}
	
	#get the number of rows in a result as a value string
	public function num_rows($query)
	{
		# create a prepared statement
		$stmt = $this->connection->prepare($query);
		
		try 
		{
			# execute query 
			$stmt->execute();
			
			# return the result
			return $stmt->rowCount();
		} 
		catch (PDOException $e) 
		{
			# call the get_error function
			$this->get_error($e);
		}
	}
	
	# fetch a single row of result as an array ( =  one dimensional array)
	public function fetch_assoc($query, $params = array())
	{
		try
		{
			# prepare the query
			$stmt = $this->connection->prepare($query);
			
			# if $params is not an array, let's make it array with one value of former $params
			if (!is_array($params)) $params = array($params);
			
			# the line
			//$params = is_array($params) ? $params : array($params);
			# is simply checking if the $params variable is an array, and if so, it creates an array with the original $params value as its only element, and assigns the array to $params.

			# This would allow you to provide a single variable to the query method, or an array of variables if the query has multiple placeholders.

			# The reason it doesn't use bindParam is because the values are being passed to the execute() method. With PDO you have multiple methods available for binding data to placeholders:

			# bindParam
			# bindValue
			# execute($values)
			
			# The big advantage for the bindParam method is if you are looping over an array of data, you can call bindParam once, to bind the placeholder to a specific variable name (even if that variable isn't defined yet) and it will get the current value of the specified variable each time the statement is executed.
			
			# execute the query
			$stmt->execute($params);
			
			# return the result
			return $stmt->fetch();
		}
		catch (PDOException $e) 
		{
			# call the get_error function
			$this->get_error($e);
		}
		
		/*
		or,
		
		catch (Exception $e)
		{
			// Echo the error or Re-throw it to catch it higher up where you have more
			// information on where it occurred in your program.
			// e.g echo 'Error: ' . $e->getMessage(); 

			throw new Exception(
				__METHOD__ . 'Exception Raised for sql: ' . var_export($sql, true) .
				' Params: ' . var_export($params, true) .
				' Error_Info: ' . var_export($this->errorInfo(), true),
				0,
				$e);
		}
		*/
	}
	
	# fetch a multiple rows of result as a nested array ( = multi-dimensional array)
	public function fetch_all($query, $params = array())
	{
		try
		{
			# prepare the query
			$stmt = $this->connection->prepare($query);
			
			# if $params is not an array, let's make it array with one value of former $params
			if (!is_array($params)) $params = array($params);
			
			# execute the query
			$stmt->execute($params);
			
			# return the result
			return $stmt->fetchAll(PDO::FETCH_ASSOC);
		}
		catch (PDOException $e) 
		{
			# call the get_error function
			$this->get_error($e);
		}
	}
	
	# return the current row of a result set as an object
	public function fetch_object($query, $params = array())
	{
		try
		{
			# prepare the query
			$stmt = $this->connection->prepare($query);
			
			# if $params is not an array, let's make it array with one value of former $params
			if (!is_array($params)) $params = array($params);
			
			# execute the query
			$stmt->execute($params);
			
			# return the result
			return $stmt->fetchObject();
		}
		catch (PDOException $e) 
		{
			# call the get_error function
			$this->get_error($e);
		}
	}
	
	# insert or update data
	public function run_query($query, $params = array())
	{
		try
		{
			$stmt = $this->connection->prepare($query);
			$params = is_array($params) ? $params : array($params);
			$stmt->execute($params);
			return true;
		}
		catch (PDOException $e) 
		{
			# call the get_error function
			$this->get_error($e);
		}
	}
	
	# display error
	public function get_error($e) 
	{
		$this->connection = null;
		die($e->getMessage());
	}
	
	# closes the database connection when object is destroyed
	public function __destruct()
	{
        # set the handler to NULL closes the connection propperly
		$this->connection = null;
    }
}

Thanks.

lampcms_com · March 4, 2011, 2:58pm

Yes, some people also call it composite class
The instance of PDO will the an instance variable inside your own custom object

Don’t forget to add getter method to get $this->connection, it’s just a good practice.

lauthiamkok · March 4, 2011, 3:16pm

Thank you.

Sorry for throwing you a few more questions - how can I do that to get $this->connection ? Is it something like this below?

# don't forget to add getter method to get $this->connection, it's just a good practice.
	public function get_connection()
	{
		$this->connection;
	}

Is the code correct and how do I use it?

Thanks.

lampcms_com · March 4, 2011, 3:30pm

Your code is correct. How do you use it is up to you, but you are on the right track.

lampcms_com · March 4, 2011, 3:32pm

lauthiamkok:

Thank you.

Sorry for throwing you a few more questions - how can I do that to get $this->connection ? Is it something like this below?
# don't forget to add getter method to get $this->connection, it's just a good practice.
	public function get_connection()
	{
		$this->connection;
	}
Is the code correct and how do I use it?

Thanks.

Actually it should be like this:

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

lauthiamkok · March 4, 2011, 3:52pm

thank you