PHP MySQL wrapper v3 - PHP5 Singleton Class to easily call mysql functions

Description
  • This is not a full script. This is the PHP singleton class (PHP5 specifically) that I use to make all the MySQL database calls in my projects. I run all my MySQL connections through it because it saves me time and space on my code. I don't have to keep typing the database information and other things like that. It also has shortcut functions because I got tired of escaping data, stripping slashes, building UGLY insert and update queries. I'm sharing this class along with some examples for using it.

What is a Singleton and why use it?
  1. A Singleton in PHP allows you to create a single object ($db) and reuse that same existing object anywhere in your code.
  2. This allows you to easily reuse the same database connection inside of other functions/classes. Without a singleton, your options would be to either declare the $db object as a global inside every function, or pass the $db object into the function via a parameter. A Singleton object eliminates all this.

Download

Requirements & Terms of Use
  • This script requires PHP5.0 or higher. If you only have PHP4, you need to use the old PHP MySQL Wrapper v2.
  • All current v3 of this script are released under GNU General Public License. Basically this means you are free to use the script, modify it, and even redistribute versions of your own under the same license.

What's new
  • 3.1.4 [2010-07-19] [credits] Removed if($pass==null) check in constructor incase logging into mysql with blank password
  • 3.1.3 [2010-05-09] First public release. There are older v3 versions, but they were not publicly available
  • Changed from v2
    • Minor: Ported to PHP5 supported OOP (meaning private, public, "__construct" etc)
    • Major: Turned the class into a singleton
    • Major: Renamed several functions: fetch_array() to fetch(), fetch_all_array() to fetch_array(), query_update() to update(), query_insert() to insert()
    • Minor: Added $debug=false; to allow suppression of oops() error messages
    • Minor: Completely stripped out support for table prefixes

Instructions

Calling the Class and configuring the settings
  • At the top of your scripts you need to create the initial $db object and pass it all the MySQL server parameters:
    // bring in the class file
    require("Database.singleton.php");
    // create the initial singleton database object
    $db = Database::obtain("server_name", "mysql_user", "mysql_pass", "mysql_database");
  • What each setting means:
    • $db = Database::obtain is how you are going to declare and access the singleton object. You are probably used to declaring an object like $db = new Database however, we are not creating a traditional object. Also note many people like to use the naming convention ::getInstance() instead of ::obtain() and you are welcome to change your version. However, I personally like ::obtain() so it's used here.
    • server_name - Name or IP of MySQL server. Usually "localhost" although sometimes it will be like "db1337.perfora.net"
    • mysql_user - User Name to log into MySQL database
    • mysql_pass - Password to log into MySQL database. If no password, use double quotes with a blank value. Example ""
    • mysql_database - Database to select and run the queries on
  • What I HIGHLY recommend (optional)
    The easiest way to handle this is to create a config.inc.php file. In it put:
    //database server
    define('DB_SERVER', "localhost");

    //database login name
    define('DB_USER', "username");
    //database login password
    define('DB_PASS', "password");

    //database name
    define('DB_DATABASE', "username_mydata");

    //smart to define your table names also
    define('TABLE_USERS', "users");
    define('TABLE_NEWS', "news");

    Then at the top of your scripts, call in the config.inc.php and the Database.singleton.php files and then create the $db object
    require("config.inc.php");
    require("Database.singleton.php");
    $db = Database::obtain(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);

    The reason why this is a good idea is because then if you change hosts etc, you can edit the single config.inc.php file, change the settings, and it is reflected on all your pages. No need to change the info on each of your pages.

  • Any place (other than the initial declaration) you want to use the existing object in your script:
    // grab the existing $db object
    $db=Database::obtain();
    You do not need to pass it any of the database info (user/pass/server) except the very first time when you create the $db object.

Turning debug on/off
  • Near the top of Database.singleton.php setting there is a public $debug = true; setting. Setting this to false will suppress any error messages triggered by oops(). It's there to help with security, but I recommend leaving it on until you are absolutely sure everything is working correctly.
  • It would also be possible to turn debugging on (or off) for a specific page by calling $db->debug = true; up at the top of the page right after you initially create the $db object.

Examples of how to call things

Code:
// every page needs to start with these basic things

// I'm using a separate config file. so pull in those config values
require("config.inc.php");

// pull in the file with the database class
require("Database.singleton.php");

// create the $db singleton object
$db = Database::obtain(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);

// connect to the server
$db->connect();


