PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Fri Sep 21, 2018 6:39 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 23 posts ]  Go to page Previous  1, 2
Author Message
 Post subject:
PostPosted: Fri Jan 19, 2007 5:14 pm 
Offline
The Ninja Space Mod
User avatar

Joined: Fri Aug 05, 2005 1:53 pm
Posts: 6424
Location: Paradise, CA
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) :)


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 19, 2007 8:47 pm 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13576
Location: New York, NY, US
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:
Syntax: [ Download ] [ Hide ]
       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)


Top
 Profile  
 
 Post subject:
PostPosted: Sat Jan 20, 2007 11:27 am 
Offline
Forum Newbie

Joined: Fri Jan 19, 2007 8:11 am
Posts: 3
Thanks a lot, arborint. It now works much faster.
Quote:
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


Top
 Profile  
 
 Post subject:
PostPosted: Sat Jan 20, 2007 11:39 am 
Offline
Forum Newbie

Joined: Fri Jan 19, 2007 8:11 am
Posts: 3
I also modified query method a little, maybe someone will find this mod :D useful.

Syntax: [ Download ] [ Hide ]
 
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:
Syntax: [ Download ] [ Hide ]
 
$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));
 


Top
 Profile  
 
 Post subject:
PostPosted: Mon Apr 23, 2007 11:25 am 
Offline
Forum Newbie

Joined: Mon Apr 23, 2007 11:13 am
Posts: 1
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
Syntax: [ Download ] [ Hide ]
       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: )
Syntax: [ Download ] [ Hide ]
       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
Syntax: [ Download ] [ Hide ]
       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: )
Syntax: [ Download ] [ Hide ]
       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!


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jun 13, 2007 6:20 pm 
Offline
Forum Commoner

Joined: Thu Aug 15, 2002 6:13 am
Posts: 90
few little additions, which would be nicer
Syntax: [ Download ] [ Hide ]
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

Syntax: [ Download ] [ Hide ]
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

Syntax: [ Download ] [ Hide ]
$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.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 21, 2007 8:27 am 
Offline
Forum Newbie

Joined: Wed Nov 09, 2005 7:46 am
Posts: 5
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! :)

Syntax: [ Download ] [ Hide ]
 
<?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;
    }
}
?>
 


Top
 Profile  
 
PostPosted: Thu May 22, 2008 7:14 am 
Offline
Forum Contributor
User avatar

Joined: Mon Mar 10, 2003 7:12 am
Posts: 214
Location: Johannesburg,South Africa
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 23 posts ]  Go to page Previous  1, 2

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group