Database Class

[mattheww]

I'm fairly new to object orientated PHP

 

and I was trying to think of a better way to connect to a MySQLi database within classes and functions, instead of just putting down an include everywhere. 

 

So far I came up with this:

 

database.php

<?php

class database {
	
	protected $db_name = 'database';
	protected $db_user = 'root';        
	protected $db_password = 'password';
	protected $db_host = 'localhost';
	
	function __construct(){
		
		$this->connect();
		
	}

    public function connect(){

        $this->db = new mysqli($this->db_host, $this->db_user, $this->db_password, $this->db_name);

    }	
	
}

$db = new database;

?>

test.php

class test{

    private function getDb(){
		
        include("database.php");
	return $db;
		
    }
    public function test1(){
		
	$find = $this->getDb()->db->query("SELECT * FROM user");
	$rows = $find->num_rows;
		
	return $rows;
		
   }

}

and then just putting echo $test->test1(); on my page.

 

I did have other functions inside the database class, but I've since scrapped those, so it just looks like that atm. 

 

It does work... however, I'm not sure how good or bad it is. In particular the first line in the test1() function. I have a feeling its a terrible way to do it... Or is it fine? like efficiency wise etc? 

 

before I would just use an include within every function. so I'd just do: $db->query("  ");

 

Eventually I will have a few classes like user, etc, and these would all need to manipulate the database. So I was just thinking of plopping the getDb() function at the top of each of them and just include the database stuff... I tried looking at how phpBB and wordpress handle databases, but theirs are just full of unnecessary stuff for me and its just difficult to decipher. They use a global I think?

 

[dmcglone]

Most of the time I just use a function with a config file here's what I use most of the time, unless I decide to use PEAR, then things change

 

 

 
<?php
 $conn= mysqli_connect(DB_HOST,DB_USERNAME,DB_PASSWORD);
    // test the connection
    if(!$conn){
      die ("Cannot connect to the database");
    }//endif
    else{
      $myconn = $conn;
}//end else
    return $myconn;

    //begin select database function
       function selectDatabase(){
         mysqli_select_db(DB_DATABASE);
      if(mysqli_error()){
    echo "echo cannot find the database ";
      }
//       echo "Database " .$this->database. " selected <p />";
    }//end selectDatabase function

//config.php file
 
// database login info
define("DB_HOST", "localhost");
define("DB_USERNAME", "myusername");
define("DB_PASSWORD", "mypassword");
define("DB_DATABASE", "mydatabase");
define("MYSQL_CONNECTION_STRING", "mysqli://" . DB_USERNAME . ":" . DB_PASSWORD . "@" . DB_HOST . "/" . DB_DATABASE);
?>

[Strider64]

This is what I do, but I'm sure a guru here will have a better way of doing this.   Though it works for me.

 

I have a file for my database constant variables called common.php

<?php

define('DB_HOST', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '****');
define('DB_NAME', 'cart_db');

my connection class:

class DatabaseConnection {
     protected static function connect() {
            $database = new mysqli(DB_HOST,DB_USERNAME,DB_PASSWORD, DB_NAME);    
        return $database;
      }             
}

Where I grab my data class

class DatabaseData extends DatabaseConnection {
    
     public $products = array();
    
      public function shopping_cart_data()
     {
         $database = parent::connect(); //Connects to the mysqli Database                     
        
        $query = "SELECT CONCAT('A00', id) AS id, description, cost, qty_on_hand FROM shopping_cart ORDER by id ASC";
        $result = $database->query($query);
        
        while ($page = $result->fetch_array(MYSQLI_ASSOC)) {              
           $this->products[] = new Item($page['id'], $page['description'], $page['cost'], $page['qty_on_hand']);                      
        }
        /* free result set */
        $result->free();

         $databaseClose = parent::close_connection();         
        
        return $this->products;
        
     }          
}

[InoBB]

You should look into prepared statements. http://us1.php.net/manual/en/mysqli.prepare.php