#####
// your main code would go here
// with the singleton you can use the $db object inside other classes or functions
function get_user_exists($user){
    
    // get the already existing instance of the $db object
    $db = Database::obtain();

    $sql="SELECT `uid` FROM `account` WHERE `user`='".$db->escape($user)."'";
    $row = $db->query_first($sql);
    
    // if user exists
    if(!empty($row['uid']))
        return true;
    else
        return false;
}
#####


// good practice to close the connection when finished
// however, PHP will auto-close it when the page ends if you forget
$db->close();
Code:
// update an existing record using update()

$data['comments'] = 3536;
$data['title'] = "New Article";

// special cases supported for update: NULL and NOW()
$data['author'] = "NULL";// it knows to convert NULL and NOW() from a string

// also supports increment to the current database value
// will also work with a negative number. eg; INCREMENT(-5)
$data['views'] = "INCREMENT(1)";

// update() parameters
//     table name (ideally by calling a constant defined in config file)
//     assoc array with data (does not need escaped)
//     where condition
$db->update(TABLE_NEWS, $data, "news_id='46'");

// would create the query:
// UPDATE `news` SET `comments`='3536', `title`='New Article', 
//     `author`=NULL, `views`=`views` + 1 WHERE news_id='46'
Code:
// insert a new record using insert()

$data['news_id'] = 47;
$data['title'] = "You're Top"; // insert() will auto escape it for us
$data['created'] = "NOW()";// it knows to convert NULL and NOW() from a string

// insert() parameters
//     table name (ideally defined as a constant, but did not for this example)
//     assoc array with data (does not need escaped)
// insert() returns
//    primary id of the inserted record. you can collect or ignore
$primary_id = $db->insert("news", $data);

// then use the returned ID if you want
echo "New record inserted: $primary_id"; 

// would create the query:
// INSERT INTO `news` (`news_id`,`title`,`created`) 
//          VALUES ('47', 'Your\'re Top', NOW())
Code:
// escape() query() and fetch()

// pullout the first 10 entries where referrer came from google
//     using defined TABLE_USERS table name from config
//     $db->escape() escapes string to make it safe for mysql

$url = "http://www.google.com/";

$sql = "SELECT user_id, nickname FROM `".TABLE_USERS."`
          WHERE referrer LIKE '".$db->escape($url)."%'
          ORDER BY nickname DESC
          LIMIT 0,10";

$rows = $db->query($sql);

while ($record = $db->fetch($rows)) {
    echo "<tr><td>$record[user_id]</td>
          <td>$record[nickname]</td></tr>";
}
Code:
// using escape() and fetch_array()

// pullout the first 10 entries where url came from google
//     using defined TABLE_USERS table name from config
//     $db->escape() escapes string to make it safe for mysql

$url = "http://www.google.com/";

$sql = "SELECT user_id, username FROM `".TABLE_USERS."`
          WHERE referer LIKE '".$db->escape($url)."%'
          ORDER BY username DESC
          LIMIT 0,10";

// feed it the sql directly. store all returned rows in an array
$rows = $db->fetch_array($sql);


// print out array later on when we need the info on the page
foreach($rows as $record){
    echo "<tr><td>$record[user_id]</td>
          <td>$record[username]</td></tr>";
}
Code:
// using query_first() 

// get user's nickname using their unique ID
//    using defined TABLE_USERS table name from config

$sql = "SELECT username FROM `".TABLE_USERS."`
          WHERE user_id=$user_id";

// since user_id is unique, only one record needs returned
//     I use $db->query_first() instead of $db->query() and fetch()
//     $db->query_first() will return array with first record found
$record = $db->query_first($sql);

// since it only returns one record, query_first() does the fetching
// I can print off the record directly
echo $record['username'];


// query_first() is also good for things like
$sql = "SELECT COUNT(*) AS amount FROM `".TABLE_NEWS."` WHERE `comments` >= 50";
$row = $db->query_first($sql);

echo $row['amount'] ." articles have 50 or more comments";
Code:
// delete a specific entry

$sql = "DELETE FROM `".TABLE_USERS."` WHERE `user_id`=$user_id";
$db->query($sql);
Code:
// using $db->affected_rows
//     returns the number of rows in a table affected by your query
//     can be used after UPDATE query (to see how many rows are updated)
//     can be used after SELECT query (to see how many rows will be returned)

$sql = "SELECT `username` FROM `".TABLE_USERS."` WHERE `user_status`='1'";

$row = $db->query($sql); 

