Page 2 of 2

Posted: Sat Jul 30, 2005 2:33 am
by Ree
very interesting.

i have a few questions for you nielsene:
DB->connect($host, $db, $user, $pass);
DB->query($queryString);
DB->getError() // some people put it in the DB some in the ResultSet to be shown next)
do i understand it correctly: the query string is passed to DB->query and the result of the query is passed to an object of ResultSet class? is it so?

DB->getError - how is this one used? does it return the error message of the last called DB function (in this case DB->connect or DB->query?

Posted: Sat Jul 30, 2005 10:48 am
by nielsene
Ree wrote:very interesting.

i have a few questions for you nielsene:
DB->connect($host, $db, $user, $pass);
DB->query($queryString);
DB->getError() // some people put it in the DB some in the ResultSet to be shown next)
do i understand it correctly: the query string is passed to DB->query and the result of the query is passed to an object of ResultSet class? is it so?
In the version I was showing, a test string $queryString is passed into DB->query(). DB->query() creates a ResultSet and returns that to the caller.
DB->getError - how is this one used? does it return the error message of the last called DB function (in this case DB->connect or DB->query?
Yes, it would return the error string of the most recent other call to any DB function. This isn't the "best" way, but its a rather simple interface that works well for many low-medium complexity projects.

Posted: Sat Jul 30, 2005 10:50 am
by theda
Now where would OOP be useful? What types of projects/scripts? Personally, I'd love to do OOP in PHP for my website, but it'd be useless... Since my code is less than 100 lines :) (Index page anyway)

Posted: Sat Jul 30, 2005 11:03 am
by nielsene
theda see the first few respnses to this thread. Ambush Commander, McGruff, and me all provided reasons that we think OOP is useful for any sized project.

I'll also add, you want to start with OOP before the complexity gets big. I'm struggling through "refactoring" -- improving the existing design/reducing duplication, without changing the end-user visible functionality -- a large application that I've been working on for years. It started out with a somewhat decent object model, but I let myself get lazy and most of the new features are heavily procedural with rampant duplication and other problemss.

Posted: Sat Jul 30, 2005 11:35 am
by Ree
nielsene wrote:
Ree wrote:
DB->connect($host, $db, $user, $pass);
DB->query($queryString);
DB->getError() // some people put it in the DB some in the ResultSet to be shown next)
do i understand it correctly: the query string is passed to DB->query and the result of the query is passed to an object of ResultSet class? is it so?
In the version I was showing, a test string $queryString is passed into DB->query(). DB->query() creates a ResultSet and returns that to the caller.
hmm... does that mean that a new instance of ResultSet is created by DB->query? maybe you could provide a code example of the same? i would really like to have a look at it.

and yes, i very much agree that one should start practising OOP with smaller projects before bigger ones come up.

Posted: Sat Jul 30, 2005 12:34 pm
by nielsene
Sure, here's something close to what I use (I use PostGreSQL so you'll have to change a few things for a different DB.

Code: Select all

class DB
  var $conn;
  var $error;

  function DB($host,$user,$pass,$dbname) {
    $this->error="";
    $this->conn = pg_connect("host=$host $user=$user password=$pass dbname=$dbname");
    if ($this->conn===FALSE) $this->error=pg_last_error($this->conn);
  }
  function getError() {
    return $this->error;
  }
  function query($query) {
    $rs = pg_query($this->conn,$query);
    $resultSet = FALSE;
    if (!$rs) $this->error=pg_last_error($this->conn);
    else $resultSet = new ResultSet($rs);
    return $resultSet;
  }
  function startTranaction() {
    $this->query("BEGIN WORK");
  }
  function rollback() {
    $this->query("ROLLBACK WORK");
  }
  function commit() {
    $this->query("COMMIT WORK");
  }
}

class ResultSet
  var $rs;
  var $index;
  function ResultSet($rs) {
    $this->index=0;
    $this->rs=$rs;
  }
  function numRows() {
    return pg_num_rows($this->rs);
  }
  function getNext() {
    $row = pg_fetch_row($this->rs,$index);
    if ($row!==FALSE)  $index++;
    return $row;
  }
}
?>
The ResultSet is slightly different than my version, I commonly need to go back through the result set or jump to certain entries so I also have getPrev and a getRowAt. In the above example, I don't really need the $index now...

