Can the query result resource be cached?

[peminator]

as we have a lots of code in our project, using the array-based caching is not very suitable for us as having to manually check too many lines of code.

can the MySQL query result be cached and restored again in form, where I can normally use mysql functions on it? (mysql_num_rows, mysql_fetch_....)

Technical background:
- mysql_query does not return result itself, only a resource id, I need to be able to create such resource from cached data and return its id so it can be used as normal mysql result in php.

As far as found, all caching solutions including memcached etc use to return the data from cache as array and the mysql functions in php do not longer work with such result ( like mysql_num_rows() etc, usually such cached result is already an array and therefore count() has to be used instead). We have lots of queries with VERY complex WHERE and JOIN etc statements having over 10 rows just to find there are currently for example 10 rows matching such criteria and have certain values...

QUESTION: is this actually technically possible? Can PHP recreate such resource from provided data itself?

[johanafm]

No, you can't serialize resources, but why not simply use ::fetchAll to retrieve all rows at once, and always operate on arrays, since you seem to be dealing with very small result sets.

However, if you really wish to transparently deal with arrays and result set resources, create a class that implements Iterator and instantiate it with either a result set or an array

class Result implements Iterator
{
    private $var = array();
    private $i = 0;

public function __construct($array_or_resultset)
{
    if (is_array($array_or_resultset) ||
    	(is_object($array_or_resultset) && get_class($array_or_resultset) == 'PDOStatement'))
    {
        $this->var = $array_or_resultset;
    }
}

public function rewind()
{
	if (is_array($this->var))
		reset($this->var);
	elseif (is_object($this->var) && get_class($this->var) == 'PDOStatement')
	{
		$this->i = 0;
	}
}

public function current()
{
	if (is_array($this->var))
		return current($this->var);
	elseif (is_object($this->var) && get_class($this->var) == 'PDOStatement')
	{
		return $this->var->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, $this->i);
	}
	return;
}

public function key() 
{
	if (is_array($this->var))
		return key($this->var);
	elseif (is_object($this->var) && get_class($this->var) == 'PDOStatement')
    	return $this->i;
}

public function next() 
{
	if (is_array($this->var))
	{
        return next($this->var);
    }
    elseif (is_object($this->var) && get_class($this->var) == 'PDOStatement')
    {
		++$this->i;
    }
}

public function valid()
{
	if (is_array($this->var))
	{
		$key = key($this->var);
		return ($key !== NULL && $key !== FALSE);
	}
	# Assuming your DBMS returns the number of retrieved rows for a SELECT statement.
	# Not all DBMS do!
	elseif (is_object($this->var) && get_class($this->var) == 'PDOStatement')
	{
		return ($this->i < $this->var->rowCount());
	}
}

}

$db = new PDO(...);

$stmt = $db->query('SELECT field FROM table WHERE id < 10');
$rr = new Result($stmt);

echo '<h3>Statement</h3>';
foreach ($rr as $r)
{
	echo '<div>'.$r['field'].'</div>';
}

$stmt = $db->query('SELECT field FROM table WHERE id < 10');
$rr = new Result($stmt);
$arr = $stmt->fetchAll();
$rarr = new Result($arr);

echo '<br/><h3>Array</h3>';
foreach ($rarr as $r)
{
	echo '<div>'.$r['field'].'</div>';
}

You can't get scrollable cursors in MySQL with PDO afaik. If you need this, use mysqli and data_seek instead.