Help with converting to OOP with MySQL queries:

[skygremlin]

I’m moving my code from Procedural to OOP and am having an issue trying to run a basic query.  Looks like the connection to the DB is made, but I must not be fully understanding Objects / scope of Objects yet..  

 

I’m trying keep it simple so I can test this out and understand it before updating my code.  My instal thoughts are:

1.  Get OOP class set up for DB Connection.  MySQL first as that’s what I’m using now.  Then config MSSQL and Oracle classes for easy integration.

2.  Get simple queries working using a basic function file with PDO (Like current code).

3.  Convert my function files to classes.  I’ve separated my code to 3 function files (categories) for some organization.  The files aren’t huge, but they do range from 150 lines to just over 1000.

 

Below is the code I’m testing with.  Pretty simple and I hope straight forward.  I’ve also tried sending in $dbConn as a variable versus have it as a global variable in the function..  I’ve read that’s the better way.  But if anybody has thoughts on that too I would love to hear opinions / recommendations.

 

thanx

 

Index.php - testing page:

/**
     * Start Includes sections
     */
    require_once("../includes/db_conn/MYSQL_database.php");
    require_once("../includes/functions.php");
    /**
     * End Includes sections
     */
    
    /**
     * Check DB Connection
     */
    if (isset($config)){
        echo"<pre>";
            print_r($config);
        echo"<pre>";  
    }else{
        echo'<script type="text/javascript">window.alert("config not set")</script>';
    }
    echo"<hr>"; 
    //if (isset($database)){echo "Database opened"; } else {echo "Database not opened";}
    
    /**
     * If connected to DB load page
     * Else echo not connected
     */
    if (isset($database)){
        //echo "Database opened"; 
        /**
         * Run query to verify working.
         */
        $customers = cust_list($dbConn);
        echo "<pre>";
            print_r($customers);
        echo "</pre>";
        
        
   
    } else {
        echo "Database not opened";
    }

MySQLDatabase Class:

class MySQLDatabase {

    /**
     * MySql Connect to DB
     */
    private $host;
    private $dbname;
    private $username;
    private $password;
    public $dbConn;
    
    
    function __construct($config){
        $this->host = ($config['host']);
        $this->dbname = ($config['dbname']);
        $this->username = ($config['username']);
        $this->password = ($config['password']);     
    }
    
    private function open_connection(){  
        try{
            //$this->connection = new PDO('mysql:host=' . $host . ';dbname=' . $dbname, $username, $password);
            $this->dbConn = new PDO('mysql:host=' . $host . ';dbname=' . $dbname, $username, $password);
            echo "<script type=\"text/javascript\">window.alert(\"Connection Made\")</script>";
        }catch(PDOException $e){
            echo 'ERROR Inside the open_connection funtion: ' . $e->getMessage();
        }    
    }   
  
}

    /**
     * Create object
     */
    $database = new MySQLDatabase($config);

Functions File (will be moved to a Class next):

function cust_list($dbConn){
        //global $dbConn;
        
        try{
            /**
             * Prepare
             */
            $cust_list = $dbConn->prepare("select * from customer");

            /**
             * Bind
             */

            /**
             * Execute
             */
            $cust_list->execute();

            /**
             * Fetch from array
             */
            $cust_list_array = $cust_list->fetchall(PDO::FETCH_ASSOC);
            
            /**
             * Return
             */
            return $cust_list_array;

            
        }
        catch(PDOException $e){
            echo"Inside cust_list " . $e->getMessage();
        }
        
        
    }

When sending in $dbConn I get:

Notice: Undefined variable: dbConn in /Users/aaronjk/localhost/base_sites/php_base/public/index.php on line 34

Fatal error: Call to a member function prepare() on a non-object in /Users/aaronjk/localhost/base_sites/php_base/includes/functions.php on line 12

 

When setting $dbConn as global inside the function I get:

Fatal error: Call to a member function prepare() on a non-object in /Users/aaronjk/localhost/base_sites/php_base/includes/functions.php on line 12

 

 

From What I've read I "Call to member function prepare() on a non-object in.. " is because I need the global connection $dbConn set in the called function, but I thought I was doing that.

[iarp]

In your index.php i see you keep checking for $database but never once $dbConn. Where is $dbConn set? it kind of sounds like you should be passing $database.

 

I see it now, you need to pass $database->dbConn to the function, not $dbConn.

 

I don't see you calling open_connection anywhere within the class either, looking at it i would figure you should add it to the end of the __construct

Edited March 31, 2014 by iarp

[skygremlin]

