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
Question: Use within a class
I am attempting to use this class within another class. It works wonderfully, until I create an object from my class within another file. My code is a few hundred lines of code but the break down is:
FILE 1:
//myclass.class.php
include(db_settings.php);
include(databaseclass.php);
class myclass{
public function __construct()
{
$db = Database::obtain(DB_SERVER. DB_USER, DB_PASS, DB_DATABASE);
$db->connect();
}
public function __destruct()
{
$db = DATABASE::obtain();
$db->close();
}
public functions that use database...
}
If a place code after that class within the same file (myclass.class.php) to create an object of myclass the code runs like a champ... however if I create a second file, say (index.php) and do the same it does not work.
//index.php
include_once("myclass.class.php");
$mine = new myclass();
//code that uses mine
this will produce
WARNING: No link_id found. Likely not be connected to database.
Could not connect to server: .
WARNING: No link_id found. Likely not be connected to database.
Could not open database: .
Now I am new to singletons so this might be my problem, but I cant seem to figure it out, the error suggests that the server name and database name is not being retained by the singleton. any ideas?
Again, I really like this class. It simplifies my life in so many important ways. It is a refined and smart class. Other singleton mysql classes I have seen around only open up connect and query, yours is much more useful.
Answer
What you are trying to do works for me (using my class inside another class and including that class inside a third file)
I don't know if you copy&pasted exactly from your code, but you have a typo (you have a period. it should be a comma after DB_SERVER) in
$db = Database::obtain(DB_SERVER. DB_USER, DB_PASS, DB_DATABASE);which could possibly be giving you the "Could not open database: ." errors.If you're still having problems I'll zip up the files I tested with so you can download and look at them and compare to what you are doing.
Question: MySQL PHP cyrillic Characterset
Hello,
thank you for this easy to use script. Everything is workling well except output cyrillic words. They are transformed to soemthing like ???????? (questionmarks). The collation of my database =utf8_bin. I have several pages that do not have any problem with cyrillic (phpbb3).
German special letters like ü,a,Ö are displayed correctly. When I put
header("Content-Type: text/html; charset=utf-8");this German letters become diamonds but this measure doesn't help with the cyrillic problem.
I tried something like set names to alter your class
mysql_query("SET NAMES 'utf8'", $sql_connection);but I don't know how.
Thank you very much!
Bernd
Answer
You're on the right track. You can change the Character Sets and/or Collations by running a query anytime after you connect to the database. Just use my class to run the query instead:
$db->query("SET NAMES 'utf8'");Question: Using the class in two different functions
Hello,
I want to use this class in a page two times.
My codes are likely this:
<?php
include("dbconnections.php");
function a(){
$db = Database::obtain();
$db->connect();
//some codes about db connection
$db->close();
}
function b(){
$db = Database::obtain();
$db->connect();
//some codes about db connection
$db->close();
}
?>
when i called first function, there is no problem. when i called two of them, first function works great, but the second doesnt work.
gives me error:
Access denied for user 'www-data'@'localhost' (using password: NO)
with this error, cannot connect database,table etc. what i need to do?
Answer
You do not need to connect/close in each function. Just do it once at the top of your main page. It should look closer to this:
<?php
include("dbconnections.php");
// create the $db singleton object
$db = Database::obtain(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);
// connect to the server
$db->connect();
// call your functions
a();
b();
// close the connection
$db->close();
// the functions themselves will look like this
function a(){
$db = Database::obtain();
//some codes about db connection
$db->query("SELECT * FROM `table`");
}
?>
Use inside another class
First of all thanks for your great class, it's very useful to me.
I have a question regarding opening and closing connection.
I had to comment the unsetting of connection parameters in the 'connect' method in order to be able to open and close the connection in each method of my own classes otherwise I got an error (due to the missing connection parameters).
Do you think this could be a good practice or you can suggest a better way of using the singleton class inside other classes methods?
Thank you very much.
Alex
Answer
Just create your initial object $db = Database::obtain(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE); and run $db->connect(); at the very top of the page. Then in all your other class methods you can just reference the already existing singleton object $db = Database::obtain(); and it will reuse the already open connection. You do not need to connect/disconnect in every method.
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.
if($server==null || $user==null || $pass=null || $database==null){$this->oops("Database information must be passed in when the object is first created.");
}
Which makes it unusable without a password, unless you remove that check..
if($server==null || $user==null || $database==null)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.
Bug: Error with multiple connections
When trying to follow the example for connecting to two databases (obviously using my db's and information)
<?php
$db_1 = Database::obtain("localhost", "user", "pass", "deadwood_public");
$db_2 = Database::obtain("localhost", "user", "pass", "deadwood_internal");
$db_1->connect(true);
$db_2->connect(true);
?>
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.
Question: Exception Handling
Hi, firstly just wanted to say I've been using the original DB class for a couple years and have found it really helpful.
Thanks so much for this simple and effective tool.
I recently updated my scripts to use the newest version because I wanted to take advantage of the new connect methods.
I've got one system in particular where MySQL timeouts are a major problem as the script runs persistently to accept socket connections (recording transmissions to the DB), but it can be hours between receiving data and I don't have the server control to allow persistent connections.
So I've been trying to find a way of using your Database class to check that the connection is still live before running queries and invoke the 'connect(true)' method if there is a timeout exception, to reestablish a connection.
I'm a noob at handling exceptions and I don't know if they're accessible at all through the abstraction layer so I'm really wandering in the dark.
Here's the general approach I've been trying without success:
[snip]
Any advice you can give would be greatly appreciated.
Tim.
Answer - auto reconnect without persistent connection
The programmer in me says you're approaching this from the wrong end. Why are you maintaining an open connection to a php file for hours? Infact, how are you doing it unless you're running PHP as a shell script (in which case, why not just use shell to make the necessary config changes)? HTTP PHP should auto-close the http connection and kill the script after 30 seconds (or whatever max_execution_time is set to).
That said, as for your question, take a look at mysql_ping(). I think that should solve your problems and is what you are looking for.
This is great.
Glad to see you incorporated all the functions that V2 uses. I will definitely be using this in my future projects. I am certainly willing to donate to the work of this project, and the potential for a user login/management system, but I did not see an email for donating. Do you have paypal?
Contributing to the site
I really appreciate the sentiment, but after some thought I've decided even accepting donations goes against the purpose of this site.
For people that want to contribute, the best way to contribute is test the content and take some time to go through the script and the documentation. Any bugs or security issues are the number one priority. I also appreciate comments on the documentation and it's clarity. I won't guarantee suggestions will be published and/or implemented, but they are appreciated.
I've ended up writing a fairly extensive library of generic scripts that I reuse in my work and my goal is to eventually release all of it. While the code itself is usually commented, the problem is documenting each one so that the people who need it can find it and easily reuse it. Proper documentation is the bane of public release and documentation takes MUCH longer than writing the actual script so most of my library stuff is never seen by anyone but me and the clients.