[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
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

If I get a chance, I'll rewrite this to both implement the iterator SPL interface, as well as hopefully optimize it, but I probably won't get a chance to do that for a while... I'm very busy. I've been meaning to though. :oops: I have already made it implement iterator, but it is buggy and It uses the same mysql_data_seek methodology, which I agree is flawed (not that I know how to write it better, but I'll at least try) :)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

The Ninja Space Goat wrote:It uses the same mysql_data_seek methodology, which I agree is flawed (not that I know how to write it better, but I'll at least try) :)
All you need to do is not seek if you are fetching the next record. I guess with this code you would just need to add and if() around the seek:

Code: Select all

       public function __get($field)
        {
                if ($this->lastPosition != $this->position || !$this->gotResult)
                {
                        if ($this->lastPosition + 1 != $this->position)
                        {
                                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];
        }
 
(#10850)
Yura
Forum Newbie
Posts: 3
Joined: Fri Jan 19, 2007 7:11 am

Post by Yura »

Thanks a lot, arborint. It now works much faster.
limit 50
class - 0,0011911392211914 seconds
php - 0,00025510787963867 seconds

limit 200
class - 0,0033228397369385 seconds
php - 0,0011029243469238 seconds

limit 500
class - 0,0070910453796387 seconds
php - 0,002432107925415 seconds

limit 1000
class - 0,014362096786499 seconds
php - 0,0048739910125732 seconds

limit 5000
class - 0,089248180389404 seconds
php - 0,041565179824829 seconds

limit 10000
class - 0,15527510643005 seconds
php - 0,056180000305176 seconds

limit 25000
class - 0,39829897880554 seconds
php - 0,13565587997437 seconds

limit 50000
class - 0,77564215660095 seconds
php - 0,27771019935608 seconds
Yura
Forum Newbie
Posts: 3
Joined: Fri Jan 19, 2007 7:11 am

Post by Yura »

I also modified query method a little, maybe someone will find this mod :D useful.

Code: Select all

 
public function query($query, $sql_values = null, $return = true)
{
    if(!empty($sql_values) AND substr_count($query, '?') == count($sql_values))
    {
        $offset = 0;
        while(list(,$v) = each($sql_values))
        {
            if(is_string($v)) $v = "'".$this->escape($v)."'";
            elseif($v === null) $v = 'NULL';
            elseif(is_bool($v)) $v = $v ? 1 : 0;
            // for some locales php uses comma as a divider in float to string conversion
            elseif(is_float($v)) $v = str_replace(',', '.', $v);
            
            $from = strpos($query, '?', $offset);
            $query = substr_replace($query, $v, $from, 1);
            // '?' in a replaced string will not break anything
            $offset = $from + strlen($v);
        }
    }
    
    $result = mysql_query($query, $this->conn);
 
    // returns DB_Result object only if necessary
    // (in case you need to make "silent" query)
    if($return)
    {
        // select, insert, update etc.
        preg_match('/^[A-Za-z]+/', ltrim($query), $statement);
        $statement = strtolower($statement[0]);
        return new DB_Result($result, $this->conn, $statement);
    }
}
 
The usage is something like this:

Code: Select all

 
$db->query('INSERT INTO items(title, views, info_update, fl_test, seen) VALUES(?, ?, NOW(), ?, ?)', array('a string ? with several ? marks in it?', 123, 0.004, null), false);
 
$rs = $db->query('SELECT * FROM items WHERE id=?', array($my_id));
 
heidi
Forum Newbie
Posts: 1
Joined: Mon Apr 23, 2007 11:13 am

Post by heidi »

I'm using this classes but I received some errors so I tried solving that by modifying two things:

-> in DB.php I changed the function query

Code: Select all

       public function query($query)
        {
                $result = mysql_query($query);
                $insert = false;
                $update = false;
                $delete = false;
                if (strpos(trim(strtolower($query)), 'insert') === 0) $insert = true;
                if (strpos(trim(strtolower($query)), 'update') === 0) $update = true;
                if (strpos(trim(strtolower($query)), 'delete') === 0) $delete = true;
                return new DB_Result($result, $this->conn, $insert, $update, $delete);
        }
(the original: )

Code: Select all

       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);
        }
-> and in DB_Result I changed the constructor

Code: Select all

       public function __construct(&$result, &$conn, $insert=false, $update=false, $delete=false)
        {
                $this->result = $result;
                $this->conn = $conn;
                
                if ($this->result !== false) {
                    if ($insert || $update || $delete) {
                        $this->affectedRows = mysql_affected_rows();
                    }
                    else {
                        $this->length = (int) mysql_num_rows($this->result);
                    }
                    
                    if ($insert) {
                        $this->id = mysql_insert_id();
                    }
                }
        }
 
(the original: )

Code: Select all

       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);
                }
        }
