[PHP5] MySQL class with Result Iterator

Small, short code snippets that other people may find useful. Do you have a good regex that you would like to share? Share it! Even better, the code can be commented on, and improved.

Moderator: General Moderators

User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

[PHP5] MySQL class with Result Iterator

Post by Chris Corbyn »

This was inspired by something ~arborint mentioned in another thread regarding return result objects from MySQL classes rather than returning the actual result resource.

The DB class provides the connection to the server, runs queries, escapes data and retreives information about the server. When running queries a resultset object is returned which contains some iterator methods and also contains some information about the query, such as the number of rows returned, or the ID of the record that was added in an INSERT query.

Overview of methods in DB:
  • __construct($host, $user, $pass, $db=null):
    Creates and instance of the object and connects to the server. If $db is not set, no database will be selected initially.
  • connect($host, $user, $pass):
    Connect to a server with username and password
  • disconnect():
    Close the connection to the server
  • getInstance():
    Called statically. Used to retreive a singleton of the DB class.
  • selectDb($db):
    Change databases or select an initial database
  • getDbName():
    Get the name of the database you're currently connected to
  • isConnected():
    Check if the DB class has a connection open
  • getError():
    Return the last error reported by MySQl as per mysql_error()
  • query($query_string):
    Run a query against the database and return as resultset object of type DB_Result
  • info():
    Get info about the server as reported by mysql_get_server_info()
  • status():
    Retreive information about the current system status in array form based upon the string returned by mysql_stat()
  • escape($string):
    Escapes data through mysql_real_escape_string() to make it safer to use in queries
Overview of methods in DB_Result:
  • get():
    Returns the actual mysql result resource as provided by mysql_query()
  • next():
    Move the iterator forward by one row. Returns TRUE on success.
  • prev():
    Move the iterator backwards by one row. Returns TRUE on success.
  • end():
    Check if the iterator has reached the end of the resultset. Returns TRUE if the end is reached.
  • start():
    Check if the iterator has reached the beginning of the resultset. Returns TRUE if the beginning is reached.
  • first():
    Move the iterator to the first row of the resultset
  • last():
    Move the iterator to the last row of the resultset
  • goto($row_number):
    Move the iterator to $row_number. Numbering starts from zero.
  • position():
    Return the position of the iterator. This will be the row number in the resultset, counting from zero.
  • length():
    Returns the number of rows in the resultset.
  • id():
    Returns the ID of the inserted record when an INSERT query is run. This will be -1 on failure.
  • affectedRows():
    Get the number of changed rows from an UPDATE, or DELETE query.
To retreive columns from the current row, simply fetch them like properties:

Code: Select all

 
echo $result->my_field;
 
//Or if your column name has special characters or spaces in it
echo $result->{'field @ name'};
 
And the code....

DB.php

Code: Select all

<?php
 
/**
 * A MySQL Database class for PHP5
 *
 * @package DB_Iterator
 * @version 0.0.1
 * @author  Chris Corbyn
 * @date    26th July 2006
 * @license http://www.gnu.org/licenses/lgpl.txt Lesser GNU Public License
 *
 * @copyright Copyright &copy; 2006 Chris Corbyn - All Rights Reserved.
 * 
 *   This library is free software; you can redistribute it and/or
 *   modify it under the terms of the GNU Lesser General Public
 *   License as published by the Free Software Foundation; either
 *   version 2.1 of the License, or any later version.
 *
 *   This library is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *   Lesser General Public License for more details.
 *
 *   You should have received a copy of the GNU Lesser General Public
 *   License along with this library; if not, write to
 *
 *   The Free Software Foundation, Inc.,
 *   51 Franklin Street,
 *   Fifth Floor,
 *   Boston,
 *   MA  02110-1301  USA
 *
 *    "Chris Corbyn" <chris@w3style.co.uk>
 *
 */
 
/**
 * A basic DB connection class returning
 * resultset objects following an iterator pattern
 */
