How to access PDO prepare and execute from inside a class?

oikram · March 30, 2009, 8:48am

Hi,

I’m absolutly newbie on PHP and OOP :s
But I really want to learn, so any comments will be greatly appreciated. I’m trying to understand how things work…

I have connected to the database, using my first class and with PDO. Good.

Working class:

class LigacaoBD
{
       private $username;
       private $password;
       private $dsn;
       private $conexao;

       public function __construct () {


$this>dsn="mysql:unix_socket=/tmp/mysql.sock;dbname=database_name";
               $this->username = "username_bd";
               $this->password = "password_bd";

       }

       public function efectuaLigacaoBD () {

               try {

                       $this->conexao = new PDO($this->dsn,
                       $this->username, $this->password);

               }
                               catch(PSOException $e){
                       echo "Erro de Conexão: " .$e->getMessage();
               }

       }

       public function terminaLigacaoBD () {

               $this->conexao = NULL;

       }

}

I’d like to get data from the database using the prepare/execute methods.
I’ve tried this:

$handlerbd->prepare('SELECT * FROM users');

But I get an error: " Call to undefined method LigacaoBD::prepare() …" and
it’s correct, because the LigacaoBD doesn’t have this method. The PDO has.

So, I though, instead of using this:

$handlerbd->prepare('SELECT * FROM users');

Maybe I can get this:

Change the scope of class property conexao to: public ;

And call it like this:

$handlerdb->conexao->prepare('SELECT * FROM users');

Ok. This seems to work BUT, I know that it’s not a good OO policy to access
class properties directly (right?), so this is not a good method…

Question:
Can please someone explain to me, how can we PROPERLY access the PDO prepare
method from within an instantiated class, where that PDO resides?
How can I access the prepare PDO method, from an instance of the class
LigacaoBD ?

Thanks a lot,
Márcio

AnthonySterling · March 30, 2009, 9:07am

You cannot access the PDO object methods because you return an instance of LigacaoBD.

You could create a factory to return the pre-configured PDO object if you wished…


<?php
class DatabaseFactory
{
    protected static $oConnection;
    
    public static function Build()
    {
        if((self::$oConnection instanceof PDO) === false)
        {
            self::$oConnection = new PDO('dsn');
        }
        return self::$oConnection;
    }
}

$oDatabase = DatabaseFactory::Build();

$oDatabase->prepare();
?>

oddz · March 30, 2009, 9:15am

Just extend PDO and place the try catch around a call to the parent constructor.

To give you an example the below is what I use.


class Connection extends PDO {
  
    public function __construct($pHost,$pUser,$pDbName,$pPwd) {
  
        try {
      
           
	parent::__construct("mysql:dbname=$pDbName;host=$pHost",$pUser,$pPwd);
          
        } catch(PDOException $e) {
      
            echo ('Unable to Connect');
            die;
        
        }
      
    }
  
}

oikram wrote:

Ok. This seems to work BUT, I know that it’s not a good OO policy to access
class properties directly (right?), so this is not a good method…

Correct, use mutators (getter and setters).


class LigacaoBD
{
       private $username;
       private $password;
       private $dsn;
       private $conexao;
 
       public function __construct () {
 
 
$this>dsn="mysql:unix_socket=/tmp/mysql.sock;dbname=database_name";
               $this->username = "username_bd";
               $this->password = "password_bd";
 
       }
 
       public function efectuaLigacaoBD () {
 
               try {
 
                       $this->conexao = new PDO($this->dsn,
                       $this->username, $this->password);
 
               }
                               catch(PSOException $e){
                       echo "Erro de Conex&#227;o: " .$e->getMessage();
               }
 
       }
 
       public function terminaLigacaoBD () {
 
               $this->conexao = NULL;
 
       }

      [b] public function getConexao() {

          return $this->conexao;

       }[/b]
 
}


$handlerdb->getConexao()->prepare('SELECT * FROM users');

