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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
oikram
Forum Newbie
Posts: 4
Joined: Mon Mar 30, 2009 3:36 am

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

Post by oikram »

[UPDATE]
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...
[/UPDATE]


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

Working class:

Code: Select all

class LigacaoBD
{
       private $username;
       private $password;
       private $dsn; //a variável relativa ao data source name usado pelo
PHP Data Objects.
       private $conexao;
 
       public function __construct () {
 
 
               $this->dsn =
"mysql:unix_socket=/tmp/mysql.sock;dbname=nome_da_base_de_dados";
               $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:

Code: Select all

$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:

Code: Select all

$handlerbd->prepare('SELECT * FROM users');
Maybe I can get this:

Change the scope of class property conexao to: public ;

And call it like this:

Code: Select all

$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
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: How to access PDO prepare and execute from inside a class.

Post by novice4eva »

I don't know what i did was proper or not, but this is what i did: added a function within my class which was a wrapper class for all database related actions

Code: Select all

 
function dbPrepare()
    {
        if(!$this->stmt = $this->connection->prepare($this->Sql))// $this->connection is PDO connection resource, $this->Sql sql statement that i set from some another function
        {
            array_push($this->Msg,'Couldnot prepare the query.');
            return false;
        }
        return true;
    }
 
oikram
Forum Newbie
Posts: 4
Joined: Mon Mar 30, 2009 3:36 am

Re: How to access PDO prepare and execute from inside a class.

Post by oikram »

Thanks a lot for your comment. It's very hard to understand this, I'm really newbie.

This seams to be a very nice way of doing thinks. I also want to have on my class a way to handle database operations.
prepare() execute() and fetch.

Ok...

So that function is inside your class.

And this:
"$this->connection->prepare($this->Sql)"

Is the part when, in your class you will prepare the query that will be associated to the property sql, right?

So, when you want to prepare a given sql query, how do you call it:

Like this?
dbPrepare($sql);

This leads me to another question, where should I actually write the selects? On the page where I instanciate the class and I will have something like this maybe:
$myclass->sql(select * from users);
??

Thanks a million for your post, please, correct strictly all interpretation errors I may been have on this.



Regards,
Márcio
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: How to access PDO prepare and execute from inside a class.

Post by novice4eva »

The process may vary from person to person. But this is the way i do it.
First lets see how my database class would look like, it might not be functional, just a way to give you an idea.

Code: Select all

 
//DATABASE is the class i was talking about, that handles all the database related tasks
class Database
{
   public $connection;//will hold conncetion resource
   public $stmt;//will hold statement resouce
   public $sql;//will hold the sql user provides
 
 
    public function Database()
    {
            //do connection, or use one that already exists
    }
   
 
   //FUNCTIONS FUNCTIONS MORE FUNCTIONS
function setQuery($Sql)
{
//setQuery sets SQL to the current object
}
 
function dbPrepare()
{
//does the prepare thing in PDO, also sets the statement resource, from the Sql that is set via setQuery
}
 
function bindParam()
{
//Do bindParam s' if the sql contains ":"
}
 
function dbExecute()
{
//dbExecute does execute on statement resource
}
 
function fetchData()
{
//do fetchAll or fetch on statememt resource set from dbPrepare
}
 
    function queryReturn($Sql)
    {
        $this->setQuery($Sql);
        if($this->dbPrepare())  
        {
                         $this->bindParam();
            return $this->dbExecute();
        }
        return false;
    }
 
 
}
 
Sorry i cannot make things clearer with the above class!! Tell us where you get stuck and maybe we will pull you outa it :wink:
if i have a class lets say employee, then

Code: Select all

 
class employee extends Database
{
    public function listEmployees()
    {
        $Sql="
        SELECT
                employee_cd,
                employee_name,.....
         FROM
              tbl_employee
         WHERE....
           ";
         return  $this->queryReturn($Sql);//queryReturn is the function in my database class that does all the prepare, if there are bind variables then bind too, and executes the statement, all in one
    }
}
 
In the control page, lets say a page where i list out all the employees i do this:
<?

Code: Select all

 
include(Databse.php);
include(employee.php);
$objEmployee = new Employee();
 
if($objEmployee->listEmployees())
{
$employees = $objEmployee->fetchData();//again fetchDatais a function in my database class that does equivalent of statement->fetchAll or fetch
}
else
{
//there were some errors
}
 
?>

Hope this helped a bit.
oikram
Forum Newbie
Posts: 4
Joined: Mon Mar 30, 2009 3:36 am

Re: How to access PDO prepare and execute from inside a class.

Post by oikram »

That helps a lot.


Welll, the time as passed and I have come with this:
class NkPDO extends PDO {
private $username;
private $password;
private $dsn;

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

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

This will extend the PDO, so now I can access the PDO properties from within this class.

Before I was unable to use the prepare() execute() method to access the database info.
With this class, I can now do this:

Create an instance of this class:

Code: Select all

$conn = new NkPDO();

Use the prepare method of the PDO Class:

Code: Select all

$conn->prepare('SELECT id_cliente, nome_cliente FROM cliente');
And if I grab this prepare statement on a variable, lets say:

Code: Select all

$query=$conn->prepare('SELECT id_cliente, nome_cliente FROM cliente');
Since the PDO->prepare() returns a PDOStatement object, I can now do:

Code: Select all

$query->execute();
A class like this, allows me to shortcut the connection, and work with the PDO methods. However, I'm still searching for a class that shortcuts my way to the database operations.

So I believe that my class, corresponds only to your constructor method. Now, I need, just like you, to add more functions and more functions. :)

I have two questions concerning your example:
1) I've read here and there, that it's better to keep our class properties private, because that will not allow the changes outside the methods we have defined.
So, why:
public $connection;
public $stmt;
public $sql;

instead of
private $connection;
private $stmt;
private $sql;

?

2) In the future I will have a employee class also, and a costumer class, and a dog class, and I'm sure I will have more then one $sql associated. So, I believe that, within your class employee , you will have more than one sql query. (this will be a very basic question I realise that). So you can grab several sql querys and hold them on a property $sql. Each of those sql querys will be called uppon the call of each associated method, correct? So, we can have for each property several values on a class, and depending on the method, we will have different values to work with. Is this correct?


Thanks a lot, really, you have absolutely understand the issue here, I was having problems finding what and how, can we organize and work with a class.
I cannot entirely develop a class right now because I'm aware of how to use bindParam and other thinks like that, but I will surely try on the future. ;)

For now, can you please just confirm the 2) point and answer the 1). ?



Kind regards,
Márcio
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: How to access PDO prepare and execute from inside a class.

Post by novice4eva »

1) Yes true. My bad, you must use private identifiers.
2) correct correct correct :D
Post Reply