class DB
{
    /**
     * The database connection resource
     * @var resource db
     */
    private $conn;
    /**
     * The database name itself
     * @var string database
     */
    private $db;
    /**
     * An instance of a singleton
     * @var object DB
     */
    private static $instance = null;
    
    /**
     * Constructor
     * @param string server
     * @param string username
     * @param string password
     * @param string db name
     */
    public function __construct($host, $user, $pass, $db=false)
    {
        $this->connect($host, $user, $pass);
 
        if ($this->conn && $db) $this->selectDb($db);
    }
    /**
     * Used for retreiving an instance of a singleton if wanted
     * @return object DB
     */
    public static function getInstance($host, $user, $pass, $db)
    {
        if (self::$instance === null)
        {
            self::$instance = new DB($host, $user, $pass, $db);
        }
        return self::$instance;
    }
    /**
     * Connect to database (stored internally)
     * @param string server
     * @param string username
     * @param string password
     */
    public function connect($host, $user, $pass)
    {
        $this->conn = @mysql_connect($host, $user, $pass);
    }
    /**
     * Change databases
     * @param string database
     */
    public function selectDb($db)
    {
        @mysql_select_db($db, $this->conn);
        $this->db = $db;
    }
    /**
     * Check which db is currently used
     * @return string database
     */
    public function getDbName()
    {
        return $this->db;
    }
    /**
     * Check if the connection is successful
     * @return boolean
     */
    public function isConnected()
    {
        return is_resource($this->conn);
    }
    /**
     * Close the connection
     */
    public function disconnect()
    {
        @mysql_close($this->conn);
    }
    /**
     * Fetch the last error
     * @return string error
     */
    public function getError()
    {
        return mysql_error($this->conn);
    }
    /**
     * Run a query against the database and return
     * a resultset iterator object
     * @return object DB_Result
     */
    public function query($query)
    {
        $result = @mysql_query($query);
        $insert = false;
        if (strpos(trim(strtolower($query)), 'insert') === 0) $insert = true;
        return new DB_Result($result, $this->conn, $insert);
    }
    /**
     * Retreive info about the server
     * @return string info  
     */
    public function info()
    {
        return mysql_get_server_info($this->conn);
    }
    /**
     * Get details about the current system status
     * @return array details
     */
    public function status()
    {
        return explode('  ', mysql_stat($this->conn));
    }
    /**
     * Escape a string to make it safe for mysql
     * @return string escaped output
     */
    public function escape($string)
    {
        return mysql_real_escape_string($string, $this->conn);
    }
}
 
?>
DB_Result.php

Code: Select all

<?php
 
/**
 * A DB_Result object & iterator
 *
 * @package DB_Iterator
 * @version 0.0.1
 * @author  Chris Corbyn
 * @date    26th July 2006
 * @license http://www.gnu.org/licenses/lgpl.txt Lesser GNU Public License
 *
 * @copyright Copyright &copy; 2006 Chris Corbyn - All Rights Reserved.
 * 
 *   This library is free software; you can redistribute it and/or
 *   modify it under the terms of the GNU Lesser General Public
 *   License as published by the Free Software Foundation; either
 *   version 2.1 of the License, or any later version.
 *
 *   This library is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *   Lesser General Public License for more details.
 *
 *   You should have received a copy of the GNU Lesser General Public
 *   License along with this library; if not, write to
 *
 *   The Free Software Foundation, Inc.,
 *   51 Franklin Street,
 *   Fifth Floor,
 *   Boston,
 *   MA  02110-1301  USA
 *
 *    "Chris Corbyn" <chris@w3style.co.uk>
 *
 */
 
/**
 * DB_Result class.  Provides an iterator wrapper
 * for working with a MySQL result.
 */