And possibly PDO if you want your script to be able to run different databases, just incase you ever wanna switch to new host  http://us1.php.net/manual/en/book.pdo.php

 

Prepared statements in MySQL are already a pre-defined function to use at your disposal. No need to re-invent the wheel. 

 

common.php:

<?php

define('DB_HOST', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '****');
define('DB_NAME', 'cart_db');

$database = new MySQLi(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);

When you need to make your query:

$sql = "SELECT CONCAT('A00', id) AS id, description, cost, qty_on_hand FROM shopping_cart ORDER by id ASC";
$query = $database->prepare($sql);
$query->bind_result($id, $desc, $cost, $qty);
$query->execute();
$query->store_result();
while ($query->fetch()) {
    // run script to loop through table
}
$query->free_result();
$query->close();

Edited May 1, 2013 by InoBB

[ignace]

Writing your own database class is nice as an experiment. When you are building software for a client however it's better to use battle-tested code as to avoid bugs and security issues.

 

In general I try to find open-source tested code for any boilerplate tasks in a project so that I can focus on the hard, more interesting stuff of the application and not bother myself with how I would want to write my database class or my authentication handler.

[rama schneider]

Here's a battle tested database class I wrote a long time ago and have updated from time to time - currently only designed to work with MySQL, but if I need to access another type of database I can use this as a framework. The downside is that the queries are constructed by the calling function, and then sent along with the actual arguments so I can be sure everything is made safe (see function query).

 

I use classes in almost every instance to act as the go between - the consuming function will call a class/object function that will make the DB call. This means any restructuring of SQL needs to be done in classes.

 

In practice one can create one instance each of the admin, read/write and read only access - this saves a lot of overhead if one is using the same database object to access the database.

 

As follows:

 

