DB 'Getter' - Good/Bad idea?

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
Sphenn
Forum Commoner
Posts: 48
Joined: Sun Jul 17, 2005 8:08 pm
Location: Winnipeg, MB

DB 'Getter' - Good/Bad idea?

Post by Sphenn »

Hi,

I'm just trying to get into the whole programming patterns with PHP. I'm wondering if anyone has any experience with any patterns that use a DB 'Interface' class, and if it was a good idea? I'm trying to build an application that is meant to be expanded by others, so I'm trying to decide if it's worth it to add another layer to my application

ie. main->dbInterface->DBAL->presentation

The only benefit I can see is that others don't need to know SQL syntax, but don't most able developers know it anyway? If I was to use this interface, basic SELECT queries for example could be shown like:

Code: Select all

$db->get(array('config_name', 'config_value'), 'config');
It seems that this would be more intuitive, but if you need a relatively complex query, it can become

Code: Select all

$db->get(array('config_name', 'config_value'), 'config', 'config_value', '3', '`config_name DESC`', '20');
This would create the same query as writing

Code: Select all

SELECT config_name, config_value
    FROM config
        WHERE config_value = 3
   ORDER BY `config_name` DESC
        LIMIT 20;
I guess what I'm asking is, do you think that it's worthwhile to have an interface that can construce SQL queries like this, or is it easier to just code them like normal. I should note that I think using the interface would make it easier to modify (change queries, DBAL, etc)

Anyway, thanks for reading

Sphenn
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post by Maugrim_The_Reaper »

I think removing SQL from equation has other benefits also. Some SQL is very specific, and other parts extremely repetitive. Adding an extra layer while appearing to do not a whole lot, can simplify coding. In my own recent apps, fetching a user from an ID is a matter of:

Code: Select all

$user = new User();
$user->getByPk(1);
Creating a user?

Code: Select all

$user = new User();
$user->setName('Sphenn');
$user->save();
Updating multiple users?

Code: Select all

$where = new User();
$where->setStatus('invalid');
$userquery = new User();
$userquery->delete($where); //delete users where status = invalid
Never underestimate the power of simplicity. I can power up a lot of SQL actions by mixing and matching simple User objects with a little DataAccess class in the background constructing the actual SQL and passing it to a database abstraction library.

If you need even more complex operations, you can just extend the base User class with additional methods, thereby quickly building up a powerful Model (the M in MVC). Maybe setting up $where conditions is too much and you need to simplfy further...

Code: Select all

public function deleteUsersWithStatus($status='invalid') {
	$where = new User();
	$where->setStatus($status);
	$userquery = new User();
	$userquery->delete($where);
}
Or at this point you can lump in actual SQL if you wish - might need to if the logic starts becoming really convoluted. The point is you simplify, identify common operations, and simplify those too. All the while you creating a Model with a set if methods for tasks which wuld normally leave you trailing whole sections of SQL queries, loops, and iterations across your app.

I'll just point out I made a tutorial for something similar... It's simple, but I have a more complex one in use across three projects at the moment. PHP DataObjects: SQL-free Business Logic
Sphenn
Forum Commoner
Posts: 48
Joined: Sun Jul 17, 2005 8:08 pm
Location: Winnipeg, MB

Post by Sphenn »

Hmm.... that makes a lot of sense.

One thing though, is that I won't really know what types of actions I'll need, mostly becuase the system I'm designing is meant to be extended. So, in addition to having methods to do common actions, do you think it's worthwhile to have very basic SQL wrappers?

ie

Code: Select all

$db->updateRow($rowName, $rowValue, $rowNewValue);
$db->insertRow(...);
This way, very common actions have their own method call, simple SQL commands can be executed, and for complex SQL, it can be embedded directly in the app, keeping the complexity of the $db class down.

Thanks for your comments.

Sphenn
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Anything that abstracts away from having to write database specific SQL by the "end user" (developer in this case) is a good idea™.
Sphenn
Forum Commoner
Posts: 48
Joined: Sun Jul 17, 2005 8:08 pm
Location: Winnipeg, MB

Post by Sphenn »

feyd wrote:Anything that abstracts away from having to write database specific SQL by the "end user" (developer in this case) is a good idea™.
Very definitive :D

I guess I know where to head now then

Thanks,

Sphenn
User avatar
TheMoose
Forum Contributor
Posts: 351
Joined: Tue May 23, 2006 10:42 am

Post by TheMoose »

feyd wrote:Anything that abstracts away from having to write database specific SQL by the "end user" (developer in this case) is a good idea™.
Thank jebus! I thought I was programming things weird when I was creating classes and functions that basically formed complete SQL queries from just a few arguments.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: DB 'Getter' - Good/Bad idea?

Post by Christopher »

Sphenn wrote:I'm just trying to get into the whole programming patterns with PHP. I'm wondering if anyone has any experience with any patterns that use a DB 'Interface' class, and if it was a good idea? I'm trying to build an application that is meant to be expanded by others, so I'm trying to decide if it's worth it to add another layer to my application
There are a number of patterns and concepts related to this. Here are a few off the top of my head:

Connection Object
Query Object
Record Set
Row Data Gateway
Table Data Gateway
Table Module
Active Record
Data Mapper
Object-relational mapping
(#10850)
Sphenn
Forum Commoner
Posts: 48
Joined: Sun Jul 17, 2005 8:08 pm
Location: Winnipeg, MB

Post by Sphenn »

Thanks for those. Off to phpPatterns and Google I go ;)

Sphenn
Post Reply