[PHP5] MySQL class with Result Iterator
Moderator: General Moderators
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.
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) 
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
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: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)
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)
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
I also modified query method a little, maybe someone will find this mod
useful.
The usage is something like this:
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);
}
}
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));
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
(the original: )
-> and in DB_Result I changed the constructor
(the original: )
I'm rather new to PHP so I was wondering if anybody could tell me whether my changes actually make sense or not? Thx!
-> 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);
}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);
}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();
}
}
}
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);
}
}few little additions, which would be nicer
note no valid checking in next and rewind, as foreach will check with valid()
btw. This is untested..and only an example
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
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.
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--;
}
}btw. This is untested..and only an example
Code: Select all
foreach($result as $row) {
print_r($row);
}e.g. your code could be cleaner with
Code: Select all
$q = $db->query(..);
$q->setHandler('news');
foreach($q as $itm)
print $itm;I've already got a bunch of classes like that built, but they are at home and I am at work.
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!
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 © 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;
}
}
?>
- thomas777neo
- Forum Contributor
- Posts: 214
- Joined: Mon Mar 10, 2003 6:12 am
- Location: Johannesburg,South Africa
Re: [PHP5] MySQL class with Result Iterator
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
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