<?
/**
    DB provides an object oriented access to any given database. Current only supports MySQL databases.
*/
    class DB {
        // Configuration constants
        // Full access
        private $DATABASE_HOST_ADMIN = 'localhost';
        private $DATABASE_NAME_ADMIN = 'username_primary';
        private $DATABASE_USER_ADMIN = 'username_admin';
        private $DATABASE_USER_PW_ADMIN = 'password';
        // read/write (no table creation/alter/etc) access
        private $DATABASE_HOST_READWRITE = 'localhost';
        private $DATABASE_NAME_READWRITE = 'username_primary';
        private $DATABASE_USER_READWRITE = 'username_rw';
        private $DATABASE_USER_PW_READWRITE = 'password';
        // read only access
        private $DATABASE_HOST_READONLY = 'localhost';
        private $DATABASE_NAME_READONLY = 'username_primary';
        private $DATABASE_USER_READONLY = 'username_ro';
        private $DATABASE_USER_PW_READONLY = 'password';
        // Instance variables
        private $DB;
        
        private static $AdminDB = false;
        private static $ReadWriteDB = false;
        private static $ReadOnlyDB = false;
        
        // Public factory methods
        public static function getAdminAccess() {
            if(DB::$AdminDB === false)
            {
                DB::$AdminDB = new DB(array('type' => 'admin'));
            }
            return DB::$AdminDB;
        }
        public static function getReadWriteAccess() {
            if(DB::$ReadWriteDB === false)
            {
                DB::$ReadWriteDB = new DB(array('type' => 'readwrite'));
            }
            return DB::$ReadWriteDB;
        }
        public static function getReadOnlyAccess() {
            if(DB::$ReadOnlyDB === false)
            {
                DB::$ReadOnlyDB = new DB(array('type' => 'readonly'));
            }
            return DB::$ReadOnlyDB;
        }
        
        private function get_errno() {
            if($this->RealErrors) {
                return $this->DB->errno;
            } else {
                if($this->DB->errno) {
                    return -1;
                } else {
                    return 0;
                }
            }
        }
        private function get_error() {
            if($this->RealErrors) {
                return $this->DB->error;
            } else {
                if($this->DB->errno) {
                    return 'There was a problem submitting the information.';
                } else {
                    return '';
                }
            }
        }
        
        public $RealErrors = false;
        
        /**
            * $in = named/value pair array containing:
            *        'type' = 'admin' for administrative (full) access,
                                     'readwrite' for read/write without table creation/deletion/alteration/etc access
                                     'readonly' for read only access
            */
        private function __construct($in = array()) {
            extract($in);
            switch(strtolower($type)) {
                case 'admin':
                    $this->DB = new mysqli($this->DATABASE_HOST_ADMIN, $this->DATABASE_USER_ADMIN, $this->DATABASE_USER_PW_ADMIN, $this->DATABASE_NAME_ADMIN);
                    break;
                case 'readwrite':
                    $this->DB = new mysqli($this->DATABASE_HOST_READWRITE, $this->DATABASE_USER_READWRITE, $this->DATABASE_USER_PW_READWRITE, $this->DATABASE_NAME_READWRITE);
                    break;
                case 'readonly':
                    $this->DB = new mysqli($this->DATABASE_HOST_READONLY, $this->DATABASE_USER_READONLY, $this->DATABASE_USER_PW_READONLY, $this->DATABASE_NAME_READONLY);
                    break;
                default:
                    $this->DB = false;
                    break;
            }
            return $this->DB ? true : false;
        }
        
        public function __destruct() {
            // Make sure the database is closed.
            $this->DB->close();
        }
        
        public function __get($n) {
            // Retrieve various variables from the underlying MySQL database object.
            switch($n) {
                case 'affected_rows':
                    return $this->DB->affected_rows;
                    break;
                case 'insert_id':
                    return $this->DB->insert_id;
                    break;
                case 'ErrorArray':
                    return array('errno' => $this->DB->errno, 'error' => $this->DB->error);
                    break;
                case 'errno':
                    return $this->get_errno();
                    break;
                case 'error':
                    return $this->get_error();
                    break;
                default:
                    break;
            }
        }
    
        /**
        Input sql may contain one or more '![number]!', and if it does this/these instance(s) are replaced by the
        value contained in the params array. Example: !0! will be replaced with the first value in the input array,
        !1! by the second and so on.
        */    
        public function query($sql,$params = array(),$debug = false) {
            $search = array();
            for($x = 0; $x < sizeof($params); $x++) {
                $search[] = '!' . $x . '!';
                $params[$x] = $this->DB->real_escape_string($params[$x]);
            }
            $sql = str_ireplace($search, $params, $sql);
            if($debug) {
                // print_r(debug_backtrace());
                print '<div style="border:solid 1px green;padding:5px;">' . $sql . '</div>';
            }
            return $this->DB->query($sql);
        }
        
        /**
        Same as above, but suitable for multiple queries on one line.
        */
        public function multiQuery($sql,$params = array(),$debug = false) {
            $search = array();
            for($x = 0; $x < sizeof($params); $x++) {
                $search[] = '!' . $x . '!';
                $params[$x] = $this->DB->real_escape_string($params[$x]);
            }
            $sql = str_ireplace($search, $params, $sql);
            if($debug) {
                // print_r(debug_backtrace());
                print '<div style="border:solid 1px green;padding:5px;">' . $sql . '</div>';
            }
            return $this->DB->multi_query($sql);
        }

        /**
        These are needed to handle multi_query requests. All results must be consumed in one manner or
        another or else an out of synch error will be thrown. The two methods that consume a result set
        are storeResult and useResult.
        */
        public function moreResults()
        {
            return $this->DB->more_results();
        }
        
        public function nextResult()
        {
            return $this->DB->next_result();
        }
    
        public function storeResult()
        {
            return $this->DB->store_result();
        }
        
        public function useResult()
        {
            return $this->DB->use_result();
        }

    }

Edited May 2, 2013 by ignace
Added code tags

[trq]

Sorry, but the idea of editing the class to simply change connection settings stinks.

[rama schneider]

Sorry, but the idea of editing the class to simply change connection settings stinks.