So using it:

Code: Select all

<?php
$db = new DB("db.example.net","db_user","SuperSecret","my_db");
$rs = $db->query("SELECT * FROM foo;");
while ($aRow = $rs->getNext()) {
  // normal loop on a row from the DB
}
?>
Many of my objects take a ResultSet in their constructor to populate themsleves, then the ResultSet isn't exposed to the script at all.


You can also add a lost more error checking to the DB and ResultSet classes thus making them more robust, and keeping most/all of the error checking in one place.

Posted: Sat Jul 30, 2005 1:00 pm
by Ree
nice, that's basically what i thought it would look like, but having an example of the actual code helps a lot. thank you.

just now i'm thinking... i thought it's recommended to have the classes as independant as possible. it appears that you wouldn't be able to fully use DB class alone, since you absolutely need ResultSet class. is this dependance ok in this case?

Posted: Sat Jul 30, 2005 1:26 pm
by nielsene
You want to have as little coupling as possible. However is always going to be some dependence of classes on each other.

Often people will put the require_once('ResultSet.inc') at the top of their DB class file, if they are in seperate files, That makes sure that you always have it if you need it, etc

Posted: Sat Jul 30, 2005 1:37 pm
by McGruff
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;
    }
}

Posted: Sat Aug 06, 2005 2:16 am
by Ree
Nielsene: The below is your class example you posted before (i have replaced &stuff; with appropriate chars for easy checking).

Code: Select all

class DB
{
  var $conn;
  var $error;

  function DB($host,$user,$pass,$dbname)
  {
    $this->error = '';
    $this->conn = pg_connect('host=$host $user=$user password=$pass dbname=$dbname');
    if ($this->conn === FALSE) $this->error = pg_last_error($this->conn);
  }

  function getError()
  {
    return $this->error;  
  }

  function query($query)
  {
    $rs = pg_query($this->conn,$query);
    $resultSet = FALSE;
    if (!$rs) $this->error = pg_last_error($this->conn);
    else $resultSet = new ResultSet($rs);
    return $resultSet;
  }

  function startTranaction()
  {
    $this->query('BEGIN WORK');
  }

  function rollback()
  {
    $this->query('ROLLBACK WORK');
  }

  function commit()
  {
    $this->query('COMMIT WORK');
  }
}

class ResultSet
{
  var $rs;
  var $index;

  function ResultSet($rs)
  {
    $this->index = 0;
    $this->rs = $rs;
  }

  function numRows()
  {
    return pg_num_rows($this->rs);
  }

  function getNext()
  { 
    $row = pg_fetch_row($this->rs,$index);
    if ($row !== FALSE)  $index++;
    return $row;
  }
}
I have a question regarding your example. How can you call $rs->getNext() in your script? The ResultSet object is created in DB class, not in your script, so you shouldn't be able to call any of its methods in the script.

By saying script, I have in mind this:

Code: Select all

$db = new DB('db.example.net','db_user','SuperSecret','my_db');
$rs = $db->query('SELECT * FROM foo;');
while ($aRow = $rs->getNext())
{
  // normal loop on a row from the DB
}
Could you explain?

Posted: Sat Aug 06, 2005 3:16 am
by McGruff
Sorry about the & 's etc. I'm working on fixing this. There are 30,000 or so posts in total and every one has to be opened... It'll take around a week to do them all.

Posted: Sat Aug 06, 2005 9:45 am
by nielsene
The ResultSet object is created by the DB object. However, because the Query function returns the ResultSet, the caller (script in this case) can use the returned ResultSet -- calling its member functions, passing it to something else, etc.

Its one of the main points of OOP. You have classes that "know" how to deal with their own data. And its rather common to have some classes that predominatly create other classes for outside use -- in this case the DB class, in some ways, acts as a Factory to create ResultSets when needed.

Posted: Sat Aug 06, 2005 3:51 pm
by Ree
nielsene wrote:However, because the Query function returns the ResultSet
ooh, how could I've missed that :). Thanks for explaining.