3 Replies - 1659 Views - Last Post: 16 July 2010 - 01:43 PM

#1 joeyadms   User is offline

  • D.I.C Head
  • member icon

Reputation: 41
  • View blog
  • Posts: 178
  • Joined: 04-May 08

MYSQL Abstraction Layer

Posted 07 May 2008 - 09:50 AM

Description: First, setup config options, and connection details. Then initialize connection through db::getInstance() , you can also pass a string to use a database other than in connection info.

Below is example of use:

$db = db::getInstance('database');

$var = $_POST['var'];

$array = array('one' => 1, 'two' => 2);

$clean_array = $db->prepare($array);
$clean_var = $db->prepare($var);

$query = "SELECT * FROM `test` WHERE `id`='$clean_var'";
$rows = $db->query($query);

echo $rows['username'];
Use this Abstraction layer to keep your business logic seperate.

Change Options Below for either mysql or mysqli drivers.

By Default query() returns Associative array, turn off in options to return resource.

Free to use and modify to your liking, just keep this comment block here.

Make sure you run your vars through prepare() before using them in query() to protect
against SQL Injection.
<?php
/**
 * @name MYSQL Database Abstraction Layer
 * @author Joey Adams
 * @version 1.0
 * @uses 
 * Use this Abstraction layer to keep your business logic seperate.
 * 
 * Change Options Below for either mysql or mysqli drivers.
 * 
 * By Default query() returns Associative array, turn off in options to return resource.
 * 
 * Free to use and modify to your liking, just keep this comment block here.
 * 
 * Make sure you run your vars through prepare() before using them in query() to protect
 * against SQL Injection.
 * 
 * @example  
 * 
 * $db = db::getInstance('database');
 * 
 * $var = $_POST['var'];
 * 
 * $array = array('one' => 1, 'two' => 2);
 * 
 * $clean_array = $db->prepare($array);
 * $clean_var = $db->prepare($var);
 * 
 * $query = "SELECT * FROM `test` WHERE `id`='$clean_var'";
 * $rows = $db->query($query);
 * 
 * echo $rows['username'];
 *
 * @copyright GPL (http://www.gnu.org/licenses/gpl.txt)
 *   This program is free software: you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation, either version 3 of the License, or
 *   (at your option) any later version.
 *
 *   This program is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *   GNU General Public License for more details.
 *
 *   You should have received a copy of the GNU General Public License
 *   along with this program.  If not, see <http://www.gnu.org/licenses/>
 *
 */


class DB {
	
	// Options
	/**
	 * Set MYSQL Driver
	 * 0 = MYSQL
	 * 1 = MYSQLI
	 * 
	 * @var int driver
	 */
	private $driver = 1;
	/**
	 * Query Results Return Associative Array
	 * 
	 * 0 = No, return Resource
	 * 1 = Yes, return Associative Array
	 *
	 * @var int return_assoc
	 */
	private $return_assoc = 1; 
	//-- End Options --
	
	
	// Connection Info
	/**
	 * MYSQL Server Host
	 * @var str host
	 */
	private $host = "localhost";
	/**
	 * MYSQL Username
	 * @var str username
	 */
	private $username = "username";
	/**
	 * MYSQL Password
	 * @var str password
	 */
	private $password = "password";
	/**
	 * MYSQL Default Database 
	 * Set this to default db to use, although you can
	 * pass a db name to getInstance() to use different one
	 *
	 * @var str def_db
	 */
	private $def_db = "test";
	//-- End Connection Info --
	
	
	/**
	 * 
	 * 
	 * Edit Below This line at your own risk
	 * 
	 * 
	 */
	
	
	
	
	/**
	 * MYSQL Connection Link
	 * Holds Link for connection for functionality
	 * 
	 * @var static MYSQL_CONNECTION
	 */
	private static $_link;
	