No it doesn't. This class is used in working environments that are designed for experienced programmers, and it makes a lot of sense. If, on the other hand, one doesn't like that approach, then one can very easily change that by using whatever method one feels comfortable with - that's the wonderful thing about being able to write the code in the class.

 

The database settings will always have to be input somewhere - for simplicity's sake I put it where it is needed.

[mac_gyver]

the connection details for any database connection (or how many different simultaneous database connections there are) is application level information. remembering or finding each class file that has application level information hard-coded in them to edit just because you moved where code is being ran at, is brute-force time-wasting make-work programming.

 

by hard-coding where connection details are stored at (an include file name) or directly what the connection details are (external-variables/defined constants or local assignment statements) or using a static method of another class, inside of a class, you cannot use the class for more than one database connection at a time because multiple instances of the class all use the same database connection information. if your solution to this would be to copy/paste the class to a different name or to shuffle-around (making,overwriting,remaking) instances of a class, that's most definitely NOT the point of using classes for anything.

 

dependency injection should be used to get application level information into any instance of a class.

[ignace]

No it doesn't. This class is used in working environments that are designed for experienced programmers.

Clearly someone needs to explain to you the principles of OO programming. Ever heard of a concept called reusability? The whole point of not changing your class from MySQL to some other vendor but instead use polymorphism for that is that you could break existing functionality.

 

What would you do for example if some part of your app. uses MySQL (front-end for example) and the other uses MSSQL due to some legacy system that is build upon MSSQL?

 

I highly suggest you take a look at Zend\Db which allows you to have a Slave/Master setup like what you are trying to do with the ADMIN, READWRITE, READONLY thingy.

Edited May 2, 2013 by ignace

[KevinM1]

Put more succinctly: if you have to continue changing your class code just so it can work in different environments, you're doing it wrong.

[rama schneider]

Understand I didn't write the above class or the extensive PHP framework I've built as a general catch all - it was designed from the get go with two major principals: 1) It is designed for LAMP, and 2) it is designed for experienced programmers. I did this for a number of reasons, but the primary one is that although I don't in general care if my code gets copied (I would certainly note otherwise where applicable) I'm not writing for the general public.

 

What I have is secure, easily understood and meets the needs of clients.

 

I went this route because I kept finding folks who were not comfortable for whatever reason with the general offerings such as Drupal or Wordpress. I am able to condense things such as backend admin functionality to whatever is needed and not one character more. I can write sites that provide a lot of functionality with very little overhead.

 

Does it meet the test of object oriented programming? Absolutely. Does it meet the test of poly-morphism? No, but I'm not concerned with that. As pointed out above I would have to create another class to work with an MS-SQL server - not a big deal.

 

Look - I just presented the above class as an example of what can be done. That is a class that is running database operations on many sites today - it works, it is secure, and I have intimate knowledge of it's innards. That's what I need to please my clients.

 

So I take the criticism that it isn't a complete catchall for everything to heart - and I agree. But the comments regarding not being done correctly just because it isn't ready for every environment is way off base.

[KevinM1]

Does it meet the test of object oriented programming? Absolutely. Does it meet the test of poly-morphism? No, but I'm not concerned with that.

Except, polymorphism is a key component of object orientation. It facilitates modularity and extensibility.

 

But the comments regarding not being done correctly just because it isn't ready for every environment is way off base.

Not really.

 

Just because you use objects, and just because it works, doesn't mean it's well designed. And, realize that the criticism isn't that it's not 100% platform agnostic out of the box, but rather it's designed in a way that makes it relatively difficult to expand beyond using a single database, or expand beyond using MySQL.

 

Ultimately, since we strive to teach best practices here, it's not an example we can support or endorse.

[InoBB]

Anyways, if this is your way of doing, then that it is.

To answer your question though

 

It does work... however, I'm not sure how good or bad it is. In particular the first line in the test1() function. I have a feeling its a terrible way to do it... Or is it fine? like efficiency wise etc? 

 

No need to call the file to include inside the function if you set the database connection with constants.