I'm rather new to PHP so I was wondering if anybody could tell me whether my changes actually make sense or not? Thx!
ReDucTor
Forum Commoner
Posts: 90
Joined: Thu Aug 15, 2002 6:13 am

Post by ReDucTor »

few little additions, which would be nicer

Code: Select all

class DB_Result implements Iterator {
    function current() {
        mysql_data_seek($this->result,$this->position);
        $this->lastposition = $this->position;
        $this->gotResult = true;
        return mysql_fetch_assoc($this->result);
    }
    function next() {
        $this->position++;
    }
    function key() {
        return $this->position;
    }
    function valid() {
        return ($this->position < 0 || $this->position > $this->length)
    }
    function rewind() {
        $this->position--;
    }
}
note no valid checking in next and rewind, as foreach will check with valid()

btw. This is untested..and only an example

Code: Select all

foreach($result as $row) {
    print_r($row);
}
Or if you wanna go all out you can use mysql_fetch_object(), and even use a class name in it, which is your data handler for the type of data you want.

e.g. your code could be cleaner with

Code: Select all

$q = $db->query(..);
$q->setHandler('news');
foreach($q as $itm)
    print $itm;
and your news class would handle the rest using __toString()

I've already got a bunch of classes like that built, but they are at home and I am at work.
Carsten
Forum Newbie
Posts: 5
Joined: Wed Nov 09, 2005 6:46 am

Post by Carsten »

Hello!

I was just searching for a class like that and modified it so it now implements Iterator and returns the results as an object and not as an associative array like in the original version. As I only need the Iterator capabilities I removed the start(), last(), goto() etc. functions from the original version, took the additions by ReDucTor and fixed minor bugs from them. Thanks to all! :)

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 implements Iterator
{
    /**
     * 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)
        {
            if ($this->lastPosition + 1 != $this->position)
            {
                mysql_data_seek($this->result, $this->position);
            }
            #$this->currentRow = mysql_fetch_assoc($this->result);
            $this->currentRow = mysql_fetch_object($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;
    }
 
    /**
     * 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;
    }
 
    /**
     * return current result row
     *
     * @return object
     */
    function current()
    {
        if ($this->lastPosition + 1 != $this->position)
        {
            mysql_data_seek($this->result, $this->position);
        }
        $this->lastposition = $this->position;
        $this->gotResult = true;
        return mysql_fetch_object($this->result);
    }
 
    /**
     * Get next position
     */
    function next()
    {
        $this->position++;
    }
 
    /**
     * Get aktual key (=position)
     *
     * @return int
     */
    function key()
    {
        return $this->position;
    }
 
    /**
     * Is actual position valid?
     *
     * @return bool
     */
    function valid()
    {
        return ($this->position < $this->length);
    }
 
    /**
     * Return to first position
     */
    function rewind()
    {
        $this->position = 0;
    }
}
?>
 
User avatar
thomas777neo
Forum Contributor
Posts: 214
Joined: Mon Mar 10, 2003 6:12 am
Location: Johannesburg,South Africa

Re: [PHP5] MySQL class with Result Iterator

Post by thomas777neo »

Great code Chris.

You should consider using ADODB or equivalent in your class to make it really useful, so then you can change between MSSQL, Oracle etc in a matter of changing the connection string.

All the best
Post Reply