Although under this circumstance you should also add a method to check if conexao exists or is not null. It is possible from your code that the conexao property could be null. If that was true and you didn’t check for it you could run into a error by calling prepare on potentially a non-object.

Furthermore, you problem doesn’t lie in accessing the PDO instance from inside a instance of LigacaoBD but outside.

oikram · March 30, 2009, 10:58am

Thanks a lot for your replys. To give you an idea about my “newbidity” here is what I don’t understand:

What is a factory and what does it do?

Why using a try catch inside the constructor will help me?
(assuming that “around a call to the parent constructor” is the same as: “inside the constructor”).

I see a lot of getters and setters on examples around the web, and books, but no one seems to care about the explanations of this methods. I mean, I know, because I’ve read, that we cannot access the properties directly, but I don’t know why, and why we need get and set methods. (that now i’ve learn we call them mutators)

What are class extensions?

So… according to all this, I must execuse myself. This is my first class, (and what a class I may say). But I’ve learn a lot already (automotivating moto).

So I propose we stick with one answer and we digg in, till I’m able to understand the answer you have give to me.

So, my question is now: will you be able to help me understand the answers provided ? It will take a lot of Q/A I believe…

Assumptions:

I have been told that we should use a class, among other advantages, to better organize our code, and that the future of PHP is OO.
According to this, I’m learning PHP directly the OO way.
I have been told that PDO is the way to go, and we should more or less forget the mysql and mysqli methods. So, that’s why I’m learning PDO.
I wish to use a prepare/execute PDO method because I’ve read that it’s more efficient if you want, for example, prepare a query, and then execute it several times, like on a “multiple insert record” scenario.
If this is correct, then I should proceed with this.

What I’m trying to accomplish:
Understand a class that uses:

Not mysql or msqli functions but PDO -> to connect to the database. -> DONE. (it works and I understand)

Not use the (query / exec) but PDO Prepare/Execute; -> To access database data.

Not use fetch_assoc but, Fetch_obj; -> To fetch the data from the database as objects..

Assuming that, those are the best tools we have to properly develop OO PHP.

The gap here is that, I have read about all this separatly, but never all toghether, and never all on a class.

More then code, I believe I need to understand the big picture of this all, and for that I will need your help.

Thanks a lot,
Márcio

oikram · March 30, 2009, 4:15pm

Try number II:

“Extend PDO.” - What does this means?
Inheritance: Use a class as the basis for another class. In this case, when we do “Class Connection extend PDO” we are saying that the class Connection will inherit all the public and protected methods and proprieties of the PDO class.

“parent::__construct”
The :: symbol allow us to name the class where PHP should search for a method or property. But if we want to refer to the current class’s parent, we also use it, so, in this case, telling "parent::__construct will be the same as PDO::__construct.

What are the parameters of the PDO::__construct method on the PDO class, we have not created it, how can we know that?
Well, we know that because we see a lot of instances of that class being populated with parameters, and by definition a constructor will run when we call an instance of class, SO, the parameters of the __construct method may be $dsn, $username, $password, $driver_options (etc… php.net)

What does it do?
“It represents a connection to the requested database.”

Now this:

public function getConexao() {

          return $this->conexao;

       }

What what does this do?
Since we cannot access the class properties directly, because it would give us some problems (that later on, I hope, we, newbies, will understand), we use a function that actually get the proprieties we want from the class. So, if, in this case, we want to use the property conexao, we will not change the conexao access modifier to “public” and call it like this:
$handlerdb->conexao

Instead, we will keep the access modifier of conexao defined as “private” and we will create a method to get that property so that we can work with when we instantiate the class (or, even, inside the class). To call we use:
$handlerdb->getConexao()

Since we use a name convention here, we will now that getConexao means, get a value that is instance of the property conexao.

Question 1)
I’m on the right track? Can you please correct some imprecisions.

Question 2)
Why should we place the try catch around a call to the parent constructor ?

Thanks a lot once again,
Márcio

