Database class

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
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Database class

Post by andre_c »

When using a database for retrieving and updating records (not for modeling or other not-so-common practices) and portability of the SQL is not important, is there a reason to put the functionality inside of a class?
When I first started programming using OOP it seemed like a good idea to handle everything with objects. I'm not so sure anymore. I like OOP and I tend to believe is the better way to program, I just want to know if there's other reasons to put the database functionality inside of a class. Do most of you do this?
crabyars
Forum Commoner
Posts: 37
Joined: Thu Jun 17, 2004 8:24 pm

Post by crabyars »

For what it's worth, I always use a database abstraction class so that if i need to change databases (from MySQL to Postgres for example) I only need to modify the queries.

The class I use is ezsql - http://php.justinvincent.com I love it, very easy to use and OO too!

I know many people like the PEAR classes also - http://pear.php.net/[/url]
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post by Maugrim_The_Reaper »

ADODB - http://adodb.sourceforge.net/

To simplify it even more you can add an additional overlay encapsulating the ADODB - or PEAR methods for querying the database. e.g.

Code: Select all

<?php
function db_connect($database_host, $database_user, $database_password, $database, $database_persistent)
{
	global $CONFIG;
	$db = &ADONewConnection($CONFIG['db_type']);
	$db->autoRollback = true;
	// use postgres7 usual connection through single host parameter
	if($CONFIG['db_type'] == "postgres7") 
	{
		$db->PConnect("host=$database_host port=$CONFIG[database_port] dbname=$database");
	}
	elseif($database_persistent == 1) 
	{
		$db->PConnect($database_host, $database_user, $database_password, $database);
	}
	else
	{
		$db->Connect($database_host, $database_user, $database_password, $database);
	}
	return $db;
}

function db($string,$filename,$linenum) 
{
	global $query, $db;
	$query = $db->Execute($string);
	if ($query == false) 
	{ 
        print '<h3>SQL SELECT [ db() ] Query Error:</h3><p>'.$db->ErrorMsg().'</p><p><h3>Query:</h3></p><p>'.$string.'</p><p><h3>File:</h3></p><p>'.$filename.'<br />Line Number: <b>'.$linenum.'</b></p>';
		exit();
	} 
}

// function for fetching each result row - one per use of dbr()
function dbr() 
{
	global $query, $db;
	$fetched = $query->fields;
	$query->MoveNext();
	return $fetched;
}
?>
Depends on whether you think the simplification is worth the expense of course...
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

My 2 cents... Using a database abtraction class can be useful. It should not however totally replace the knowledge of how to handle a database, if you needed to, without the class. You should always know how to do it yourself rather than rely on a class you do not understand at all. This goes really for any class.

Also bear in mind that if you need to provide example code (e.g in this forum) you would also need to explain the methodology of the database handling.
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post by Maugrim_The_Reaper »

Given how simple any non-portable database set of methods are likely to be, I don't really see any benefit to using OOP. A list of functions in this case are just as useful as a full class.

And no, database abstraction is not something you need. I use it primarily for portability, and a few other side benefits - but it's overkill for a simple set of database functions...
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Post by andre_c »

So, I assume that the only benefit of using OOP for database is abstraction, correct?
But when you do that you're losing features specific to the database that you are using.
Can I assume then that if you're not looking for abstraction, then using the functions is just as good as using objects for the database?
Is there some other benefit from managing the database through an object?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

i've written some classes that represent a "datastore", in most systems that is synonim for a database. http://timvw.madoka.be/datastores/std.d ... class.phps

then i've extended that class to a pageable datastore it allows me to retrieve entities in pages. http://timvw.madoka.be/datastores/pagea ... class.phps

then i've implemented a mysql and a mysqli backend for that pageable datastore.
http://timvw.madoka.be/datastores/mysql ... class.phps
http://timvw.madoka.be/datastores/mysql ... class.phps

and in concreto i've written 2 concrete classes (by implementing the datadictionary methods)
http://timvw.madoka.be/datastores/city. ... class.phps
http://timvw.madoka.be/datastores/count ... class.phps

by using this abstraction, all my scripts can use the create, read, update and delete function of the datastore. all validation and dml generation (usually sql) is done by the classes ;)
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Eclipse is also worth a look.

OOP is used to encapsulate stuff related to the database object - in Eclipse that's the connection, querying, errors & etc. OOP doesn't mean you lose db specific features (Eclipse for example has different classes for different dbs).

Further reading:

http://www.phppatterns.com/index.php/ar ... ew/25/1/1/
http://www.martinfowler.com/eaaCatalog/ (see data source architectural patterns)
jl
Forum Commoner
Posts: 53
Joined: Tue Nov 09, 2004 12:05 am

Post by jl »

andre_c wrote:So, I assume that the only benefit of using OOP for database is abstraction, correct?
...
Personally I can't think of any situation when I wouldn't want to abstract database access (through OO or any other method) because having the option to be able to easily change the underlying database or data-access system (e.g. if you wanted/needed to convert from RDBMS to flat text files for e.g.) is invaluable.

I have my own db class that can be used for MySQL or PostgresQL access. It supports some PostgresQL features that aren't included in MySQL, like transactions, but still would greatly assist in the change either way between MySQL and PostgresQL, or from either to a new db.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Abstraction is definitely an advantage, but there are certainly others. I've got my own class that has a few advantages not provided by inline connecting, in as few lines of code.

For 1, it loads connection variables from a config file. This way, I don't have to re-enter connection data on each page - saving myself some time in coding, and saving time if I ever have to change the database name, password or username (it's happened more often than it should).

Second, error reporting is much nicer. The way my current class is set up, if a query fails, it very nicely outputs an organized error message telling me what the error returned was, the query that generated the error, and the string I've passed along, saying what I was doing in the query. It really helps to debug stuff.

Finally, I've got a run-time file for my class that sets up all the connections and selects the correct DB for me. So, if I want to access a database from a given page, I just include(db.php) at the beginning of the file, then I can use 1 line to actually do the query. A typical situation could look like this:

Code: Select all

<?php
include('db.php');
//some code here..
//more code
$query = <<<SQL
SELECT
   *
FROM
   users
WHERE
   clue > 0
SQL;

$result = $DB->sql_query($query,'finding non-clueless users');
$count = $DB->count_rows($result);
echo $count;

?>
In continuing with the joke, this page would just dump '0'.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

i didn't see real advantages of having an abstraction, untill we got a mysql mirror (reading happens on mirror, create/update/delete happens on the main). With somewhat of an abstraction class, the correct server can be choosen easily

Code: Select all

function query($query)
if (preg_match(/^INSERT|UPDATE|DELETE/))
{     
       mysql_query($query, $this->main);
}
else
{
       mysql_query($query, $this->mirror);
}
another advantage was when i switched from mysql to mysqli library

Code: Select all

function query($query)
{
     mysqli_query($this->main, $query);
}
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Personally I think that some custom fns - along the lines of Timvw's examples above - are a minimum. You won't see the need for abstraction until you get bitten one day.
Post Reply