hmmm I made a couple changes with new results.  I think I'm understanding what you meant..

 

Index.php:

/**
     * If connected to DB load page
     * Else echo not connected
     */
    if (isset($database)){
        //echo "Database opened"; 
        /**
         * Run query to verify working.
         */
        $customers = cust_list($database->dbConn);
        echo "<pre>";
            print_r($customers);
        echo "</pre>";
        
        
   
    } else {
        echo "Database not opened";
    }

MySQL Database Class:

class MySQLDatabase {

    /**
     * MySql Connect to DB
     */
    private $host;
    private $dbname;
    private $username;
    private $password;
    public $dbConn;
    
    
    function __construct($config){
        $this->host = ($config['host']);
        $this->dbname = ($config['dbname']);
        $this->username = ($config['username']);
        $this->password = ($config['password']);    
        $this->open_connection();
    }
    
    private function open_connection(){  
        try{
            //$this->connection = new PDO('mysql:host=' . $host . ';dbname=' . $dbname, $username, $password);
            $this->dbConn = new PDO('mysql:host=' . $host . ';dbname=' . $dbname, $username, $password);
            echo '<script type="text/javascript">window.alert("Function:  '.__FUNCTION__.' Line:  '.__LINE__.'")</script>';
        }catch(PDOException $e){
            echo 'ERROR Inside the open_connection funtion: ' . $e->getMessage();
        }    
    }   
  
}

    /**
     * Create object
     */
    $database = new MySQLDatabase($config);

New Errors:

Notice: Undefined variable: host in /Users/aaronjk/localhost/base_sites/php_base/includes/db_conn/MYSQL_database.php on line 29

Notice: Undefined variable: dbname in /Users/aaronjk/localhost/base_sites/php_base/includes/db_conn/MYSQL_database.php on line 29

Notice: Undefined variable: username in /Users/aaronjk/localhost/base_sites/php_base/includes/db_conn/MYSQL_database.php on line 29

Notice: Undefined variable: password in /Users/aaronjk/localhost/base_sites/php_base/includes/db_conn/MYSQL_database.php on line 29
Array
(
[host] => localhost
[username] => root
[password] => root
[dbname] => portal_db
)

Array()

[skygremlin]

I've fixed the undefined variable in the past with using !empty like below..  But for some reason If I use the below code it's still posting those errors.  And my query doesn't seem to be working either...  

function __construct($config){
        if(isset($config['host'])){
            if(!empty($config['host'])){
                $this->host = ($config['host']);  
                echo'<script type="text/javascript">window.alert("Host is:  '.$this->host.'")</script>';
            }
        }
        if(isset($config['dbname'])){
            if(!empty($config['dbname'])){
               $this->dbname = ($config['dbname']);
                echo'<script type="text/javascript">window.alert("DBname is:  '.$this->dbname.'")</script>';
            }
        }
        if(isset($config['username'])){
            if(!empty($config['username'])){
                $this->username = ($config['username']);  
                echo'<script type="text/javascript">window.alert("UserName is:  '.$this->username.'")</script>';
            }
        }
        if(isset($config['password'])){
            if(!empty($config['password'])){
                $this->password = ($config['password']);    
                echo'<script type="text/javascript">window.alert("Password is:  '.$this->password.'")</script>';
            }
        }
        $this->open_connection();
    }

[mac_gyver]

the line number where the error is occurring at is inside your open_connection() method code. you need to use $this->host, ect... to reference the class properties.

 

also, to use try/catch around the pdo statements that prepare/execute queries requires that you set PDO::ATTR_ERRMODE to  PDO::ERRMODE_EXCEPTION.

 

lastly, imo, for something like a database class, where you are just adding features, such as error checking/handling, to an existing built-in class,  it is better to just extend the pdo class with your class so that an instance of the pdo class will be returned to the calling code when you create an instance of your class, i.e. $database = new MySQLDatabase($config); will mean that $database is an instance of the pdo class and you can directly use the methods of the pdo class with it.

Edited March 31, 2014 by mac_gyver

[trq]

On a side note. You don't just convert procedural code to class based code and call it OOP. It's not. OOP is a complete paradigm, you can't simply "convert" to it.

[skygremlin]

Thanx mac_gyver and trq..  

 

@mac_gyver I'm trying your suggestions and so far no luck...  I'll play around some more and see if I can figure out what I'm doing wrong..  I'm sure it's something basic, I just need to do some more looking / research.  

 

@trq thank you.  I think we're on the same page..  Hopefully just a poor choice of words on my part..  That being said what you posted helped me look at things from another angle.  

 

thanx guys