oikram · March 30, 2009, 11:34pm

Try number III:

Extend means, that I will have all the public and protect methods of PDO available from ligacaoBD, right?
So Inside my class I will have:

Class ligacaoBD extends PDO {

// Should I put all my class inside?

}

Should I declare this, instead of my normal class declaration?

So my parent class will be PDO, and when I need to access the public methods or properties of the PDO I will use
parent::prepare($this->sql); or PDO::prepare($this->sql) for example?

Regards,
Márcio

oddz · March 31, 2009, 1:13am

oikram wrote:

What what does this do?
Since we cannot access the class properties directly, because it would give us some problems (that later on, I hope, we, newbies, will understand), we use a function that actually get the proprieties we want from the class. So, if, in this case, we want to use the property conexao, we will not change the conexao access modifier to “public” and call it like this:
$handlerdb->conexao

Good practice dictates that a object should in control of its own state. When properties are able to manipulated by the outside world the object no longer has control of its own state. However, by using getter and setters the outside world can indirectly modify the objects state while the object is directly able to control that input.

A simple example of the purpose of getter and setters can be seen in the below User class. The user class has a property name. Name should only ever be a string. By using a setter we can control that where as if we were able to set the property directly from the outside world we would be unable to control that. In most other languages all we would need to do is actually type the argument to a string, but PHP isn’t f OO oriented just has support for it. For example, a string and integer aren’t objects.


class User {

	private $_name;

	public function setName($pName) {
	
		if(is_string($pName)) {
		
			$this->_name = $pName;
		
		}
	
	}
	
	public function getName() {
	
		return $this->_name;
	
	}

}


$user = new User();
$user->setName(3); // not going to set name because it isn't a string
$user->setName('tom'); // sets name because argument is a string
echo $user->getName(); // returns 'tom'

oikram wrote:

So my parent class will be PDO, and when I need to access the public methods or properties of the PDO I will use
parent:repare($this->sql); or PDO:repare($this->sql) for example?

Under this circumstance you may call $this->prepare() inside the subclass because the the prepare method doesn’t exist inside the subclass.


Class ligacaoBD extends PDO {

   public function send($pSql) {

    $this->prepare($pSql);

   }

}

However, if you override the prepare method then you must call it upon the parent.


Class ligacaoBD extends PDO {

   public function prepare($pSql) {

    parent::prepare($pSql);

   }

}

oikram wrote:

Why using a try catch inside the constructor will help me?

That is just for convenience. You could just as easily place it outside the class when creating a instance.

oikram wrote:

I wish to use a prepare/execute PDO method because I’ve read that it’s more efficient if you want, for example, prepare a query, and then execute it several times, like on a “multiple insert record” scenario.

Well yes but in terms of what your talking about it would be more efficient to do it all at once using one query.


INSERT INTO TABLE_NAME (id,whatver) VALUES (NULL,'blah'),(NULL,'blah again'),(NULL,'blah blah blah');

oikram wrote:

What is a factory and what does it do?

The below isn’t a factory, but the singleton pattern. A singleton only allows you to create one instance of the class. You would generally also declare the constructor as private or protected so that a error would occur if you attempt to instantiate a object of the class from outside the class. Generally this done by using a protected/private static property that holds the instance and a public static method that returns or creates the instance based on whether it has or hasn’t been already created. A factory pattern does exist, but it has a completely separate purpose from a singleton.


<?php
class DatabaseFactory
{
    protected static $oConnection;
    
    public static function Build()
    {
        if((self::$oConnection instanceof PDO) === false)
        {
            self::$oConnection = new PDO('dsn');
        }
        return self::$oConnection;
    }
}

$oDatabase = DatabaseFactory::Build();

$oDatabase->prepare();
?>

AnthonySterling · March 31, 2009, 8:03am

Good morning Oddz,

I think I’m a little confused, and as such may have mislead the OP.

