The MySQL Database class is implemented using the singleton design pattern. This design pattern allows for only one single instance of the object. This one instance can then be obtained through a static method. This provides a convenience in that you can get your database object from anywhere in your code.
Some people may cry that a singleton acts very much like a global (without the name conflict problem). However, using singleton pattern for your database interfacing is convenient and popular. Of course, since there can be only one instance of the database object, this class only works in situations in which you need only one database connection at a time and you aren’t doing connection pooling (unless you would like to modify this code to support pooling). This is the case for most of us most of the time.
And example of obtaining an instance of the database object:
$db = Database::obtain();
The class contains some very useful functions including a great error output function that catches and displays a more verbose output of any MySQL errors. You can actually turn this off when in production mode to keep from any important and accidental information about your database structure from being exposed to untrusted visitors.
Here is the class:
<?php
/**
Name: mysqlDatabaseEngine.php
Description: MySQL Singleton Class to allow easy and clean access to common mysql commands
Author: Mike Frank - Swhistlesoft.com
Update: 2011-02-16
Version: 2.0.1
Copyright 2011 swhistlesoft.com
USAGE:
//require("config.inc.php");
//$db = Database::obtain(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);
//$db = Database::obtain();
**/
class Database {
// Debug flag for showing error messages
public $debug = true ;
// Store the single instance of the Database
private static $instance ;
// Server connection parameters
private $server = "" ;
private $user = "" ;
private $pass = "" ;
private $database = "" ;
// Database Error
private $error = "" ;
// Number of rows affected by SQL query
public $affected_rows = 0 ;
// Link and Query resource variables
private $link_id = 0 ;
private $query_id = 0 ;
/**
Constructor
**/
private function __construct( $server=null, $user=null, $pass=null, $database=null ) {
if( $server==null || $user==null || $pass==null || $database==null ) {
$this->oops( "Database information must be passed in when the object is first created." ) ;
}
$this->server = $server ;
$this->user = $user ;
$this->pass = $pass ;
$this->database = $database ;
}// END CONSTRUCTOR
/**
Singleton Declaration
**/
public static function obtain( $server=null, $user=null, $pass=null, $database=null ) {
if( !self::$instance) {
self::$instance = new Database( $server, $user, $pass, $database ) ;
}
return self::$instance ;
}// END SINGLETON DECARATION
/**
Connect and select database
**/
public function connect( $new_link=false ) {
$this->link_id = @mysql_connect( $this->server, $this->user, $this->pass, $new_link ) ;
if( !$this->link_id) {
$this->oops( "Could not connect to the MySQL Database Server: <b>$this->server</b>.") ;
}
if( !@mysql_select_db( $this->database, $this->link_id ) ) {
$this->oops( "Could not open database: <b>$this->database</b>.") ;
}
// Reset connection data so it cannot be dumped
$this->server = "" ;
$this->user = "" ;
$this->pass = "" ;
$this->database = "" ;
}// END CONNECTION
/**
Close the connection to the database
**/
public function close( ) {
if( !@mysql_close( $this->link_id ) ) {
$this->oops( "Connection close failed!" ) ;
}
}// END CLOSE
/**
Escapes characters to be mysql ready
**/
public function escape( $string ) {
if( get_magic_quotes_runtime( ) ) $string = stripslashes( $string ) ;
return @mysql_real_escape_string( $string, $this->link_id ) ;
}// END ESCAPE
/**
Executes a SQL query to an open connection
**/
public function query( $sql ) {
$this->query_id = @mysql_query( $sql, $this->link_id ) ;
if( !$this->query_id ) {
$this->oops( "<b>MySQL Query Failed:</b> $sql" ) ;
return 0 ;
}
$this->affected_rows = @mysql_affected_rows( $this->link_id ) ;
return $this->query_id ;
}// END QUERY
public function insert_id( ) {
return mysql_insert_id($this->link_id);
}
/**
Execute a SQL Query and returns only the first row, frees resultset
**/
public function query_first( $sql ) {
$query_id = $this->query( $sql ) ;
$out = $this->fetch( $query_id ) ;
$this->free_result( $query_id ) ;
return $out ;
}// END FETCH SINGLE
/**
Execute a SQL Query and returns results one line at a time
**/
public function fetch( $query_id=-1 ) {
if( $query_id != -1 ) {
$this->query_id = $query_id ;
}
if( isset( $this->query_id ) ) {
$record = @mysql_fetch_assoc( $this->query_id ) ;
} else {
$this->oops( "Invalid query_id: <b>$this->query_id</b>. Records could not be fetched." );
}
return $record ;
}// END FETCH
/**
Return all results in array format
**/
public function fetch_array( $sql ) {
$query_id = $this->query( $sql ) ;
$out = array ( ) ;
while( $row = $this->fetch( $query_id ) ) {
$out [] = $row ;
}
$this->free_result( $query_id ) ;
return $out ;
}// END FETCH ARRAY
/**
Execute an update query with an array
**/
public function update( $table, $data, $where='1' ) {
$q = "UPDATE `$table` SET " ;
foreach( $data as $key=>$val ) {
if( strtolower( $val ) == 'null' ) $q .= "`$key` = NULL, " ;
elseif( strtolower( $val ) == 'now()') $q .= "`$key` = NOW(), " ;
elseif(preg_match("/^increment\((\-?\d+)\)$/i",$val,$m)) $q.= "`$key` = `$key` + $m[1], ";
else $q.= "`$key`='".$this->escape($val)."', ";
}
$q = rtrim( $q, ', ') . ' WHERE '.$where.';' ;
return $this->query( $q ) ;
}// END UPDATE
/**
Execute an insert query with an array
**/
public function insert( $table, $data ) {
$q="INSERT INTO `$table` ";
$v=''; $n='';
foreach($data as $key=>$val){
$n.="`$key`, ";
if(strtolower($val)=='null') $v.="NULL, ";
elseif(strtolower($val)=='now()') $v.="NOW(), ";
else $v.= "'".$this->escape($val)."', ";
}
$q .= "(". rtrim($n, ', ') .") VALUES (". rtrim($v, ', ') .");";
if($this->query($q)){
return mysql_insert_id($this->link_id);
}
else return false;
}// END INSERT
/**
Frees Resultset
**/
private function free_result( $query_id=-1 ) {
if ($query_id!=-1){
$this->query_id=$query_id;
}
if($this->query_id!=0 && !@mysql_free_result($this->query_id)){
$this->oops("Result ID: <b>$this->query_id</b> could not be freed.");
}
}// END FREE RESULTSET
/**
Throws an Error Message only if debug flag is true
**/
private function oops( $msg = '' ) {
if(!empty($this->link_id)){
$this->error = mysql_error($this->link_id);
}
else{
$this->error = mysql_error();
$msg="<b>WARNING:</b> No link_id found. Likely not be connected to database.<br />$msg";
}
// if no debug, done here
if(!$this->debug) return;
?>
<table align="center" border="1" cellspacing="0" style="background:white;color:black;width:80%;">
<tr><th colspan=2>Database Error</th></tr>
<tr><td align="right" valign="top">Message:</td><td><?php echo $msg; ?></td></tr>
<?php if(!empty($this->error)) echo '<tr><td align="right" valign="top" nowrap>MySQL Error:</td><td>'.$this->error.'</td></tr>'; ?>
<tr><td align="right">Date:</td><td><?php echo date("l, F j, Y \a\\t g:i:s A"); ?></td></tr>
<?php if(!empty($_SERVER['REQUEST_URI'])) echo '<tr><td align="right">Script:</td><td><a href="'.$_SERVER['REQUEST_URI'].'">'.$_SERVER['REQUEST_URI'].'</a></td></tr>'; ?>
<?php if(!empty($_SERVER['HTTP_REFERER'])) echo '<tr><td align="right">Referer:</td><td><a href="'.$_SERVER['HTTP_REFERER'].'">'.$_SERVER['HTTP_REFERER'].'</a></td></tr>'; ?>
</table>
<?php
}// END OOPS
}
?>
To use this class, all you need to do is include the class file at the top of your scripts and then obtain an instance to the object. Once you have obtained an instance you can start querying the database.
Here is a quick example:
$db = Database::obtain(); // The database connection information is defined in a global define file as constants
$ret = $db->fetch_array( "SELECT `name`, `key`, `value` FROM `some_table` WHERE `key`=1000;" ) ;
for( $i = 0, $tCnt = count( $ret ) ; $i < $tCnt ; $i ++ ) {
echo $ret[ $i ]["name"] . " - " . $ret[ $i ]["key"] . " : " . $ret[ $i ]["value"] . "<br />" ;
}
hello mfrank,
thanks for beautiful class, but the affected_rows works only with insert, update, delete queries.
so with select queries it will not return the correct number.
i need it for using this class with a paging class.
how can i solve this problem ?
thanks again
Hello Kairm,
The query you would probably be interested in is something like:
SELECT SQL_CALC_FOUND_ROWS id, [other columns go here] FROM [some_table_name] LIMIT [start],[page_size]
followed by
SELECT FOUND_ROWS() as rows
This should give you better performance than your typical:
SELECT COUNT(id) FROM [some_table]
Let me know how you make out.
Thanks,
Hi,
I dont see you using connect method anywhere in the class. should not you call it once you got the instance of object