How many times do your fingers type out the commands to run a query or fetch a row? If you are performing database tasks on the majority of your pages and you have not simplified the process then you need to keep reading. Developing a database class is not very difficult, in fact there are plenty of them out there which can set you on your way. Once you start using them you will most likely never go back.
A database class should be thorough, simple, and save you time. A single command to connect or disconnect as well as most of your common functions should be included. Do not re-invent the wheel if you have a large scale project, there are plenty of pre-build APIs which will handle much more than the examples given here. Below is a simple class which can get you up and running as well as examples of how to use it, let’s take a look:
The Class:
var $host;
var $user;
var $pass;
var $database;
var $persistent=0;
var $last_query;
var $result;
var $connection_id;
var $num_queries=0;
function configure($host, $user, $pass, $database, $persistent=0)
{
$this->host=$host;
$this->user=$user;
$this->pass=$pass;
$this->database=$database;
$this->persistent=$persistent;
return 1; //Success.
}
function connect()
{
if(!$this->host) { $this->host="localhost"; }
if(!$this->user) { $this->user="root"; }
if($this->persistent)
{
$this->connection_id=mysql_pconnect($this->host, $this->user, $this->pass) or $this->connection_error();
}
else
{
$this->connection_id=mysql_connect($this->host, $this->user, $this->pass, 1) or $this->connection_error();
}
mysql_select_db($this->database, $this->connection_id);
return $this->connection_id;
}
function disconnect()
{
if($this->connection_id) { mysql_close($this->connection_id); $this->connection_id=0; return 1; }
else { return 0; }
}
}
Above is the bare bones, this will allow you to connect or disconnect to your database, all you would need to do to connect is add the following lines to the beginning of your code. Remember, once you are connected you will not have to do so again.
$db=new database;
$db->configure(‘localhost’, ‘username’, ‘password’, ‘database’, ‘persistent’);
$db->connect();
$c=$db->connection_id;
So now we are connected to our db, we can add more functions to our class now to make it more usable and friendly. Add the following functions:
{
mysql_select_db($database, $this->connection_id);
$this->database=$database;
}
function query($query)
{
$this->last_query=$query;
$this->num_queries++;
$this->result=mysql_query($this->last_query, $this->connection_id) or $this->query_error();
return $this->result;
}
function fetch_row($result=0)
{
if(!$result) { $result=$this->result; }
return mysql_fetch_assoc($result);
}
function num_rows($result=0)
{
if(!$result) { $result=$this->result; }
return mysql_num_rows($result);
}
function connection_error()
{
die("<b>FATAL ERROR:</b> Could not connect to database on {$this->host} (".mysql_error().")");
}
function query_error()
{
die("<b>QUERY ERROR:</b> ".mysql_error()."<br />
Query was {$this->last_query}");
}
function fetch_single($result=0)
{
if(!$result) { $result=$this->result; }
return mysql_result($result, 0, 0);
}
function clean_input($in)
{
$in=stripslashes($in);
return str_replace(array("<",">",‘"’,"’","\n"), array("<",">",""","'","<br />"), $in);
}
function unhtmlize($text)
{
return str_replace("<br />","\n", $text);
}
function escape($text)
{
return mysql_real_escape_string($text, $this->connection_id);
}
function affected_rows($conn = NULL)
{
return mysql_affected_rows($this->connection_id);
}
The Use
So the above code added into your class will give you all of the basic connection and querying tools you should need to perform most tasks. You may want to modify and add additional functions to this class. So a basic query would now look like:
Escaping…
So if we wanted to take a group of variables posted from a form and escape them all we could write a small routine using our class. What the below would do is allow us to configure an array with the names of all the variables we are expecting. If the variable exists we escape it and return the variable as it’s original name. An example would be: a user fills out a form with two fields (username, and password), the form is posted to our php script. We are expecting those two inputs so we add it to our array. We then escape those values and return them as $username and $password. Take a look:
The above code will take any number of variables passed from your form and escape them all while returning them in a variable form. It is a great function which adds security by helping prevent injections.
Wrap it up
If you are running a small site and do not require complicated functions then the above class will be more than enough to save you time in development. Do not load a large API for small sites, this will most likely hinder performance instead of help it. Remove the commands that you do not use and add ones that you do. Having a small custom class like this makes all your other tasks just that much easier.
![[del.icio.us]](http://scriptperfect.com/wp-content/plugins/bookmarkify/delicious.png)
![[Digg]](http://scriptperfect.com/wp-content/plugins/bookmarkify/digg.png)
![[dzone]](http://scriptperfect.com/wp-content/plugins/bookmarkify/dzone.png)
![[Facebook]](http://scriptperfect.com/wp-content/plugins/bookmarkify/facebook.png)
![[Furl]](http://scriptperfect.com/wp-content/plugins/bookmarkify/furl.png)
![[Google]](http://scriptperfect.com/wp-content/plugins/bookmarkify/google.png)
![[LinkedIn]](http://scriptperfect.com/wp-content/plugins/bookmarkify/linkedin.png)
![[MySpace]](http://scriptperfect.com/wp-content/plugins/bookmarkify/myspace.png)
![[Newsvine]](http://scriptperfect.com/wp-content/plugins/bookmarkify/newsvine.png)
![[Propeller]](http://scriptperfect.com/wp-content/plugins/bookmarkify/propeller.png)
![[Reddit]](http://scriptperfect.com/wp-content/plugins/bookmarkify/reddit.png)
![[Slashdot]](http://scriptperfect.com/wp-content/plugins/bookmarkify/slashdot.png)
![[Spurl]](http://scriptperfect.com/wp-content/plugins/bookmarkify/spurl.png)
![[StumbleUpon]](http://scriptperfect.com/wp-content/plugins/bookmarkify/stumbleupon.png)
![[Technorati]](http://scriptperfect.com/wp-content/plugins/bookmarkify/technorati.png)
![[Twitter]](http://scriptperfect.com/wp-content/plugins/bookmarkify/twitter.png)
![[Email]](http://scriptperfect.com/wp-content/plugins/bookmarkify/email.png)
I would check this out: http://www.developer.com/lang/php/article.php/3604111
That is a great article, I will have to try some of that out. Have you had the chance to try it using multiple connections to separate databases? I am curious to see if it is possible to use an abstract class with multiple interfaces to handle simultaneous connections.
Wouldn’t it be better to use an external library to do this kind of thing? For example the utilities at http://pear.php.net/.
When it comes to PHP and Database -> doctrine-project.org
I would also recommend to look at the PDO functionality or at least the mysqli functions. Also, var is deprecated, look at private/protected/public.
Thank you to everyone for the input, it has been some time I guess. Maybe I should call this an old outdated way?
It’s considered a best practice for a class name to start with a capital letter. You’ll want to make those variables private as well.
Tim, the only thing wrong with this article is the title. Here are some suggestions:
PHP Database Class, The Wrong Way.
PHP Database Class, The Right Way, in 2001 & PHP3.
PHP Database Class, and some other random functions.
You can use your own imagination from here on. Maybe some other people have suggestion?
Well Tim, using your own database class (that for example extends MySQLi) to add some functions of your own is not outdated. However, the way this article describes _is_ outdated.
I have an additional question: why did you create the clean_input()-function? You usually do not wanna store HTML in your database; you just wanna output it as HTML whenever it is needed. And to this extend you might wanna have a look at http://php.net/htmlspecialchars instead of making your own variation on this with str_replace().
Buy:Aricept.Benicar.SleepWell.Seroquel.Zetia.Prozac.Zocor.Ventolin.Lipothin.Buspar.Lasix.Cozaar.Amoxicillin.Female Cialis.Female Pink Viagra.Nymphomax.Acomplia.Wellbutrin SR.Lipitor.Advair….
Thank you……
Did I already tell you that I enjoy every subjects on your site ?…
Title…
This is my Excerpt…
What’s wrong with mysqli or PDO? There really are a lot of omissions/bad practices in this article
I’ve just started off a blog, the knowledge you give on this site has aided me extremely. Thank you for all your time & work….
Of course, what a great site and informative posts, I will add backlink – bookmark this site? Regards, Reader…
Cam to this article researching database classes, look like it is better to go with an established and free database class
Wholesale Yankee Candles…
[...]just below, are some totally unrelated sites to ours, however, they are definitely worth checking out[...]…
Another Title…
I saw this really good post today….
Super Cheap Yankee Candles…
[...]we like to honor other sites on the web, even if they aren’t related to us, by linking to them. Below are some sites worth checking out[...]…
Free Movie Downloads…
[...]just below, are some totally unrelated sites to ours, however, they are definitely worth checking out[...]…
Free Movie Downloads…
[...]just below, are some totally unrelated sites to ours, however, they are definitely worth checking out[...]…
Big Job Site…
[...]while the sites we link to below are completely unrelated to ours, we think they are worth a read, so have a look[...]…
Roth IRA Rules…
[...]we like to honor other sites on the web, even if they aren’t related to us, by linking to them. Below are some sites worth checking out[...]…
Chandler homes for sale…
[...]we like to honor other sites on the web, even if they aren’t related to us, by linking to them. Below are some sites worth checking out[...]…
Potenzmittel Cialis…
[...]Tadalafil Citrate Potenzmittel online rezeptfrei sehr guenstig bestellen[...]…
Interesting sites…
[...]these are a lot of links to sites that we link to because we think they are worth visiting[...]…
Steve’s Blog…
[...]we like to honour other internet sites on the web, even if they aren’t linked to us, by linking to them. Beneath are some web sites worth looking over[...]…
EDU Auto Approve List…
[...]Scrapebox Auto Approve Blogs[...]…
Free iPad 2…
[...]Did you know you can get an Ipad 2 for FREE?? Click link above!![...]…
Google Blogs…
[...] which follow there can be the connection to a new services that most of us trust it is important to consultation [...] …
Steve’s Tech Site…
[...]though the sites we link to below are totally unrelated to ours, we presume they are worthy of a read, so have a look[...]…
Google News…
[...] on this page there’s the hyperlinks to a new internet sites that take into account you may want to come to [...] …
Fantastic Post…
[...]is always a good read, take a look now to see if there is anything new and let me know if you[...]…
Quite the read…
[...]very interesting commonality to our prior article… But be critical on resources[...]…
Favorite Writers…
[...]like me, you’ll no doubt love each of the sites that I have listed here[...]…
Chandler homes for sale…
[...]we like to honor other sites on the web, even if they aren’t related to us, by linking to them. Below are some sites worth checking out[...]…
Chicken Coop Design…
[...]we like to honor other sites on the web, visit our links list[...]…
Russell Armstrong Suggests Its A Fantastic Post Congratulations…
Below might be various other information everyone may possibly be interested within, keep it up, well done,…
[...]below you’ll find the link to some sites that we think you should visit[...]…
[...]here are some links to sites that we link to because we think they are worth visiting[...]…
Travel Deals…
[...]number of online sites that are detailed below, from our own perception are definitely worth browsing[...]…
Cieaura Chips In You Are Great…
[...] Best post so we would like to track-back to this!!! BTW did one read concerning Middle East now has extra worries as well … [...]…
Travis on his way to hollywood?!…
[...]do you know that we were looking for this, johny was too[...]…
[...]while the sites we link to below are completely unrelated to ours, we think they are worth a read, so have a look[...]…
[...]just below, are some totally unrelated sites to ours, however, they are definitely worth checking out[...]…
Background Check…
[...]the time to read or visit the content or sites we have linked to below the[...]…
1.) Keys, locks and more…
2.) [...]we came across a cool site that you might enjoy. Take a look if you want[...]…
Financial Advisor…
[...]I like to showing you other places on the WWW, even though those URLs are in no way similar to this blog, by hyperlinking them. Below are several places worth checking out[...]…
1.) Keys, locks and more…
2.) [...]we came across a cool site that you might enjoy. Take a look if you want[...]…
free grant money for college…
[...]below you’ll find the link to some sites that we think you should visit[...]…
New Indie Films Online Says It’s An Excellent Posting Well Done?…
[...] In your own time try to see this they may constitute fascination also! [...]…
science news…
[...]here are some links to sites that we link to because we think they are worth visiting[...]…
Breast Actives faq…
[...]i highyl recommend you check out[...]…