from mysql to PDO -- confused about the When and Where

I'm trying to switch from building query strings and calling mysql_query() and embrace PDO. I've read a handful of tutorials, and understand the basic process of preparing a statement and assigning values to the placeholders.

What I don't understand is how to integrate this new te开发者_JAVA技巧chnique into my existing web app.

Let me explain:

currently, I've got an init.php which is require_once'd on every page. It holds the database U/P and opens the database connection. With that loaded, I can sling queries left and right, willy AND nilly, anywhere I please without any further thought.

PDO strikes me as a much more deliberate approach. Do I still stuff all the U/P and connection handlers into my init file? Can I just work through my scripts and replace queries one-to-one with PDO statements?

Sorry for the simple question!

---

For what it is worth, I had the same issue and this is how I approached it. I divided the database functions into two different classes, Database and Query. I setup and open the database in my init function and then call instances of my Query class throughout the page, closing the queries each time I am done. Finally, I close the database.

The actual classes I use have some more error checking and other stuff I specifically, but here are the drafts I uses when putting the scheme together. Maybe it will be useful to you.

Database Class:

<?php
    class Database {

        private $db, $dbserver, $dbport, $dbname, $dbuser, $dbpassword;

        //----------------------------------------------------------------------------------------------------------
        //Function: __construct()
        //----------------------------------------------------------------------------------------------------------
        public function __construct($idbserver, $idbport, $idbname, $idbuser, $idbpassword) 
        {

        $this->dbserver = $idbserver;
        $this->dbport = $idbport;
        $this->dbname = $idbname;
        $this->dbuser = $idbuser;
        $this->dbpassword = $idbpassword;

        }

        //----------------------------------------------------------------------------------------------------------
        //Function: openDatabase()
        //----------------------------------------------------------------------------------------------------------
        public function openDatabase()
        {

        /* Create a new user db object with persistent database connection parameters */
        try {
            $this->db = new PDO("mysql:host=".$this->dbserver.";dbname=".$this->dbname.";port=".$this->dbport, $this->dbuser, $this->dbpassword, array(
                PDO::ATTR_PERSISTENT => true
                )); 
            return true;
            } catch (PDOException $e) {
                print "Error!: " . $e->getMessage() . "<br/>";
                die();
            }

        }

        //----------------------------------------------------------------------------------------------------------
        //Function: getDB
        //----------------------------------------------------------------------------------------------------------
        public function getDB()
        {

        //return the database
        return $this->db;

        }

        //----------------------------------------------------------------------------------------------------------
        //Function: getDBName
        //----------------------------------------------------------------------------------------------------------
        public function getDBName()
        {

        //return the database
        return $this->dbname;

        }




        //----------------------------------------------------------------------------------------------------------
        //Function: closeUserDatabase
        //----------------------------------------------------------------------------------------------------------
        public function closeDatabase()
        {

        //close the database
        $this->db=null;
        }



    }
?>

Then my Query Class:

<?php
    class Query {

        private $db, $sql, $qresult;

        //----------------------------------------------------------------------------------------------------------
        //Function: __construct
        //----------------------------------------------------------------------------------------------------------
        public function __construct($idb, $isql) 
        {

        $this->db = $idb;
        $this->sql = $isql;

        }

        //----------------------------------------------------------------------------------------------------------
        //Function: openQuery
        //----------------------------------------------------------------------------------------------------------
        public function openQuery($param) 
        {

            /* assemble and run a query and return a result object */

            //prepare the sql statement
            //get the db object

            $tempdb= $this->db->getDB();
            $this->qresult = $tempdb->prepare($this->sql);

            //bind parameters to the prepared statement
            foreach ($param as $key => $value) {
                $this->qresult->bindValue($key,$value);
                }

            return $this->qresult->execute();

        }

        //----------------------------------------------------------------------------------------------------------
        //Function: fetchAssociative
        //----------------------------------------------------------------------------------------------------------
        public function fetchAssociative() 
        {

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

        }


        //----------------------------------------------------------------------------------------------------------
        //Function: closeQuery
        //----------------------------------------------------------------------------------------------------------
        public function closeQuery()  
        {

        $this->qresult = null;

        }



    }
?>

Then I set up the database in my init function:

//initialize the user database with buffered set true so we can use nested queries
$usrdb = new Database($udbserver, $udbport, $udbname, $udbuser, $udbpassword, array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
//open the database
$usrdb->openDatabase();

Then I call my query in the page:

//set up the sql
$sql = "SELECT * FROM Blah WHERE blahid = :filter";

//create the parameter array
$params = array(':filter'=>$filter);

//craetea new query
$q = new Query($usrdb, $sql);

//run the query
if ($q->openQuery($params)) {

    //get the results if there are any
    while ($row=$q->fetchAssociative()) {

        $viewData['blahid']=$row['blahid'];

    }


}

//clean up the query
$q->closeQuery();

After that I work with the results just like I always used to.

---

PDO works same way as old school approach. You still need to define connection and initialise object in order to query database.

---

I store the PDO database connection and retrieve an instance of it as a singleton.

Any place you need your database, to run a query or execute prepared statements, you use the singleton instance.

$db_handle = PDO_DBConnect::getInstance();
$stmt = $db_handle->prepare("SELECT * FROM users WHERE name= ?");
$result = $stmt->execute(array($cleaned_username));
...

If a class has multiple queries, you can store the handle in an instance variable,
$this->dbh = PDO_DBConnect::getInstance(); for convenience.

One other note is unlike pg_close or mysql_close, there is no close method for PDO. You simply set the db handle to null.