PHP MySQL via PDO: mysql_result() substitute not working when called more than once

Question

I'm in the process of converting a quite large existing webapp from the old mysql_ library to the new PDO class. Since it is a quite large webapp and this necessary change popped up quite late in an ongoing project (limitation of mysql_ functions and stored procedures) I have decided for now to just create custom functions which replace the old mysql_ functions and do a search/replace over the whole webapp.

As a reference, my queries are executed as follows:

function MysqlQuery($SqlQuery)
{
   global $MysqlPDO;  //$MysqlPDO is initialized in an earlier called functions which creates the database connection object
   $SqlResult = $MysqlPDO->prepare($SqlQuery,array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));  //tried setting a scrollable cursor but this doesnt seem to work...
   $SqlResult->execute();
   return $SqlResult;
}

So far the only thing I can't get solved is to find a good alternative to the old mysql_result() function. Off course here and elsewhere on the net there are ample examples of so-called mysql_result() replacements, but so far I've found none that works when being called multiple times to fetch the same row.

I first started out with this custom function

function MysqlResult($SqlResult, $RowNumber, $FieldName = 0)
{
   $RowArray = $SqlResult->fetch(PDO::FETCH_BOTH,PDO::FETCH_ORI_ABS,$RowNumber);
   return $RowArray[$FieldName];
}

However when I call this function more than once, it will only work the first time, as the 2nd time $SqlResult->fetch() is called, it returns false, like so:

$SqlQuery = "SELECT 'TEST' AS Test";
$SqlResult = MysqlQuery($SqlQuery);
var_dump(MysqlResult($SqlResult,0,"Test"));
var_dump(MysqlResult($SqlResult,0,"Test"));
var_dump(MysqlResult($SqlResult,0,"Test"));
var_dump(MysqlResult($SqlResult,0,"Test"));
var_dump(MysqlResult($SqlResult,0,"Test"));

This will output the following:

string 'TEST' (length=4)
null
null
null
null

I then decided to use the PDOStatement::fetchAll method in my custom MysqlResult() function, like so:

function MysqlResult($SqlResult,$RowNumber,$FieldName = 0)
{
   $ResultArray = $SqlResult->fetchAll();
   $RowArray = $ResultArray[$RowNumber];
   return $RowArray[$FieldName];
}

But this produces exactly the same output as my first attempt.

The problem is that in my existing webapp, there are a few occurances where mysql_result is used to get the same SQL output twice or more times. While I perfectly realize this is not nice coding, I don't want to go in and rewrite those parts at this point. Am I to believe that the shiny new MySQL PDO class is more limited than the old mysql_ functions??

Relevant question [here](http://stackoverflow.com/questions/16024889/pdo-multiple-fetching-of-same-query) — sudee, Jul 25 '14 at 09:33
A significant question comes to my mind - why would one want to connect to a DB and execute the query more then once in a while to retrieve the exactly same information? Isn't there something missing in the application architecture...? Or maybe we are missing some argument(s) why this is necessary... — shadyyx, Jul 25 '14 at 10:05
@sudee: Thanks for the link, while the solution proposed there works, it requires rewriting of existing code. I'd rather build a 'real' mysql_result() replacement function. — Alex, Jul 25 '14 at 10:06
@shadyyx: Please see the last lines of my question: `The problem is that in my existing webapp, there are a few occurances where mysql_result is used to get the same SQL output twice or more times. While I perfectly realize this is not nice coding, I don't want to go in and rewrite those parts at this point.` — Alex, Jul 25 '14 at 10:07
*I don't want to go in and rewrite those parts at this point.* => Why should we help you to maintain the ugly code and errors in architecture? Right now you have the opportunity to make your code better so take it. — shadyyx, Jul 25 '14 at 10:07

sourcecode · Answer 1 · 2014-07-29T17:27:57.310

Insead of passing mysql pdo result in MysqlResult , You can send result array from fetchAll()

function MysqlQuery($SqlQuery)
{
   global $MysqlPDO;  //$MysqlPDO is initialized in an earlier called functions which creates the database connection object
   $SqlResult = $MysqlPDO->prepare($SqlQuery,array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));  //tried setting a scrollable cursor but this doesnt seem to work...
   $SqlResult->execute();
   // this will return all the rows from result set
   return $SqlResult->fetchAll();
}

function MysqlResult($SqlResult, $RowNumber, $FieldName = 0)
{
   return $SqlResult[$RowNumber][$FieldName];
}

This also doesn't work when being called twice MysqlResult() will return false the 2nd time — Alex, Jul 28 '14 at 15:31

score 0 · Answer 2 · answered Jul 28 '14 at 15:36

It seems the query has to be 'reset' by calling the 'closeCursor()' methor of the result object before it will return the same set again. This effectively executes the MySQL query again, so it's a nasty hack to be honest, but it works for the time being until I get around to remove wherever this method is being called. This is my working MysqlResult() function I made now:

function MysqlResult($SqlResult,$RowNumber,$FieldName = 0)
{
   //HACK: since mysql_result() cannot be remade using the PDO class, we have to re-execute the query. This is a quite ugly solution and should be removed in the future...
   $SqlResult->closeCursor();
   $SqlResult->execute();
   $ResultArray = $SqlResult->fetchAll();
   $RowArray = $ResultArray[$RowNumber];
   return $RowArray[$FieldName];
}

PHP MySQL via PDO: mysql_result() substitute not working when called more than once

2 Answers2