if($db->affected_rows > 0){
    echo "Success! Number of users found: ". $db->affected_rows;
}
else{
    echo "Error: No user found.";
}
PHP

Comments

July 7, 2010 - 4:37pm — Anonymous

When trying to follow the example for connecting to two databases I get the following error

WARNING: No link_id found. Likely not be connected to database.
Could not connect to server: .

If I comment each one out individually it connects fine, but it seems adding the second connection is throwing an error. Any suggestions would be appreciated. Great script so far though.

July 8, 2010 - 2:32pm — ricocheting

Since it's a singleton, when you "create" your second $db_2 it's just reusing the first object, not opening/creating a new connection. That's what I get for copy & pasting the documentation from v2 without testing. I didn't even think of that.

You really shouldn't really use a singleton if you need to handle multiple connections from the same page. However, you can use the v3 class as a normal object with a few changes to Database.singleton.php file. Edit it and change
private function __construct($server=null, $user=null, $pass=null, $database=null){
to
public function __construct($server=null, $user=null, $pass=null, $database=null){

Then you can create your database objects the old-school way like:
<?php
// creating and connecting to two database
$db_1 = new Database("localhost", "user", "pass", "deadwood_public");
$db_2 = new Database("localhost", "user", "pass", "deadwood_internal");
?>
You will of course obviously loose all the advantages of the singleton. I edited the examples to remove the multiple connections one.

July 15, 2010 - 12:15am — Anonymous

While using a blank password isn't best practice, the documentation suggests:
If no password, use double quotes with a blank value. Example ""
But the singleton has a check in the constructor for a null password that will generate a big nasty oops.

Thanks!

July 19, 2010 - 11:01pm — ricocheting

Good catch. Version has been updated to 3.1.4 to include this fix. If you want a name/nickname in your credits, let me know.

October 7, 2010 - 2:43pm — Anonymous

This is some modification :

<?php
public function connect($new_link=false){
    if (!$this->link_id = @mysql_connect($this->server,$this->user,$this->pass,$new_link){  //open failed
        $this->oops("Could not connect to server: <b>$this->server</b>.");
        }

    if(!@mysql_select_db($this->database, $this->link_id)){//no database
        $this->oops("Could not open database: <b>$this->database</b>.");
        }

    // unset the data so it can't be dumped
    $this->server='';
    $this->user='';
    $this->pass='';
    $this->database='';
}#-#connect()
?>

December 19, 2010 - 5:32pm — dengine

Hey ricocheting,

Great Singleton class. I'm just starting to rewrite my codes using classes and this Singleton looks very promising. Your explanation is very clear and so are the examples. I do have one question though. You say if you need to call the $db object in a function or class you can do this by using: $db=Database::obtain(); I have the following class example:

<?php
class Images
{
       $db = Database::obtain();

    function get_images()
    {
        $sql = 'SELECT * FROM tblPhotos WHERE ph_ref="aid" ORDER BY ph_level, ph_date';
        $rows = $db->query_array($sql);
        return $rows;
    }
}
?>

I get an error when calling the $db this way. If i put the $db object inside the get_images function it works correct. Is there a way to put the $db at the top of the class so I can use it through the entire class or is that bad practice? I hope you can clear things up for me.

BTW, the error I'm getting is:
PHP Parse error: syntax error, unexpected T_VARIABLE, expecting T_FUNCTION in /Users/dengine/Documents/myWebserver/910_develop/classes/kp_classes/images.class.php on line 6

Many thanks in advance!
Jerome

December 20, 2010 - 1:34pm — ricocheting

You generally put $db=Database::obtain(); inside each function that interacts with the database. it's made to behave that way and it would be fine. If you really want to use it just once in the class, you can declare it in an object contructor (which gets called when you create the Images object) then use $this->db->whatever to call it.

<?php
class Images{

    private $db;

    function __construct(){
        $this->db = Database::obtain();
    }

    function get_images(){
        $sql = 'SELECT * FROM tblPhotos WHERE ph_ref="aid" ORDER BY ph_level, ph_date';
        $rows = $this->db->query_array($sql);
        return $rows;
    }

}
?>

January 13, 2011 - 5:56pm — Anonymous

Great class! However, I need to set the charset to UTF8 after connecting to mysql for a specific job. Normally, you'd set it after mysql_connect with mysql_set_charset('utf8',$db);

I'm not exactly fluent in singleton OOP, how can I achieve this within the class?

cheers and thanks,
peter

January 22, 2011 - 2:40pm — Aquaguy

Add @mysql_set_charset('utf8',$this->link_id); to the connect() function.

<?php
#-#############################################
# desc: connect and select database using vars above
# Param: $new_link can force connect() to open a new link, even if mysql_connect() was called before with the same parameters
public function connect($new_link=false){
    $this->link_id=@mysql_connect($this->server,$this->user,$this->pass,$new_link);

    if (!$this->link_id){//open failed
        $this->oops("Could not connect to server: <b>$this->server</b>.");
    }

    if(!@mysql_select_db($this->database, $this->link_id)){//no database
        $this->oops("Could not open database: <b>$this->database</b>.");
    }
   
    @mysql_set_charset('utf8',$this->link_id);

    // unset the data so it can't be dumped
    $this->server='';
    $this->user='';
    $this->pass='';
    $this->database='';
}#-#connect()
?>

June 2, 2011 - 4:44am — Demeus

I would like so that the class was provided paginal division

June 5, 2011 - 6:25pm — ricocheting

the problem is that short of running the query twice (once to get results count, once to get the actual results with a modified query with a limit) I've never found a good way to do it. I'm open to suggestions from anyone and would love to include something like that.

October 25, 2011 - 5:22pm — jeffrydell

Rather than just trusting that I'm connected to my db after running the two includes, creating the object, and issuing $db->connect(), I needed some kind of test that would throw an error to the end user should the connection attempt fail.

So I whipped up this little gem, maybe you can include it in a future version?

<?php
#-#############################################
# desc: return whether there is a connection to the db
public function connected(){
    if (!$this->link_id) return FALSE;
    else return TRUE;
}#-#connected()
?>

November 11, 2011 - 11:02pm — Anonymous

Quick question: When using increment option to insert/update statement and I have decimal numbers like 32.50 it always resets the database field to 0.00, but if I use 32 than it's fine:
Resets field to 0.00

$val = -16.32;
$data['amount_due'] = "increment($val)";
$db->update("content",$data,"id = 1");

Works fine
$val = -16;
$data['amount_due'] = "increment($val)";
$db->update("content",$data,"id = 1");

How can I use the first example? Thanks

November 14, 2011 - 6:40pm — ricocheting

The increment() doesn't currently support decimals/floats. You'll need to change a line in the function update() from
elseif(preg_match("/^increment\((\-?\d+)\)$/i",$val,$m)) $q.= "`$key` = `$key` + $m[1], "; to
elseif(preg_match("/^increment\((\-?[\d\.]+)\)$/i",$val,$m)) $q.= "`$key` = `$key` + $m[1], ";
I'll make that fix in a future version and give you credit.

March 28, 2012 - 6:40am — Anonymous

Class is great, but i had problems with inserting INET_ATON ip addresses.

So i made quick update of update function:
TASK: $data['lastIP'] = "INET_ATON(".$_SERVER['REMOTE_ADDR'].")";

<?php
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], ";    
        elseif(preg_match("/^INET_ATON\(((?:25[0-5]|2[0-4]\d|1\d\d|[1-9]\d|\d)(?:[.](?:25[0-5]|2[0-4]\d|1\d\d|[1-9]\d|\d)){3})\)/", $val, $m))
            $q.= "`$key`=  INET_ATON('{$m[1]}'), ";
        else $q.= "`$key`='".$this->escape($val)."', ";
    }
    $q = rtrim($q, ', ') . ' WHERE '.$where.';';     
    return $this->query($q);
}#-#update()
?>

