You might be interested in the
data access classes article on phppatterns.com.
Here's another take on a database class which takes on more of the work. I wouldn't say it's a better approach necessarily but that's where I am at the moment.
Code: Select all
/*
Unlike the native php behaviour, new MysqlDatabase objects always open a
new connection, even if the class is instantiated multiple times with the
same parameters. If you want to re-use a single connection, pass
the object around.
*/
class MysqlDatabase // implements Database
{
var $failed_conn_error = 'Failed to connect to database server [%s].';
var $_connection; // (resource)
/*
param (string) x 3
*/
function MysqlDatabase($server, $user, $password)
{
$this->_server = $server;
$this->_connection = mysql_connect($server, $user, $password, true);
}
/*
param (string)
return (mixed)
*/
function execute($sql)
{
if(! $this->_connection) {
return false;
}
$result = mysql_query($sql, $this->_connection);
if (! $result) {
trigger_error(mysql_error($this->_connection));
}
return $result;
}
/*
param (string)
return (array/false)
*/
function &getRow($sql)
{
if($result = $this->execute($sql)) {
return mysql_fetch_assoc($result);
} else {
return false;
}
}
/*
param (string)
return (object/false)
*/
function &getRows($sql)
{
if($result = $this->execute($sql)) {
require_once(APERI_LIB . 'iterators/MysqlResultIterator.php');
return new MysqlResultIterator($result);
} else {
return false;
}
}
/*
param (string)
return (mixed)
*/
function manipulateRows($sql)
{
if(false !== $this->execute($sql)) {
return mysql_affected_rows($this->_connection);
} else {
return false;
}
}
/*
return (string/false)
*/
function lastInsertId()
{
if(false !== ($hash = $this->getRow('SELECT LAST_INSERT_ID() as id'))) {
return $hash['id'];
} else {
return false;
}
}
/*
return (string)
*/
function getError()
{
if($this->_connection) {
return mysql_error($this->_connection);
} else {
return sprintf($this->failed_conn_error, $this->_server);
}
}
/*
return (bool)
*/
function disconnect()
{
if(! $this->_connection) {
return true;
}
return mysql_close($this->_connection);
}
}
The test case (SimpleTest):
Code: Select all
class TestOfMysqlDatabase extends UnitTestCase
{
function TestOfMysqlDatabase()
{
$this->UnitTestCase();
$this->_connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS);
}
function setUp()
{
$this->_db =& new MysqlDatabase(DB_SERVER, DB_USER, DB_PASS);
$this->_query('create database testofmysqldatabase');
$this->_query('create table testofmysqldatabase.foo (bar text)');
$this->_query("insert into testofmysqldatabase.foo set bar='a'");
}
function tearDown()
{
$this->_db->disconnect();
$this->_query('drop database testofmysqldatabase');
}
function testWithFailedConnection()
{
$server = 'mirage';
$db =& new MysqlDatabase($server,'', '');
$this->assertErrorPattern('/unknown.*server.*host/i');
$this->assertIdentical($db->getError(), sprintf($db->failed_conn_error, $server));
// everything should return false
$this->assertIdentical($db->execute('show status'), false);
$this->assertIdentical($db->getError(), sprintf($db->failed_conn_error, $server));
$this->assertIdentical($db->getRow('select bar from testofmysqldatabase.foo'), false);
$this->assertIdentical($db->getError(), sprintf($db->failed_conn_error, $server));
$this->assertIdentical($db->getRows('select bar from testofmysqldatabase.foo'), false);
$this->assertIdentical($db->getError(), sprintf($db->failed_conn_error, $server));
$this->assertIdentical($db->manipulateRows('delete from testofmysqldatabase.foo'), false);
$this->assertIdentical($db->getError(), sprintf($db->failed_conn_error, $server));
$this->assertIdentical($db->lastInsertId(), false);
$this->assertIdentical($db->getError(), sprintf($db->failed_conn_error, $server));
}
function testDoNotReUseOldLinkWithSameConnectionParameters()
{
$foo =& new MysqlDatabase(DB_SERVER, DB_USER, DB_PASS);
$bar =& new MysqlDatabase(DB_SERVER, DB_USER, DB_PASS);
$foo->execute('use testofmysqldatabase');
$this->assertNotEqual(
$foo->getRow('SELECT database()'),
$bar->getRow('SELECT database()'));
$foo->disconnect();
$bar->disconnect();
}
function testExecute()
{
$this->assertTrue($this->_db->execute('show status'), true);
}
function testExecuteResturnsFalseWithBadQuery()
{
$this->assertIdentical($this->_db->execute('drop database phantom'), false);
$error_pattern = "/database\sdoesn't\sexist/i";
$this->assertErrorPattern($error_pattern);
$this->assertWantedPattern($error_pattern, $this->_db->getError());
}
function testGetRow()
{
$row = $this->_db->getRow('select bar from testofmysqldatabase.foo');
$this->assertEqual($row, array('bar'=>'a'));
}
function testGetRowReturnsFalseWithBadQuery()
{
$this->assertIdentical($this->_db->getRow('select * from non-existent-table'), false);
$error_pattern = '/You\shave\san\serror\sin\syour\sSQL\ssyntax/i';
$this->assertErrorPattern($error_pattern);
$this->assertWantedPattern($error_pattern, $this->_db->getError());
}
function testGetRows()
{
$got = $this->_db->getRows('select bar from testofmysqldatabase.foo');
$this->assertTrue(is_object($got));
$this->assertEqual($got->next(), array('bar'=>'a'));
}
function testGetRowsReturnsFalseWithBadQuery()
{
$this->assertIdentical($this->_db->getRows('select * from non-existent-table'), false);
$error_pattern = '/You\shave\san\serror\sin\syour\sSQL\ssyntax/i';
$this->assertErrorPattern($error_pattern);
$this->assertWantedPattern($error_pattern, $this->_db->getError());
}
function testManipulateRows()
{
$this->_db->execute('use testofmysqldatabase');
$this->assertIdentical($this->_db->manipulateRows('delete from testofmysqldatabase.foo'), 1);
$this->assertIdentical($this->_db->manipulateRows('delete from testofmysqldatabase.foo'), 0);
}
function testManipulateRowsReturnsFalseWithBadQuery()
{
$this->_db->execute('use testofmysqldatabase');
$this->assertIdentical(
$this->_db->manipulateRows('delete from testofmysqldatabase.does_not_exist'),
false);
$error_pattern = "/Table 'testofmysqldatabase.does_not_exist' doesn't exist/i";
$this->assertErrorPattern($error_pattern);
$this->assertWantedPattern($error_pattern, $this->_db->getError());
}
function testLastInsertId()
{
$this->_db->execute('create table testofmysqldatabase.last ' .
'(id int(11) not null auto_increment, primary key (id))');
$this->_db->manipulateRows('insert into testofmysqldatabase.last (id) values(null)');
$this->assertEqual($this->_db->lastInsertId(), 1);
$this->_db->manipulateRows('insert into testofmysqldatabase.last (id) values(null)');
$this->assertEqual($this->_db->lastInsertId(), 2);
}
function testErrorsAreClearedPerQuery()
{
$this->assertIdentical(
$this->_db->manipulateRows('delete from testofmysqldatabase.does_not_exist'),
false);
$error_pattern = "/Table 'testofmysqldatabase.does_not_exist' doesn't exist/i";
$this->assertErrorPattern($error_pattern);
$this->assertWantedPattern($error_pattern, $this->_db->getError());
$this->_db->execute('select bar from testofmysqldatabase.foo');
$this->assertEqual($this->_db->getError(), '');
}
function _query($sql)
{
$result = @mysql_query($sql, $this->_connection);
if (! $result) {
trigger_error(mysql_error($this->_connection)
. "\nsql: " . $sql . "\n");
}
return $result;
}
}