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

Warning: Abandoned
This page/script has been classified as abandoned and will no longer be updated. I will keep this page here indefinitely as a reference, but it will no longer be updated and I no longer offer support of any kind regarding content found on this page.
Note:
At this point in time, you really should be using the MySQLi or the PDO module to access your database. Any host with PHP and MySQL should offer them and they both already contain most of the functionality this wrapper gave the old mysql_* commands.

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
Abandoned

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 using PHP's define() to set your site-wide variables as constants. Something like:

<?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 necessarily 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, storing 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 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.

October 30, 2018 - 2:32pm — Justin Anderson

Webmaster Notes:
Justin updated the old class to use MySQLi functions and kindly pasted the code here. You can view/download Justin's changes. Everything should be fully backwards compatible with the old version.
  1. He converted all the mysql_* functions to mysqli_*
  2. $this->query_id is is now a false bool if it is unused or there are errors (instead of -1)
  3. Unlike mysql_free_result() which returned a success bool, mysqli_free_result() returns no value so $this->free_result() was updated to reflect this

November 3, 2018 - 9:22pm — ricocheting

Very nice job. Appreciate you putting in the time and work to convert this and then sharing it with all of us. Also, if anyone wants to take this a step further, you could also go:

<?php
class Database extends \MySQLi {
    //...
}
?>

and eliminate some of the redundancies for functions MySQLi already handles. It would also allow you to inherit all the other MySQLi functions and use them within your $db Database object. eg;

<?php
$db->prepare("SELECT `uid` FROM `account` WHERE `user`=?");
?>

December 13, 2018 - 2:36pm — Justin Anderson

You are very welcome! I had to upgrade PHP and MySQL on our company server and my PHP framework I wrote used your database class, so I ported it over after seeing that it hadn't been done on here yet, I posted for all to see and use. Thanks for providing such a great database class to use.

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()
?>

May 31, 2013 - 7:46am — Koen Thomeer

a proposition for inserting/updating NULL values in version 3.1.4

line 203:
if(strtolower($val)=='null'||$val == NULL) $q.= "`$key` = NULL, ";

line 225:
if(strtolower($val)=='null'||$val == NULL) $v.="NULL, ";

May 31, 2013 - 1:46pm — ricocheting

That's a great idea. Depending on the behavior wanted, you might want to use $val === NULL otherwise $data['blank']=""; will also insert/update as NULL.