[RESOLVED] Correct way to use a result twice

phpSimon

Hi all

I want to do this:

$query = "SELECT item1, item2 FROM table";
$result = mysql_query($query);

while(list($item1, $item2) = mysql_fetch_array($result){

// do something with the items

}

while (list($item1, $item2) = mysql_fetch_array($result){

// do something else with the items

}

This doesn't work; I'm guessing $result is cleared by the action of mysql_fetch_array.

I can't do $result2 = $result and run the while(list) on that because that doesn't copy array results.

I can only seem to run the query twice! Is there some other solution?

Thanks

johanafm

[man]mysql_data_seek[/man]

leatherback

I would just do all that you have to do in the first 'while' loop, and store whatever you need later in a variable, which you can then send to the user where you need it..

Derokorian

I think johanafm has the right idea, you can seek back to the first record before looping again this is how I would do it, instead of creating more variables to hog more resources =D

mysql_data_seek($result,0);

bradgrafelman

Derokorian wrote:
instead of creating more variables to hog more resources

You're still hogging more resources by forcing MySQL to re-send all of the data it already sent you before.

Six of one, half a dozen of the other. However, memory should be a lot faster than re-fetching all of the results from the SQL server all over again, so I would much rather store the results in an array and simply loop over that array as many times as I need to (which I would try to get down to just one time).

phpSimon

Thanks everyone for your input and apologies for being so slow to get back into the forum and respond.

I'm surprised there isn't some sort of obvious way of using $result without it being emptied; in some cases I'm dealing with a remote database so it's really important to reduce the number of queries sent to MySQL (I also query a separate MS SQL database, again remotely).

What I usually do is what bradgrafelman suggests; store the results in an array (using a function to run the query) and loop over it but in those cases I've usually got a complicated query with a collection of joins, and I want to manipulate the return and generate some additional results and package the whole lot up in a useful array.

In this case however, the return didn't need any changes - I simply wanted to run through the results more than once. Given the close relationship between PHP and SQL, I do think it's surprising that this isn't possible in an obvious way.

Would it be possible to write a function that takes an SQL $result and stores it in an array that can be read more than once, ideally in the same way (using mysql_fetch_array) no matter what the shape and size of the result? Can someone advise which part of the result parsing process 'deletes' the content? Is a variable pointer thing, and if so why can't we simply reset the pointer? Also - why can't we write:

$result2 = $result;

... and use $result2 the same way?

I'm still a relative beginner in PHP. I'm always amazed by the sheer flexibility of the language; there really isn't anything you can't do; this seems like such a simple thing! It looks like a really good case for a new PHP function.

Thanks again

leatherback

Of course you can write a function that does that;e.g.

funtion StoreResults($resultspointer)
  {
  $rows = array();
  while($row = mysql_fetch_array($resultspointer))
    {  

    $rows[] = $row;
    }
  return($rows);
  }

funtion GetResults($rownr, $dataarray)
  {
  if(is_array($rownr))
    {
    $out = array();
    foreach($rownr as $thisrow)
      {
      $out[] = GetResults($thisrow, $dataarray);
      }
    }
  else
    {
    $out = $dataarray[$rownr);
    }
  return($out);
  }

phpSimon

That's useful, thank you. I'll give it a try.

Why is this not possible, though?

$result2 = $result;

bradgrafelman

If $result is a resource, then $result2 will point to that same resource. In other words, if you walk through the entire result set with $result, then you've simultaneously done the same with $result2 since they point to the same resource.

phpSimon

Thank you. I need to read up on PHP resources, obviously!

With reference to your earlier comment about forcing MySQL to re-send data / storing it locally - in some cases I'm connected to a remote (and slow) MySQL DBMS. I thought it would be better to query MySQL, store the data in memory and re-use it rather than keep asking MySQL for the data again.

I'm not really clear why

mysql_data_seek($result,0);

... is forcing mysql to resend data. I sort of presumed that $result would become a resource in terms of a variable stored in memory - and resetting the pointer simply told PHP to read the variable from the start again.

phpSimon

Ah. It wasn't that hard.

http://php.net/manual/en/language.types.resource.php

"...a special variable, holding a reference to an external resource. Resources are created and used by special functions."

So $result becomes a sort of connection back to the data source?

Clever.

Derokorian

That's exactly what I thought in fact that's what it says in the docs:

mysql_data_seek() moves the internal row pointer of the MySQL result associated with the specified result identifier to point to the specified row number.

I'd be interested in knowing why you say this re-sends the data from mysql BG.

bradgrafelman

phpSimon;10997850 wrote:
I'm not really clear why
mysql_data_seek($result,0);
... is forcing mysql to resend data. I sort of presumed that $result would become a resource in terms of a variable stored in memory - and resetting the pointer simply told PHP to read the variable from the start again.

The manual page [man]language.types.resource[/man] gives a (very) brief explanation of what a resource variable is.

For the specific code you've referenced, though, note that there is no data in PHP's memory - the "result set" is stored on the MySQL server, and PHP simply uses $result as a pointer to allow PHP to interact with the MySQL server. So in order to go back through the result set, PHP is simply sending commands to the SQL server to rewind the SQL server's internal pointer back to the beginning of the result set. Subsequent mysql_fetch_*() functions will again tell the SQL server to send rows from the result all over again.

bradgrafelman

Derokorian;10997852 wrote:
That's exactly what I thought in fact that's what it says in the docs:I'd be interested in knowing why you say this re-sends the data from mysql BG.

Because both the "pointer" and the result set itself are internal to the MySQL server - not PHP.

EDIT: Woops, looks like I'm mixing up too much of MySQLi behavior with the mysql library. The above may not be true for mysql_query().

phpSimon

I lose track of how much I learn in these forums!

Thank you!

Derokorian

bradgrafelman;10997855 wrote:
Because both the "pointer" and the result set itself are internal to the MySQL server - not PHP.

Ah, I guess I misunderstood how it worked then. I thought the result set was sent from mysql to php, and php could then use it without interacting with mysql again. Guess I failed.

bradgrafelman

Derokorian;10997857 wrote:
Ah, I guess I misunderstood how it worked then. I thought the result set was sent from mysql to php, and php could then use it without interacting with mysql again. Guess I failed.

Nope - you were right, I keep forgetting that mysql_query() calls mysql_store_result() rather than mysql_use_result(). Alas, I was wrong; the PHP source code tells no lies:

	PHP_FUNCTION(mysql_query)
	{
	    php_mysql_do_query(INTERNAL_FUNCTION_PARAM_PASSTHRU, MYSQL_STORE_RESULT);
	}

and relevant section of the php_mysql_do_query() C function:

    if(use_store == MYSQL_USE_RESULT) {
        mysql_result=mysql_use_result(mysql->conn);
    } else {
        mysql_result=mysql_store_result(mysql->conn);
    }

Then again, the real question is: Why are we even talking about the mysql extension in the first place? It's old, outdated, and deprecated by the PHP developers. 😉

phpSimon

Ah.

This creates more questions than it answers (well, for me anyway!)

Does this mean

mysql_data_seek($result,0);

... will NOT talk to MySQL again, or it will?

Thanks !

bradgrafelman

phpSimon;10997859 wrote:
Does this mean
mysql_data_seek($result,0);
... will NOT talk to MySQL again, or it will?

Well I haven't looked at the MySQL client library source code (the PHP function mysql_data_seek() is a small wrapper for the function from the MySQL client library with the same name), I'm guessing that it does not. It might check to see if the server has "gone away" or something like that, but no, the results of mysql_query() are automatically fetched before the function returns (thus no further communication with the SQL server is required in order to use those results).

It might make things easier to understand if you look at the opposite behavior; read the manual page for [man]mysql_unbuffered_query/man.

phpSimon

Thank you!

Reading about unbuffered query does indeed help.