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?
- A Singleton in PHP allows you to create a single object ($db) and reuse that same existing object anywhere in your code.
- 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
ZIP with all files (Database.singleton.php, sample config, sample how to call, copy of license, examples of queries)- Quickview Database.singleton.php (already included in above zip)
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 objectrequire("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 objectYou 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.
$db=Database::obtain();
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
// 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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
// 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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
// 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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
// 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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
// 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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// 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:
1
2
3
4
2
3
4
// delete a specific entry
$sql = "DELETE FROM `".TABLE_USERS."` WHERE `user_id`=$user_id";
$db->query($sql);
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 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.";
}
Comments
adding floats in update/insert statement
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
Answer
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], ";toelseif(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.
Check for a db connection?
Rather than just 'trusting' that I'm connected to my db after running the two includes, creating the object, and issuing $db->connect(), I would like to have some kind of 'test' that would throw an error to the end user should the connection attempt fail. I looked through the source code and didn't see a method for testing the connection. Any thoughts on a good way to do this?
Thanks!
Answer
There's no built in way to test. but if I had to do it, I would probably check if the link_id exists. You'll need to change it from private to public if you're checking outside the class.
if(empty($db->link_id)) echo "no connection";Check for connection
Couldn't quite do it that way:
Fatal error: Cannot access private property Database::$link_id in /home/path/to/file.php on line 71
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()
?>
Thanks for a GREAT class - I use it a lot, very handy!!!!
Answer
I like it, nice job. We might see it in a future version.
MySQL server has gone away
Hi,
I have a script that runs continuously to get data from the sources in a loop.
Whenever my script get data it simply use existing instance of the $db object and insert data into database.
but I am getting error "MySQL server has gone away".
How can I solve this issue with this class?
thanks
Answer
The connection is open long enough that it's timing out. If it's your server, you can increase the wait_timeout setting in MySQL to a number high enough so it won't ever expire. If you are on shared hosting or can't change the MySQL settings directly, try modifying your script to use mysql_ping() before you run your query:
mysql_ping -- Ping a server connection or reconnect if there is no connection
paging
I would like so that the class was provided paginal division
Answer
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 seen a good way to do it. I'm open to suggestions.
db close on __destruct?
Just wondering if there is any reason why, instead of putting this $db->close(); at the end of a php script/page, if it's possible to simply add a __destruct function to the singleton class and have that function do the explicit closing of the db connection?
Answer
If you dispose of your $db object when you're done with the connection, calling $this->close(); in the deconstructor would probably work well for you.
Personally I have bad habits and usually don't even call a close on the MySQL connection (perfectly safe in PHP, it'll automatically close it when the page is finished. but it is a very bad programming habit to get into).
set connection charset to UTF8
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
UTF8
<?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>.");
}else{
@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()
?>
Answer: Specifying a character set
You can still do it that way. Inside the connect() function after the database is selected (current version about line 98) you can add:
@mysql_set_charset('utf8', $this->link_id);Where to put the $db in a new class
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
Answer
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;
}
}
?>
Suggestion: Improvement connect()
This is some modification :
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()
Bug: In the case of no password
While using a blank password isn't best practice, the documentation suggests this..
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!
Version Updated
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.
Question: Correct code throws error
Correct code throws error, but using different code seems to work fine. I'm confused.
When I attempt this:
<?php$db = new Database(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);
?>
I get this:
Fatal error: Call to private Database::__construct() from context '' in /var/www/vhosts/directoryToMy/test.php on line 40
(Which happens to be the line where I call $db = new Database();)
But when I do this:
<?php$db = Database::obtain(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);
?>
Then all seems to work just fine. Any help would be greatly appreciated as I have another enigma which will follow once this gets resolved.
Thanks in advance!
Answer
If you really want to declare and use $db as a traditional object, you can change the visibility on the constructor (line 61 on Database.singleton.php is what's erroring) from private to public which will allow you to call the __construct method from outside the database class.
Singleton vs no Singleton
Singleton vs global var
Generally the use of globals is frowned upon and seen as poorly planned code. Globals can in some cases lead to extra memory overhead and can also lead to scope issues and headaches while debugging.
In this, yes you are right. The singleton here essentially has the same function as just reusing a global $db object. In a more general sense when you call a singleton it would create the object if it didn't already exist (here that doesn't really work because the initial call has to include the database info as parameters). With a generic singleton you wouldn't need to worry about whether the global $db object already existed or not; you just call it and use it.
Bug: Error with multiple connections
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.
Answer - Using multiple connections with a singleton
After asking around, your best bet would be to simply not use the class as a singleton if you have to handle multiple connections from the same page (which should only happen VERY VERY rarely). Before you can use the v3 class "normally" you need to edit Database.singleton.php and change
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");
?>
Sidenote for anyone wanting a project
A friend suggested using a hash based off the database info and using the hash to create a unique object on a per database basis. It might have merit, but it's not something I ever see myself using and 95% of what is here is here because it was something I needed. If someone else wants to play with the idea and comes up with a workable solution for using a singleton to handle multiple connections, I'll happily test it and pass it along.