In your post, you state that my example is not a factory; putting the singleton shenanigans aside, it was my understanding that as the object returned a pre-configured object it would be a factory, no?

Some clarification would be greatly appreciated.

oddz · March 31, 2009, 9:37am

more or less a typical factory:

A factory links up two separate but related class hierarchies. This is normally accomplished using the template pattern to build a abstract class that returns a object typed in relationship to a shared interface.

This example may place it in a easier to understand context.

template:


abstract class Controller {

	private $_view;
	
	public function __construct() {
		
		$this->_view = $this->getView();
		$this->_view->render();
	
	}

	abstract protected function getView();

}

concrete controllers


class BlogController extends Controller {

	protected function getView() {
	
		return new BlogView();
	
	}

}

class HomeController extends Controller {

	protected function getView() {
	
		return new HomeView();
	
	}

}

class ContactController extends Controller {

	protected function getView() {
	
		return new ContactView();
	
	}

}

shared interface:


interface Viewable {

	public function render();

}

concrete related views:


class BlogView implements Viewable {

	public function render() {
	
		echo 'blog view';
	
	}

}

class HomeView implements Viewable {

	public function render() {
	
		echo 'home view';
	
	}


}

class ContactView implements Viewable {

	public function render() {
	
		echo 'contact view';
	
	}

}

As long as you program to the Viewable interface you may easily add a new controller by extending the template and have the abstract method return a new Viewable object. In php if it possible to type the return value of getView() that would be typed as Viewable, but that isn’t possible so you just need to enforce that rule yourself.

oikram · March 31, 2009, 10:07am

Sorry, back to the topic:

So when you have this:

class Connection extends PDO {

