Database Connection class that needs improving

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

Post Reply
kalthar
Forum Newbie
Posts: 7
Joined: Fri Dec 11, 2009 2:28 pm

Database Connection class that needs improving

Post by kalthar »

Recently I wrote a database class to make using database functions easier, but some features need work (returning objects is one) and any other critique is welcome.

Code: Select all

 
<?php
 
// Define variables to be passed into the function to return values
 
/**
 * @var: Object
 * @desc: Return an object from the database, this is the default return
 */
define('OBJECT', 'OBJECT', true);
 
/**
 * @var: Array_A
 * @desc: Return an associative/numerically indexed array
 */
define('ARRAY_A', 'ARRAY_A', false);
 
class Connection {
 
    private $host, $user, $pass, $dbase;
    public $result;
 
    // Create a constructor function to connect
    public function __construct() {
    
        // Register the __destruct function so that the class returns true at the end
        register_shutdown_function(array(&$this, "__destruct"));
        
        // Set up the public variables with the data we need
        $this->host = dbhost;
        $this->user = dbuser;
        $this->pass = dbpass;
        $this->dbase = dbase;
        
        // Instantiate a connection
        $this->connectToDb();
    
    }
    
    // Generic query function
    public function setQuery($query = null, $true = false) {
    
        // Create an sql variable that contains the query string
        $sql = $query;
        
        // Get the result
        $this->result = mysql_query($sql) or die (mysql_error());
        
        // If the true variable is true, then return true rather than the result
        if ($true == true) {
        
            // If this succeeded then we return the true
            if ($this->result) {
            
                return true;
            
            } else {
            
                return false;
            
            }
        
        } else {
        
            // Return the result
            return $this->result;
            
        }
    
    }
    
    // Function to use the query function and return whatever format the user requests
    public function getQuery($query = null, $output = OBJECT) {
    
        // If the query isn't null then perform a query
        if ($query) {
        
            // Create a query using the $query variable
            $sql = $query;
            $result = mysql_query($sql) or die (mysql_error());
            
            // Get the query and return the object
            if ($output == OBJECT) {
            
                $return = mysql_fetch_object($result);
                return $return;
            
            } else if ($output == ARRAY_A) {
                
                // Create an empty array to hold our data
                $returnarr = array();
            
                while ($return = mysql_fetch_array($result)) {
                
                    // Push all the data into the array
                    array_push($returnarr, $return);
                
                }
                
                // Return the array
                return $returnarr;
            
            }
        
        }
    
    }
    
    // Function to insert data into the given table
    public function insertRow($table, $insert) {
    
        // Create an empty sql variable
        $sql = "";
        
        // Add the insert into statement
        $sql .= "INSERT INTO `".$table."` VALUES (NULL, ";
        
        // Run through the insert array to build the sql
        foreach($insert as $data) {
        
            $sql .= "'".$data."', ";
        
        }
        
        // Remove the last comma
        $sql = substr_replace($sql, "", -2);
        
        // Append the close bracket
        $sql .= ")";
        
        // Run the query and make sure it return true
        if ($this->setQuery($sql, true)) {
            
            return true;
            
        } else {
        
            echo "An error occurred and your data was not written to the database";
            return false;
        
        }
    
    }
    
    // Function to update rows
    public function updateRow($table, $data, $where, $id) {
    
        // Create an empty sql variable
        $sql = "";
        
        // Append the sql statement with the table
        $sql .= "UPDATE `".$table."` SET ";
        
        // Run through the data array to append the required data
        foreach($data as $col => $update) {
        
            $sql .= $col." = '".$update."', ";
        
        }
        
        // Remove the last comma
        $sql = substr_replace($sql, "", -2);
        
        // Append the $id with a WHERE statement
        $sql .= " WHERE `".$where."` = '".$id."'";
        
        // Run the query and return either true or false depending on success
        if ($this->setQuery($sql, true)) {
        
            return true;
        
        } else {
        
            echo "An error occurred and your data was not written to the database";
            return false;
        
        }
    
    }
    
    // Function to return aa count of a particular table
    public function countThis($table) {
    
        // Create an sql statement using the $table variable
        $sql = "SELECT * FROM `".$table."`";
        
        // Run the query and return a count
        $result = $this->setQuery($sql);
        
        // Set up the count
        $count = mysql_num_rows($result);
        
        // Return the number of rows
        return $count;
    
    }
    
    // Destruct function
    public function __destruct() {
 
        return true;
    
    }
    
    // Function to connect using the constructor
    private function connectToDb() {
    
        $connect = mysql_connect($this->host, $this->user, $this->pass);
        
        if ($connect) {
        
            mysql_select_db($this->dbase) or die (mysql_error());
        
        } else {
        
            return "Failure to connect to database";
        
        }
    
    }
 
}
 
/**
 * If the $db variable has been set elsewhere then don't instantiate a class, otherwise set up a global variable
 * 
 * @var: $db
 * @desc: instantiates a connection to make it easier to run database functions
 */
if (!isset($tbdb)) {
 
    $db = new Connection;
 
}
 
?>
 
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Database Connection class that needs improving

Post by Christopher »

There are several of these Connection classes discussed in detail here in the Code Critique forum. Perhaps you could read those discussions and let us know specifically what questions you have about your design.
(#10850)
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Re: Database Connection class that needs improving

Post by daedalus__ »

stop

Code: Select all

 
define('OBJECT', 'OBJECT', true);
 
Post Reply