	/**
	 * Establish MYSQL Connection
	 * with either mysql, or mysqli driver
	 * based on config.
	 * 
	 * Accepts 1 param for database name,
	 * If null, uses self::def_db
	 *
	 * @param str $db
	 */
	public function __construct($db = null){
		if(!is_null($db)){
			$this->def_db = $db;
		}
		if($this->driver){
			$this->_link = new mysqli($this->host,$this->username,$this->password,$this->def_db);
		} else {
			$this->_link = mysql_connect($this->host,$this->username,$this->password);
			mysql_select_db($this->def_db,$this->_link);
		}
	}
	
	/**
	 * Closes MYSQL Connection
	 *
	 */
	public function __destruct(){
		if($this->driver){
			$this->_link->close();
		} else {
			mysql_close($this->_link);
		}
	}
	
	/**
	 * Returns instance of MYSQL
	 * 
	 * Use this to init DB(), or to 
	 * get an instance of the connection.
	 * 
	 * Makes sure only 1 persistent connection
	 * is maintained.
	 * 
	 * Pass a string when initializing if you want
	 * to use a different database than in config.
	 *
	 * @param str $db
	 * @return MYSQL_CONNECTION
	 */
	public function getInstance($db = null){
		if(!self::$_link){
			return new DB($db);
		} else {
			return self::$_link;
		}
	}
	
	/**
	 * Wrapper for Real_Escape_String
	 * 
	 * Accepts either array, or string
	 * and returns escaped vars.
	 * 
	 * Use this before query() on vars to
	 * protect from sql injection
	 *
	 * @param str,array $var
	 * @return str,array $clean
	 */
	public function prepare($var){
		if(is_array($var)){
			if($this->driver){
				foreach($var as $key=>$val){
					$clean[$key] = $this->_link->real_escape_string($val);
				}
			} else {
				foreach($var as $key=>$val){
					$clean[$key] = mysql_real_escape_string($val,$this->_link);
				}
			}
		} else {
			if($this->driver){
				$clean = $this->_link->real_escape_string($var);
			} else {
				$clean = mysql_real_escape_string($var,$this->_link);
			}
		}
		return $clean;
	}
	
	/**
	 * Performs Query Against Database
	 * 
	 * Returns ResultSet or Assoc Array 
	 * depending on config above
	 *
	 * @param str $query
	 * @return MYSQL_RESULT,ASSOC_ARRAY
	 */
	public function query($query){
		if($this->driver){
			$resultSet = $this->_link->query($query);
			if($this->return_assoc){
				while($result = $resultSet->fetch_assoc()){
					foreach($result as $key=>$val){
						$returnResult[][$key] = $val;
					}
				}	
			} else {
				$returnResult = $resultSet;
			}
		} else {
			$resultSet = mysql_query($query,$this->_link);
			if($this->return_assoc){
				while($result = mysql_fetch_assoc($resultSet)){
					foreach($result as $key=>$val){
						$returnResult[][$key] = $val;
					}
				}	
			} else {
				$returnResult = $resultSet;
			}
		}
		return $returnResult;
	}
	
}


Is This A Good Question/Topic? 0
  • +

Replies To: MYSQL Abstraction Layer

#2 joeyadms   User is offline

  • D.I.C Head
  • member icon

Reputation: 41
  • View blog
  • Posts: 178
  • Joined: 04-May 08

Re: MYSQL Abstraction Layer

Posted 14 May 2008 - 09:14 AM

NEW:::: Now captures errors, and most importantly, adds object return functionality. See Code for details.
Was This Post Helpful? 0
  • +
  • -

#3 joeyadms   User is offline

  • D.I.C Head
  • member icon

Reputation: 41
  • View blog
  • Posts: 178
  • Joined: 04-May 08

Re: MYSQL Abstraction Layer

Posted 17 May 2008 - 04:44 PM

Now added, asXML(), extending plugins, multiple connection handling etc... SEE http://www.bin.joeya...t/mysql_ab_pro/ For Documentation!!!!
Was This Post Helpful? 0
  • +
  • -

#4 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4208
  • View blog
  • Posts: 13,283
  • Joined: 08-June 10

Re: MYSQL Abstraction Layer

Posted 16 July 2010 - 01:43 PM

you may consider using the Registry Pattern (or a variation thereof) instead of the Singleton Pattern.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1