class DB_Result
{
    /**
     * The ID that was created as a result
     * of inserting a row
     * @var int id
     */
    private $id;
    /**
     * The size of the resultset
     * @var int length (num rows)
     */
    private $length = 0;
    /**
     * The result itself
     * @var result result
     */
    private $result;
    /**
     * The row at our current position in the
     * resultset
     * @var array row
     */
    private $currentRow = array();
    /**
     * Current position
     * @var int position
     */
    private $position = 0;
    /**
     * The last position we were at when we read from the resultset
     * @var int last position
     */
    private $lastPosition = 0;
    /**
     * If we have pulled out any rows or not yet
     * @var boolean Got rows
     */
    private $gotResult = false;
    /**
     * The affected number of rows from the query
     * @var int num rows
     */
    private $affectedRows = -1;
    
    /**
     * Constructor
     * @param result result
     * @param resource connection
     * @param boolean insert query
     */
    public function __construct(&$result, &$conn, $insert=false)
    {
        $this->result = $result;
        $this->conn = $conn;
        
        if ((@mysql_num_rows($this->result) >= 0 && $this->result !== false) || $insert)
        {
            if ($insert) $this->id = mysql_insert_id($conn);
            $this->length = (int) @mysql_num_rows($this->result);
            $this->affectedRows = mysql_affected_rows($conn);
        }
    }
    /**
     * Magic overloaded method.
     * Returns data from the resultset
     * @param string column
     */
    public function __get($field)
    {
        if ($this->lastPosition != $this->position || !$this->gotResult)
        {
            mysql_data_seek($this->result, $this->position);
            $this->currentRow = mysql_fetch_assoc($this->result);
            $this->lastPosition = $this->position;
            $this->gotResult = true;
        }
        return $this->currentRow[$field];
    }
    /**
     * Get the insert id
     */
    public function id()
    {
        return $this->id;
    }
    /**
     * Size of the resultset
     */
    public function length()
    {
        return $this->length;
    }
    /**
     * Go to the first row of the resultset
     * @return boolean
     */
    public function first()
    {
        if ($this->length > 0)
        {
            $this->goto(0);
            return true;
        }
        else return false;
    }
    /**
     * Go to the last row of the resultset
     * @return boolean
     */
    public function last()
    {
        return $this->goto($this->length-1);
    }
    /**
     * Check if we've reched the end of the resultset
     * @return boolean
     */
    public function end()
    {
        if ($this->position >= $this->length) return true;
        else return false;
    }
    /**
     * Check if we're at the start of the resultset
     * @return boolean
     */
    public function start()
    {
        return ($this->position < 0);
    }
    /**
     * Move to the next row of the resultset
     * @return boolean
     */
    public function next()
    {
        return $this->goto($this->position+1);
    }
    /**
     * Move to the previous row in the resultset
     * @return boolean
     */
    public function prev()
    {
        return $this->goto($this->position-1);
    }
    /**
     * Go to a specified row in the resultset
     * Row numbering starts at zero
     * @param int row
     * @return boolean
     */
    public function goto($position)
    {
        if ($position < -1 || $position > $this->length) return false;
        else
        {
            $this->position = $position;
            return true;
        }
    }
    /**
     * Get the affected number of rows
     */
    public function affectedRows()
    {
        return $this->affectedRows;
    }
    /**
     * Get the result resource itself
     */
    public function &get()
    {
        return $this->result;
    }
    /**
     * Get the current position
     */
    public function position()
    {
        return $this->position;
    }
}
 
?>
... And a quick example....

Code: Select all

<?php
 
require_once('DB.php');
require_once('DB_Result.php');
 
$db = new DB('localhost', 'user', 'pass', 'db_test');
 
/*
 create table foo
(
    id int auto_increment primary key,
    one varchar(255),
    two varchar(255)
)
 */
 
$result = $db->query("select * from foo");
 
//Go forwards through the resultset
for ($result->first(); !$result->end(); $result->next())
{
    echo $result->one.' '.$result->two.'<br />';
}
 
//We can find the affected rows and the id of the insert row when running
//  insert, update & delete queries
$result = $db->query("insert into foo (one, two) values ('zip', 'button')");
echo $result->id(); //The ID of the record we just added
 
