PHP Design – Biggest Database Oversights

Over the last three years I’ve had the opportunity to work on several PHP projects, some of them having grown rapidly and required to scale quickly. Three in particular have been a fantastic learning experience for me. Now I don’t consider myself a total expert, but I thought I would share my experiences. Many PHP developers are self-taught, like I was, and are learning PHP to be able to create dynamic websites. Many haven’t had studied or learned best practices for Object-Oriented Programming (OOP). They are web designers first, and programming in PHP is just a way to do things simple HTML can’t do.

Because most PHP applications rely heavily on the ability to read and store information in a database, many times designing how you interact with that database is critical. The reason being is if you design something, but don’t foresee a requirement or necessity, it can be extremely time consuming to go back and change. These “oversights” can be costly in time, money, and resources.

I’ve thought of some of the biggest oversights I’ve had when working with PHP and MySQL and put them in a list. This is my personal list, and I’m sure some people can think of some other oversights that belong on the list as well. This list is just for PHP & MySQL, not PHP and any database. I know many people like using software like Doctrine to allow switching between different database types. That is beyond the scope of this article.

Many of the examples in this article are very simple and are designed for showing techniques of better programming, not for actual use. Straight copy and paste most likely will not create sound, robust solutions. Search the web for a solid MySQL PHP Class to handle these issues for you, or you can make your own.

Now that I’ve thrown out my disclaimers and thoughts, lets venture on to by list:

  • Oversight #1 – No Data Access Layer
  • Oversight #2 – Design for Only One Database Connection
  • Oversight #3 – No Developer Logging
  • Oversight #4 – Queries Written In Procedural Processes
  • Oversight #5 – No Separation of Reads & Writes

Oversight #1 – No Data Access Layer

I first heard the term Data Access Layer was when I was reading about ASP .NET. Basically a Data Access Layer (DAL) is a layer of code that you access data through. Sounds simple right? Many new php developers don’t have any type of DAL. Here is an example of not having a Data Access Layer:

[php]

[/php]

Now, that wasn’t so bad, was it? Lots of developers just use the mysql commands. I have several projects I worked on my early days with PHP that fall victim to this oversight. One in particular now has grown out of hand so bad that we’ve decided to start from scratch for a whole new version. Why? Lets say you have 3000+ php files, and your boss says “Hrm, we’re seeing some problems with performance. Can you display at the bottom of each page the # of queries you use on that page?” If you coded your entire project like the example above, you would be totally screwed. You would have to find each and every mysql_query() and add some counter at the end. It would be a managing Nightmare. So how cold you solve this problem?

[php]

connection = mysql_connect($host, $user, $pass);
return true;
}

// Run a query against the database. The key here is to make sure
// every query command goes through this function
public function Query($sql)
{
// Execute Query & Get Result Resource
$result = mysql_query($sql);

// Increment Query Counter
$this->query_count++;

// Return the result
return $result;
}

// Function to take an SQL query, execute it, and return all
// the rows in an assoc. array.
public function FetchArray($sql)
{
// Execute the Query and get the Result
$result = $this->query($sql);

// Create Empty Array to Store all the rows
$array = array();

// Loop through each row
while($row = mysql_fetch_assoc($result))
{
// Add the row to the array
$array[] = $row;
}

// Return the array containing all the rows
return $array;
}
}

$dal = new MySqlDAL(‘localhost’,’mysql_user’,’mysql_pass’);
$user_list = $dal->FetchArray(“SELECT * FROM users”);

// Display Table of Users…
// ..

?>

[/php]

First of all, you’ve satisfied the boss’s wish of keeping a counter for the total # of queries. The trick is to have all queries go through the function “Query($sql).” This will ensure the counter stays current. You can also add code to catch errors, display error messages, log queries, etc. Having a class to wrap around your database connection, queries, etc. can make your life easier, and more simple.

Oversight #2 – Design for Only One Database Connection

I have a project that I’ve taken over right now that is suffering from being programmed into handling only one database connection. “But why would you ever need to connect to more than one database?” Well, I like to have a database for important information: invoices, users, payments, etc. Stuff that is updated every day & information that the end user manipulates. Then I like to have another database for logging statistical information: user log-ins, page hits, visitor counts. The problem I’ve ran into lately is I want to have hourly backups for my important information, but only daily backups for simple statistical information. It would be much easier if they were separated in different databases, but many times they are in the same. I don’t want to take the extra time and space to backup the statistical information.

Many people who run into this problem use a Singleton class to handle their Data Access Layer. A singleton class is a class that can only have one instance at a time in execution. This can be suer easy and quick to use in your code, but may lock you into a situation where your code only handles a single database connection:

[php]

[/php]

Now your boss comes in and asks you to connect to another database to get a list of customers. You think “uh oh, I can’t really do that with my design, now can I?” The solution I like to use is having Database Connection classes, and then a Database Connection Manager. Imagine I have the MySqlDAL class from the previous example in oversight #1. I would create singleton class called MySqlManager. It’s job would be to handle the creation & management of all it’s classes. Here is an example:

[php]
connections = array();
}

public static function GetInstance()
{
// If the instance is null, make one
if(!self::$instance)
{
self::$instance = new MySqlManager();
}

return self::$instance;
}

// Connect to a new database;
public static function CreateConnection($host, $user, $pass, $name = ‘default’)
{
$manager = self::GetInstance();
$manager->connections[$name] = new MySqlDAL($host, $user, $pass);

return $manager->connections[$name];
}

public static function GetConnection($name = ‘default’)
{
$manager = self::GetInstance();
if(isset($manager->connections[$name]))
{
return $manager->connections[$name];
}
else
{
// handle connection not found error…
}
}
}

// Now you can create a connection, and if you don’t give it a name, it will be defaulted for you

$dal = MySqlManager::CreateConnection(‘localhost’,’mysql_user’,’mysql_pass’, ‘customer’);

$customer_dal = MySqlManager::CreateConnection(‘mysql.another-location.com’,’another_user’,’another_pass’);

// ..

// later in your code, maybe even in a class or function you could get the connection by name:

// get default connection
$dal = MySqlManager::GetConnection();

// Get customer database connection
$customer_dal = MySqlManager::GetConnection(‘customer’);

$users = $dal->FetchArray(‘SELECT * FROM users’);

$customers = $customer_dal->FetchArray(“SELECT * FROM customers”);

// ..

?>
[/php]

Now you have a MySQL Connection Manager class that can handle the creation, management, and disconnection of your SQL connections. Your boss will be happy to hear you can connect to another database with easy!

Oversight #3 – No Developer Logging

There are many times where I wish I could what was going on behind the scenes. It is very easy to add Query Logging to your Data Access Layer if you choose. I won’t give an example how to do so, but there are some several very important parts to it. First, you need to be able to enable & disable query logging. Second, you should log whether it was successful or not. Also, if you play around with PHP’s back tracing abilities you can trace where each query was made. I’ll make a blog post about this another day.

Oversight #4 – Queries Written In Procedural Processes

I absolutely *loathe* when I get into a situation where queries are being written all over the place. If there is one constant in any software development, it is that things will always change. When queries are being placed in procedural code, it makes management of database changes a total hassle. “But what about find and replace?” That can help you find places where you need to change, but you still can miss other places in your code, which can lead to bugs, headaches, and self-combustion. Here is an example:

[php]
FetchArray(“SELECT * FROM user_links WHERE user_id = 1 ORDER BY number”);

// Display them on links page…

// .. another page

// Get user links
$links = $dal->FetchArray(“SELECT * FROM user_links ORDER BY number”);

// Display them on profile page…

// .. another page

// Get user links
$links = $dal->FetchArray(“SELECT * FROM user_links ORDER BY number”);

// Display them on about me page…

?>
[/php]

Not seeing the big issue here? Lets say you copy and paste that line of code ‘$links = $dal->FetchArray(“SELECT * FROM user_links ORDER BY id”);’ on twenty different pages. Then your boss comes and says “Hey, you know how the user_links table has a title? Well every where we display the title, I’d like to have it show which # of link it is, so example: Link #1 – My Blog, Link #2 – My Facebook., Oh, and can we order them by date_added? Thanks!”

Have fun trying to find every place that query is executed. Don’t be surprised when only 80% of the queries get changed, and the other 20% become bug tickets with your boss wondering why you didn’t find them before. This is an example of writing queries all over the place when you need them, instead of organizing them and abstracting them by Classes and Functions. Here is an example of abstracting the query:

[php]
FetchArray(“SELECT * FROM user_videos WHERE user_id = ‘$this->id’ ORDER BY date_added”);

$new_video_list = array();
foreach($videos as $video)
{
$video[‘caption’] = “Link #”.$video[‘number’].’ – ‘.$video[‘caption’];
$new_video_list[] = $video;
}

return $new_video_list;
}

// ..
}

$user = new User(1);

$user_videos = $user->GetVideos();

// ..

?>
[/php]