April 9, 2012 - 2:10pm — Anonymous

Hi. I've been using your DB class for a LONG time now and love it!

I do have a question though: I have a table (called tbl_globals) where I store several site-wide variables (which are echoed throughout the site and on more than one occasion). They are variables such as org's phone number, site's name, etc. This particular table only has one row (and no additional rows are ever added)

In the past I have queried the table in the database and assigned a variable to each resulting value, which is fine. I have a script that has the query and then a variable for each returned value.

Is there a way that I can use the table's field name AS the variable name (and its value as the value)? I know variable variables can do this, as well as the foreach loop, but I have been unsuccessful in getting this to work with your class.

Any help would be appreciated!

April 10, 2012 - 8:58pm — ricocheting

Specifically for what you're asking, you'd probably have to use eval() like:

<?php
$query=$db->query("SELECT * FROM ".TABLE_CONFIGURATION);
while($record = $db->fetch($query)){
    foreach($record as $key=>$v){
        eval('$'.$key.'="'.$v.'";');
    }
}
?>

However, I would recommend against storing your globals that way. A better/safer way would be to go with something like this (even if you never plan on adding additional globals):

<?php
/*
CREATE TABLE IF NOT EXISTS `configuration` (
  `config_key` varchar(64) NOT NULL DEFAULT '',
  `config_value` text NOT NULL,
  `title` varchar(64) NOT NULL DEFAULT '',
  `description` varchar(255) NOT NULL DEFAULT '',
  `date_modified` datetime NOT NULL,
  `config_group_id` tinyint(3) unsigned NOT NULL DEFAULT '1',
  UNIQUE KEY `config_key` (`config_key`)
);

INSERT INTO `configuration` (`config_key`, `config_value`, `title`, `description`, `date_modified`, `config_group_id`) VALUES
('SITE_TITLE', 'My Site Name', 'Name of site', 'Full name of site for breadcrumbs and title tag', NOW(), 0),
('TAX_AMOUNT', '0.05', 'Tax percent', 'Tax percent in decimal format (eg; enter 0.055 for 5.5%)', NOW(), 0);
*/