$result = $db->query("select * from foo");
 
//Go backwards through the resultset
for ($result->last(); !$result->start(); $result->prev())
{
    echo $result->one.' '.$result->two.'<br />';
}
 
//Get the number of rows
echo $result->length();
 
//Jump to a certain row
$result->goto(1);
echo $result->one;
 
?>
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post by neophyte »

Nice work D11. I'm sitting here looking at your code like a total dork think "wow" now that's pretty code...
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Post by daedalus__ »

I was totally working on something similar, mind if I borrow ideers?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Daedalus- wrote:...ideers?
Is that somwthing new from Apple?

or did you mean ideas? :P
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Daedalus- wrote:I was totally working on something similar, mind if I borrow ideers?
Go for it :)
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Post by daedalus__ »

Pimptastic wrote:
Daedalus- wrote:...ideers?
Is that somwthing new from Apple?

or did you mean ideas? :P
It's some new from DaedaCorp
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Code: Select all

       /**
         * Go to a specified row in the resultset
         * Row numbering starts at zero
         * @param int row
         * @return boolean
         */
        public function goto($position)
        {
                if ($position < -1 || $position > $this->length) return false;
                else
                {
                        $this->position = $position;
                        return true;
                }
        }
Shouldn't that be

Code: Select all

if ($position < 0 || $position > $this->length) return false;

Code: Select all

$iterator->goto(-1); //will set to 'row' -1
:)
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Yes and no. I need to re-think my iterator logic. I ended up using -1 to get the backwards iteration stuff working.... A little re-think of the logic and it can be zero though :)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Ah, yes gotcha.

I didn't read through the flow fully, I just noticed how the comment said "row numbering starts at 0" :)
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

IDEA! Make this implement Iterator, so you can foreach() it...
I'm going to do this when I get home, so I'll post it when I do. :)
Yura
Forum Newbie
Posts: 3
Joined: Fri Jan 19, 2007 7:11 am

Post by Yura »

 
d11wtq,
First, thank you for this class I'm using it in several projects of mine but I found out that it is extremely slow in going through large resultsets.
 
using class methods:

Code: Select all

 
$rs = $db->query('SELECT id, title FROM news ORDER BY id ASC LIMIT 50000');
$time_start = microtime(1);
for($rs->first(); !$rs->end(); $rs->next())
{
   $t[$rs->id] = $rs->title;
}
$time_end = microtime(1);
echo($time_end - $time_start); echo "seconds \n";
 
168,27008008957 seconds

using php functions for resultsets:

Code: Select all

 
$rs = $db->query('SELECT id, title FROM news ORDER BY id ASC LIMIT 50000');
$time_start = microtime(1);
$my_rs =& $rs->get();
while($row = mysql_fetch_object($my_rs))
{
   $t[$row->id] = $row->title;
}
$time_end = microtime(1);
echo($time_end - $time_start); echo "seconds \n";
 
0,32234001159668 seconds

I don't know that the problem is - mysql_data_seek or object properties overloading in php. Maybe you know the way to improve speed of your class. Thanks.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Interesting. I'd guess it's all the seeking that's going on but if someone fancies optimizing it then great :) I'd look more closely but I'm mega busy right now :( (do i keep saying that? 8O Well at least I have a nice bottle of red with me while I work on this rainy friday evening!)
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 »

While this might not work for huge result sets - might it be better to read the result set in as an array? Then get() won't have to seek from the beginning of the result set every time - it can just iterate through an array.

Does that make sense or am I off the wall?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

makes sense to me
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

I remember looking at this code back when it was posted. It really should not do the mysql_data_seek() when you are simply fetching the next record -- so the code should check to see that you are asking for a position that is not sequential, which is the exception.

Likewise, the DB_Result initialization should probably not do mysql_insert_id(), mysql_num_rows(), mysql_affected_rows() unless the programmer explicitly asks for them.
(#10850)
Post Reply