Now your boss will give you a raise when you can make changes this quickly. While this example may seem simple, there are many times where I’ve had projects that queries have required major changes and re-writes. Bugs quickly service if queries that are the same, or similar, are copied and re-copied all over the place. This leads to a giant mess very quickly. So remove queries from the procedural flow of things by abstracting them in classes and functions.

Oversight #5 – No Separation of Reads & Writes

This oversight has stung me for the first time just a few weeks ago. A project of mine had reached critical mass with its size, and out grew its beefy MySQL dedicated server. I started investigating doing a Master-Slave replication system, but every article said I needed to route SELECT statements to the slave servers, and just about every else to the Master (like Insert, Update, Delete, etc). This project didn’t have any Data Access Layer at all, and 2+ years of development on it. There is no way I could go back and re-write all the queries on budget and timeline, especially without causing bugs. especially if Insert/Update/Delete commands on accidently got routed to a slave server.

My suggestion is funneling all queries into two functions, Select() and Execute(). Then, when you create a MySqlDAL instance, it would actually have a $this->master_connection; and $this->slave_connection; It wouldn’t connection on creation, but on the first instance a query was executed. This is done to release stress on the Master server, the whole purpose of Master-Slave replication setups. Here is a mock class that would handle this:

[php]
slave_connection)
{
$this->ConnectSlave();
}
// .. Increment Slave Counter ..

return $this->Query($sql, $this->slave_connection);
}

public function Execute($sql)
{
if(!$this->master_connection)
{
$this->ConnectMaster();
}

// .. Increment Master Counter ..

return $this->Query($sql, $this->master_connection);
}

// ..

}

?>
[/php]

Conclusion

Hopefully these insights into oversights with PHP and MySQL can help someone out there tackle there next project, big or small. If you have any questions, feel free to leave a comment and I’ll respond as soon as possible.