    public function __construct($pHost,$pUser,$pDbName,$pPwd) {
        parent::__construct("mysql:dbname=$pDbNamehost=$pHost",$pUser,$pPwd);

in this example, are we overriding the parent class with the construct params of the Connection class?

I mean, I believe that since we are extending PDO we don’t need to call an instance, (like new PDO ()) we only need to, in this PDO case, to call the parent constructor and change his arguments with new parameters. Since we are changing the the parent construct with our own params, we call this overriding? Is this correct and precise?

Thanks a lot, (I’m almost there I believe, I just need to clear some things first, then I will present a code for consideration here, thanks for your patience and time).

Regards,
Márcio

oddz · March 31, 2009, 10:33am

Yes, override just means to declare a new implementation for a method that already exists in the super class. If you override the constructor then you must explicitly call the parent constructor to pass the required arguments.

AnthonySterling · March 31, 2009, 11:14am

Off Topic:

Thank you Oddz for your time and the provided resource, it cleared up more questions than it created, which must mean I’m getting somewhere!

oikram · March 31, 2009, 12:17pm

class LigacaoBD extends PDO {

private $username;
private $password;
private $dsn;
private $conexao;
private $sqlquery;

  public function __construct () {
	
    $this>dsn="mysql:unix_socket=/tmp/mysql.sock dbname=database_name";
    $this->username = "username_bd";
    $this->password = "password_bd";
		
      try {
        parent::__construct($this->dns, $this->username, $this->password);
      } catch(PDOException $e) {
         echo('Unable to Connect');
         die;
      }
/*you have not put the getMessage, because you don't want the users to see details about your errors, right? So, we use getMessage when we are developing, and then we take them away on production mode?*/

		
  }//end of public function construct

}//end of class

Then I should add a function like this:

public function preparequery($sqlquery) {

$this->prepare($sqlquery);

}

Do I need a accessor for this? Maybe not, since on the external php file, I will call this class property $sqlquery, using the preparequery method, so it will be ok?

[update]
I believe that, with this, I will never need/want access a $sqlquery without a method, since, in this class, I want to have all the methods that I could have to operate the SQL querys. So prepare will be here, execute also, fetch_obj (also). Is this a good way of doing things? [/update]

I’m on the right track?

Oddz. Thanks a lot. I’m really sorry about all this replys, I’m felling bad with all this, believe me, but I have read and read, but I read employe person examples, and I’m getting a hard time to pass from employe/person examples to PDO examples. And now, when I’m in front of the code, I really have some difficulties. Please have patience with me.

@SilverBulletUK: Your comments are also welcome.

Thanks a lot guys, really.

Regards,
Márcio

oddz · March 31, 2009, 12:26pm

Yes, if you name the method preparequery() you can call $this->prepare($sql) directly and it will resolve to prepare() method of the PDO class. I don’t believe a preparequery() method exists inside the PDO class, but that’s something you may want to check in the docs.

oikram · March 31, 2009, 12:44pm

I’m feeling that having a method called preparequery on a class that extends pdo is somewhat a litle bit redundant. I mean, If I’m inside a class that as the prepare method in it, why should I have a preparequery method to call that other prepare() from PDO ?
Anyway: I’m newbie, maybe I must give myself a break…

I will give it a try and see what happen.

Thanks a million, really,
Márcio

oikram · March 31, 2009, 3:40pm

Thanks. Here is the class with your help:


<?php

class NkPDO extends PDO {

private $username;
private $password;
private $dsn;
private $query;
		
public function __construct () {
$this->dsn="mysql:unix_socket=/tmp/mysql.sock dbname=mydatabasename";
			

$this->username = "usernamebd";
$this->password = "passbd";
		
try {					
					
parent::__construct($this->dsn, $this->username, $this->password);
					
} catch(PDOException $e) {
					
	echo "Erro de Conexão: " .$e->getMessage();
		
  }
	
 }	
}

Since this is an extension, for now, I believe I don’t need the to create a
prepareQuery method inside, or something.

To execute the prepared statement, however, I need to extend another classe, because the execute() in a method of PDOStatement class.

I may do something like this:


class DBStatement extends PDOStatement
{
    public $dbh;

    protected function __construct($dbh)
    {
        $this->dbh = $dbh;

        //for later consideration.
        $this->setFetchMode(PDO::FETCH_OBJ);
    }

So when I do $dbh->execute($query), it will work. However, this is and illegal move as stated above. So, now it’s the time to call a getter method?


class DBStatement extends PDOStatement {
private $dbh;

protected function __construct() {
   $this->setFetchMode(PDO::FETCH_OBJ);
}

public function getDbh () {
  $this->dbh=$dbh;

}

Please advice:
I’m on the right track here?

Regards,
Márcio

oikram · July 23, 2009, 9:15am

I forget to close this topic, for others that may came and maybe found this useful.
It’s not perfect, but this is what I’ve done with the list/forum help:

A myPDO class:

class MyPDO extends PDO 
{
	private $_username;
	private $_password;
	private $_dsn;	
	private $_opcoes;
	
	private static $_instance = null;
		
		final public function __construct () 
                {
	
			$this->_dsn="mysql:unix_socket=/tmp/mysql.sock;dbname=my_bd_name";
			
			$this->_username = "myUser";
			
			
			$this->_password = "mypass";
			
			
			$this->_opcoes = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8");
			
			
				
      			try 
                       {					
					
					
			  parent::__construct($this->_dsn, $this->_username, $this->_password, $this->_opcoes);
      			} 
	  
	  		catch(PDOException $e) 
                        {
					
			 echo "Erro de Conex&#227;o: " .$e->getMessage();  
		
      			}

	
  		} //eo construct
		
		public static function getInstance() 
                {
			
			if (self::$_instance === null) 
                        {
      			
				self::$_instance = new self;
    		        }
    		
			return self::$_instance;
  		
		}//eo getinstance

} //eo class

?>

A DAOAbstract Class:

abstract class DAOGeneral 
{

  protected $_dbh;
  
  public function __construct() 
  {
    require_once("/absolutepath/to/MyPDO.class.php"); 

	$this->_dbh = MyPDO::getInstance(); 
	
 }

}

Regards,
Márcio