how to separate sql from php code

Question

I have a class that helps me to handle users. For example:

$user = new User("login","passw");
$name = $user->getName();
$surname = $user->getSurname();
$table = $user->showStats();

All these methods have SQL queries inside. Some actions require only one sql queries, some - more than one. If database structure changes - it will be difficult to change all queries (class is long). So I thought to keep SQL queries away from this class. But how to do this?

After reading this question I've known about Stored Procedures. Does it mean, that now one action requires only one SQL query (call of Stored Procedure)? But how to organize separation sql from php? Should i keep sql-queries in an array? Or may be it should be an sql-queries class. If yes, how to organise this class (maybe what pattern I should learn)

Answer 1

This is a surprisingly large topic, but I have a few suggestions to help you on your way:

You should to look into object-relational mapping, in which an object automatically generates SQL queries. Have a look at the Object-Relational Mapping and Active Record articles for an overview. This will keep your database code minimal and make it easier if your table structure changes.

But there is no silver bullet here. If your schema changes you will have to change your queries to match. Some people prefer to deal with this by encapsulating their query logic within database views and stored procedures. This is also a good approach if you are consistent, but keep in mind that once you start writing stored procedures, they are going to be tied heavily to the particular database you are using. There is nothing wrong with using them, but they are going to make it much more difficult for you to switch databases down the road - usually not an issue, but an important aspect to keep in mind.

Anyway, whatever method you choose, I recommend that you store your database logic within several "Model" classes. It looks like you are doing something similar to this already. The basic idea is that each model encapsulates logic for a particular area of the database. Traditionally, each model would map to a single table in the DB - this is how the Ruby on Rails active record class works. It is a good strategy as it breaks down your database logic into simple little "chunks". If you keep all of the database query logic within a single file it can quickly grow out of control and become a maintenance nightmare - trust me, I've been there!

To get a better understanding of the "big picture", I recommend you spend some time reading up on web Model-View-Controller (MVC) architecture. You will also want to look at the established PHP MVC frameworks, such as CodeIgniter, Kohaha, CakePHP, etc. Even if you do not use one - although I recommend you do - it would be helpful to see how these frameworks organize your code.

Answer 2

I would say you should look into implementing the "repository" design pattern in your code.

A good answer to how to implement this would be too long for this space, so I'll post a couple of PHP-oriented references:

travis swicegood -- Repository Pattern in PHP

Jon Lebensold -- A Repository Pattern in PHP

Answer 3

You are on the right lines if you use separation of concerns to separate your business logic from your data access logic you will be in a better place.

Answer 4

Judging by your "there are already 2K lines of code" statement, you're either maintaining something, or midway through developing something.

Both Faust and Justin Ethier make good recommendations - "how should I separate my database access from my application code" is one of the oldest, and most-answered, questions in web development.

Personally, I like MVC - it's pretty much the default paradigm for web development, it balances maintainability with productivity, and there are a load of frameworks to support you while you're doing it.

You may, of course, decide that re-writing your app from scratch is too much effort - in which case the repository pattern is a good halfway house.

Either way, you need to read up on refactoring - getting from where you are to where you want to be is going to be tricky. I recommend the book by Fowler, as a starter.

Could you explain more about why your database schema may change? That's usually a sign of trouble ahead.....