40 thoughts on “PHP Design – Biggest Database Oversights

  1. nicely written and it explains well the limitations of PHP MySql.

    Have you in anytime thought of a solution for mapping PHP objects to Mysql tables with a layer?
    Any limitations?

    Like

  2. I have done something along those lines, I just need to clean up my code and make good examples to show it off. One day I’ll get around to it.

    Like

  3. Looks like I’m doing something right then. The DB Connection class I wrote recently looks almost line for line identical to yours except I’m using PDO 🙂

    Thanks for the excellent article Justin.

    Like

  4. Shouldn’t the new link argument be set for mysql_connect?

    Like

  5. In your solution to oversight #4 you should mention the use of iterators. You’ve fallen into a pattern where in your method that encapsulates some very specific functionality you loop over a set of database results. It’s also the case where in your FetchArray method you loop over the result set. In all likelihood, the array you return from your GetVideos method would be looped to display video information to the user.

    In sum, you’ve looped over the mysql result set three times simply to display results to the user. Given a big result set this is a huge waste of resources.

    The solution, which I would say is oversight #6 is to use iterators to make it so that fetching from the database, formatting results, and displaying them, is all only done when it needs to be done.

    I suggest you go research PHP’s Iterator interface and the IteratorIterator class.

    Like

  6. @Justin, Great Article!

    I’m working on a framework (Recess! Framework, http://www.recessframework.com/) which abstracts away each of your oversights.

    I wrote a response to how Recess! addresses these oversights internally here: http://www.krisjordan.com/2008/11/28/how-recess-solves-common-phpmysql-issues/

    Cheers!

    Like

  7. Very useful article, Justin, thanks. I am rebuilding a site’s data warehouse, this explains what I wanted to know in one page.

    Like

  8. $dal = new MySqlDAL(‘localhost’,’mysql_user’,’mysql_pass’);

    What happens if they change the username & password? You’ll have to change that line in 3000+ files?

    Like

  9. @coolerthanthou

    You bring up a good point I didn’t mention in the article. It is *critical* that you use configuration files. That way your website finds the username & password from a configuration file, so when you change your login information, you change it in one place.

    Another option is to have an included file that is always loaded at the beginning of your website that establishes a connection.

    Like

  10. Cool post, thank you!

    Like

  11. Great post, I follow all of those measures and I must say that they do solve a lot of things, although it took me years of trial and error to get to the point where I am now.

    It is great that you showed this to everyone, and I recommend that you post someday your own MySQL handling class. The one I use was developed by Sebastien Laout and it has one of the best debugging systems I have used.

    Best regards,
    Alex

    Like

  12. i think most of the points here are for those who obsessed with oop structure. most of these examples will increase the code-base without adding an useful functionality. php is a 4gl and does not need excessive structure to get things done for small apps. is there really a need for a data abstraction layer if you never plan on migrating from mysql?

    consider oversight 4 – ‘Queries Written In Procedural Processes’ – if you’ve got to update the same query in 20 different pages for a minor functionality change, your system architecture must be quite convoluted anyway. if you have to disturb the flow of an application by continually accessing class upon class, you are not making it easier to maintain. it means each time you want to modify a simple logic change in a procedure, you have to have multiple files open figure out where the change is.

    no piece of code is permanent. the purpose of software is to make effective use of hardware in a constantly changing environment. a language provides sufficient structure – your app doesnt need to.

    Like

  13. AWLearningTheHardWay March 29, 2009 — 1:07 pm

    Hmm this is really good,

    Will there be a Follow-up Artical with more… The whole why fix something if you can make it so it doesnt break in the first place is a key ideaology which i am trying to follow .

    And secondly from your own experiance if you was starting a project from scratch, which would be avalible for others database specific say ‘mySQL’ or so that its possible to add there own handleing class such as how phpBB does it?

    Like

  14. Very useful files search engine. indexoffiles.com is a search engine designed to search files in various file sharing and uploading sites.

    Like

  15. wow…nice tutorial, thanks.

    Like

  16. Apologies if someone has mentioned this already, but referring to oversight 1, this is the way the previous developer at my company coded our system.

    The problem is, anytime that a query the error comes from the same line (obviously, because its run from the same line). This is really frustrating, because you have no way of telling where the real error is coming from.

    Like

  17. Weight Loss Pills, weight loss program, quick weight loss, stay fit…
    weight loss tips

    Like

  18. On #4: Keeping it DRY, you ought to put repeatedly used stuff in an extra, included file, anyway.

    And if you look at it from the other side: what if your boss wants to change links only in SOME places? You’d have to write multiple classes or introduce some parameters. I leave it to you to decide what’s better.

    Like

  19. use CodeIgniter or another framework. Will solve most of your issues by default.

    Like

  20. Nice site, I was just talking about it with my friends last nite. It also looks really nice in the Blackberry browser, which is rare. Good job!

    Like

  21. Your bring up good point,but general use framework or pdo. thank you your share!!

    Like

  22. @Cassie: The simple DAL has no error checking. In your real DAL you would throw an exception when an error occurs. The stack trace of the exception then gives clues on where the error occured.

    Like

  23. Great.

    I couldn’t live without a MySqlDAL-type class these days.

    One thing that I just learned was with designing a $db->insert() method, I should pass it an array with keys and values. So simple, I should have thought about it years sooner 🙂

    Until now I had been constructing the INSERT statement in each set() method of my objects’ classes.

    Like

  24. Good database design is incredibly important if you want your application to scale. This means you also probably want to compile the database yourself, optimize its variables beforehand, practice good SQL coding standards, load balance, replicate, cluster . . .

    Like

  25. Now that’s a huge database. The codes work flawlessly.

    Like

  26. Hi,
    A bit confused though since I’m just starting to learn PHP.I’m basically a designer but these days it’s kind of hard not to have other skills especially programming, employers cut costs by hiring a one guy team. Thanks for the tutorial

    Like

  27. Very Nice post, quiet informative.
    Database designing problems being the buzzwords these days, one of the most common designing mistakes is the “Poor Planning / Architecture” of the database or mart. Follow the link to know more…
    http://www.sqllion.com/2010/08/database-design-and-modeling-i/

    Like

  28. Now that’s a huge database. The codes work flawlessly.

    Like

  29. Very interesting, thanks for posting

    Like

  30. I realize this article was written a while ago and you probably have a very different view on a lot of these issues, and while I totally agree with the things you said in the article, at the start, you compare PHP to ASP .NET. .NET is a framework, so it has all that stuff built into it. PHP it just a programming language.

    I’m not super familiar with ASP, but am I correct in saying that just straight (I believe they call it “classical”) ASP doesn’t really have these things, does it?

    Regardless, I don’t PHP was every meant to provide that sort of thing for you, and as others mentioned, you can use a PHP framework that was written with these things in mind. I saw someone else mention CodeIgniter, and I can second that one. It brings a lot of great practices into play (including these database things).

    Like

  31. Wow, I should reread my posts before submitting. Sorry for all those typos.

    Like

  32. I wanted to write you a bit of note in order to thank you yet again for the striking concepts you have provided above. It was so generous of you to supply unhampered precisely what a few individuals could have advertised for an ebook to end up making some profit on their own, notably since you might well have tried it in case you considered necessary. These advice additionally worked to be a good way to be sure that some people have similar zeal similar to my personal own to realize very much more with regards to this issue. I know there are lots of more enjoyable occasions up front for many who read carefully your website.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close