$query = $db->query("SELECT config_key, config_value FROM ".TABLE_CONFIGURATION);
while($row = $db->fetch($query)){
    define($row['config_key'], $row['config_value']);
}
// use the defined constant from the database
echo SITE_TITLE; // prints: My Site Name
?>

April 16, 2012 - 12:24pm — Anonymous

how much use of this class is advised for a large website ? eg. high traffic website ?

April 16, 2012 - 2:54pm — ricocheting

It really depends on how big is "high traffic?" If your database is on a single server, yes this class will work well.

However, if you use multiple database machines (or plan on upgrading to multiple db machines in the future) then you should use a database script written to handle multiple servers. Not because this class is inefficient, but because handling multiple master/slave connections and queries requires something different.

The only efficiency caution I have (this applies to everyone) is:

<?php
// feed it the sql directly. store all returned rows in an array
$rows = $db->fetch_array($sql);
?>
Don't use $db->fetch_array() to pull large amounts of data/rows. The reason is $db->fetch_array() retrieves and returns the entire result set so you can store it in an array/memory. If you're printing a 10,000 row CSV sheet, storying all that info in an array before printing could easily run PHP out of memory. With a large amount of data you should print it row-by-row using $db->fetch() so only a single row is stored in memory at a time.

August 20, 2012 - 12:18pm — Anonymous

I just created a new method for this excellent class file. I was unable to display current records showing on page so i decided to create a new method that allows to display form - to records shoing on current page:

<?php
/**
     * Display pagination showing records
     *
     * @access public
     * @return string
     */
    function ShowingResults()
    {       
        if($this->page < $this->max_pages){
            $end = $this->offset + $this->rows_per_page;
        }else{
            $end = $this->offset + ($this->total_rows - $this->offset);
        }
       
        $showing = ($this->offset + 1) .'&nbsp;-&nbsp;'.$end;
       
        return $showing;
    }
?>

August 20, 2012 - 12:31pm — ricocheting

Looks good. How are you filling out $this->total_rows and $this->max_pages? I assume you are running an additional query to get the counts? That's always been my biggest problem about incorporating some type of automatic pagination into this class.

August 25, 2012 - 1:59pm — Anonymous

I've used your wrapper some time now and are pleased with it. Now I'm thinking about using mysqli and prepared statements. Any change your wrapper will support this in the future?

August 28, 2012 - 11:01am — ricocheting

It is possible, but unlikely to happen until the mysql_* commands are depreciated (right now they're just "not recommended" in favor of MySQLi or PDO). After which I might make a MySQLi version so people using the wrapper can move to MySQLi without redoing their code. However, I will most likely stop development at that point.

If you have access to them, I would recommend you directly use MySQLi (or PDO) as they already have most of the functionality this wrapper gave the old mysql_* commands.

August 25, 2012 - 4:40pm — Anonymous

Just cosmetic modification:

<?php
public function delete($table, $where){
    $q="DELETE FROM `$table` WHERE ".$where;

    return $this->query($q);
}#-#delete()
?>

April 22, 2013 - 11:24pm — rian

i have an error when i call a procedure in mysql

PROCEDURE table_name.procedure_name can't return a result set in the given context

April 23, 2013 - 1:36pm — ricocheting

I believe you can't use stored procedures with the older mysql_* extension that this class uses. If possible you should use the MySQLi extension and then probably mysqli_multi_query() as I know it supports using MySQL procedures. MySQLi has most of the functionality already built in that this class added to the old mysql_* commands.