Using reset() and mysql_data_seek()

[Clive Sweeting]

I was trying to make a page were I have printing out an array of results
from a MYSQL query, then for interest, printed out the same results
underneath again. Not sure why I might want to do this, but I want to test
how to do it.

I understand that the array pointer had advanced to the end of the array
after the first loop, and hence I have to spin it back to zero to be able to
loop again.

I have:

$result = mysql_query("select * from products");

$results_array = mysql_fetch_array($result);

I thought that

Reset($results_array);

Would loop it back to zero. It fails. As does

Reset($result);

With

Warning: reset(): Passed variable is not an array or object

However

mysql_data_seek($result, 0); // works perfectly


So I'm completely confused. I don't see why using reset() fails. Surely
$results_array is an array or an object? because otherwise, how on earth
have I managed to loop through it to get the results out in the first set of
results? And hence, why the error?

If anyone can clarify what I'm missing I would appreciate it.

P.S. My news program has a habit of capitalising the fist letter of lines,
so ignore the Reset(), I have reset()

[Clive Sweeting]

On 20/12/03 11:40, in article BC09E5C4.56E30%eatspam@sweet-apple.co.uk,
"Clive Sweeting" <eatspam@sweet-apple.co.uk> wrote:

As a addendum to that query, I've also noticed that after successfully
rewinding the array and printing out the array again, the very first row of
the 2nd lot of result is empty.

I'm printing as a table, so a get a row of
<td></<td><td></<td><td></<td><td></<td>

If I remove the first "printed row then the empty row disappears.

Which is most odd. Anyone explain.

And yes, I have changed my newsgroup posting details from Clive Sweeting,
due to the spam. Should have done that sooner I guess....

[Andy Hassall]

On Sat, 20 Dec 2003 11:40:52 +0000, Clive Sweeting <eatspam@sweet-apple.co.uk>
wrote:

>I was trying to make a page were I have printing out an array of results
>from a MYSQL query, then for interest, printed out the same results
>underneath again. Not sure why I might want to do this, but I want to test
>how to do it.
>
>I understand that the array pointer had advanced to the end of the array
>after the first loop, and hence I have to spin it back to zero to be able to
>loop again.
>
>I have:
>
>$result = mysql_query("select * from products");
>
>$results_array = mysql_fetch_array($result);
>
>I thought that
>
>Reset($results_array);
>
>Would loop it back to zero. It fails. As does

Back to the first column in the row; except if you're already gone past the
end, you probably have 'false' which isn't an array anyway.

>Reset($result);
>
>With
>
>Warning: reset(): Passed variable is not an array or object

No - $result is a resource handle, you can only access resource handles
through other PHP functions that can access the internals of it. i.e. MySQL
resource handles can only be accessed with mysql_* functions.

>However
>
>mysql_data_seek($result, 0); // works perfectly

Yes.

>So I'm completely confused. I don't see why using reset() fails. Surely
>$results_array is an array or an object?

$results_array can be either:

(a) An array containing a single row, one column per entry in the row.
(b) Boolean false, if there were no more rows.

You can't do anything with that to get back to a previous ROW.

>because otherwise, how on earth
>have I managed to loop through it to get the results out in the first set of
>results? And hence, why the error?

Because mysql_fetch_array() returns a new array containing the next row each
time you call it, or 'false' when there aren't any more to return.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) ([url]http://www.andyh.co.uk[/url])
Space: disk usage analysis tool ([url]http://www.andyhsoftware.co.uk/space[/url])

[Bonge Boo!]

On 20/12/03 13:06, in article [email]e5i8uv46ti7v9qhmdeoeh83om8osmf47pl@4ax.com[/email],
"Andy Hassall" <andy@andyh.co.uk> wrote:

>> because otherwise, how on earth
>> have I managed to loop through it to get the results out in the first set of
>> results? And hence, why the error?

>
> Because mysql_fetch_array() returns a new array containing the next row each
> time you call it, or 'false' when there aren't any more to return.

Ummm. Not sure I understand what you mean. I'm reading that as I could
'reinitialise" the array to the "pre-manipulated" state of $results_array by
simply placing

$results_array = mysql_fetch_array($result);

Further down the code that the first repeated block of results.

If I do that, I get precisely nothing returned, as I would expect if the
array pointer is at the end. So I'm getting the wrong end of your meaning.

I think what you're are telling me is that I can't use reset() with any of
the msysql realted function, because it just can't do it, and the tool for
the job is mysql_data_seek() because it can do it?

But to clear my mind.

If I made my own bog-standard array, I could rewind it using reset(), even
if I had gone to the end of the array, and hence would have a value of null?

If not, is there a function that can do that?

[Andy Hassall]

On Sat, 20 Dec 2003 13:17:13 +0000, Bonge Boo! <sweetapple@localhost.com>
wrote:

>On 20/12/03 13:06, in article [email]e5i8uv46ti7v9qhmdeoeh83om8osmf47pl@4ax.com[/email],
>"Andy Hassall" <andy@andyh.co.uk> wrote:
>

>>> because otherwise, how on earth
>>> have I managed to loop through it to get the results out in the first set of
>>> results? And hence, why the error?

>>
>> Because mysql_fetch_array() returns a new array containing the next row each
>> time you call it, or 'false' when there aren't any more to return.

>
>Ummm. Not sure I understand what you mean. I'm reading that as I could
>'reinitialise" the array to the "pre-manipulated" state of $results_array by
>simply placing
>
>$results_array = mysql_fetch_array($result);

No, that won't reinitialise it, it'll fetch the NEXT row. mysql_fetch_array
doesn't fetch an array of rows, it fetches a row as an array :-)

>Further down the code that the first repeated block of results.
>
>If I do that, I get precisely nothing returned, as I would expect if the
>array pointer is at the end. So I'm getting the wrong end of your meaning.
>
>I think what you're are telling me is that I can't use reset() with any of
>the msysql realted function, because it just can't do it, and the tool for
>the job is mysql_data_seek() because it can do it?

Pretty much, but I think maybe you've also got the array for a single row
confused with the whole result set.

>But to clear my mind.
>
>If I made my own bog-standard array, I could rewind it using reset(), even
>if I had gone to the end of the array, and hence would have a value of null?

Yes - if you'd made an array of arrays of rows, i.e. saved the entire result
set into a PHP array.

Maybe it'll help to go through each step of the process:

Given a table:

mysql> select * from products order by id;
+----+--------------+
| id | product_name |
+----+--------------+
| 1 | Product 1 |
| 2 | Product 2 |
| 3 | Product 3 |
+----+--------------+
3 rows in set (0.00 sec)


$result = mysql_query('select * from products order by id')

... returns a result set resource identifier - this is a pointer to the result
set somewhere in the MySQL client code, you can't directly access this, so you
have to go through the other mysql functions.

$row = mysql_fetch_array($result)

... copies a single row out of the internal storage for the result set and
into a PHP array, and advances the internal pointer in the result set. If
you're already past the last row, it returns 'false' instead of an array.

You can iterate through this $row array if you want, but remember it only
contains ONE row, so all you'd do is go up and down the fields in that one row
- it's completely disconnected from the rest of the result set.

Since the internal pointer for the 'current row' is hidden inside that $result
resource identifier, you can't use array functions on it; it's only
mysql_data_seek that can modify it.

If you wanted all the rows in one array (for which $result_array would be a
good name - so maybe this is what you wanted in the first place) then have a
look at the last bit in the example code I'm posting.

I think this code demonstrates most of the points above:

<pre>
<?php
$conn = mysql_connect();
mysql_select_db('test');

echo "Run query, and get a result set identifier back\n";
echo "(this is NOT an array):\n";
$result = mysql_query('select * from products order by id');
var_dump($result);

echo "Fetch a row as an array:\n";
$row = mysql_fetch_array($result);
var_dump($row);

echo "Fetch again; it fetches the NEXT row:\n";
$row = mysql_fetch_array($result);
var_dump($row);

echo "And again:\n";
$row = mysql_fetch_array($result);
var_dump($row);

echo "Try fetching again - already got all the rows, so you get:\n";
$row = mysql_fetch_array($result);
var_dump($row);

echo "Use mysql_data_seek to get back to the beginning:\n";
mysql_data_seek($result, 0);

echo "Try fetching again - back to the first row:\n";
$row = mysql_fetch_array($result);
var_dump($row);


echo "Or if you want everything in one big array:\n";
echo "go back to the start,\n";
echo "and fetch each row into the next index of your own array:\n";
mysql_data_seek($result, 0);
$result_array = array();
while ($row = mysql_fetch_array($result))
$result_array[] = $row;

var_dump($result_array);

?>
</pre>

It outputs:

Run query, and get a result set identifier back
(this is NOT an array):
resource(3) of type (mysql result)
Fetch a row as an array:
array(4) {
[0]=>
string(1) "1"
["id"]=>
string(1) "1"
[1]=>
string(9) "Product 1"
["product_name"]=>
string(9) "Product 1"
}
Fetch again; it fetches the NEXT row:
array(4) {
[0]=>
string(1) "2"
["id"]=>
string(1) "2"
[1]=>
string(9) "Product 2"
["product_name"]=>
string(9) "Product 2"
}
And again:
array(4) {
[0]=>
string(1) "3"
["id"]=>
string(1) "3"
[1]=>
string(9) "Product 3"
["product_name"]=>
string(9) "Product 3"
}
Try fetching again - already got all the rows, so you get:
bool(false)
Use mysql_data_seek to get back to the beginning:
Try fetching again - back to the first row:
array(4) {
[0]=>
string(1) "1"
["id"]=>
string(1) "1"
[1]=>
string(9) "Product 1"
["product_name"]=>
string(9) "Product 1"
}
Or if you want everything in one big array:
go back to the start,
and fetch each row into the next index of your own array:
array(3) {
[0]=>
array(4) {
[0]=>
string(1) "1"
["id"]=>
string(1) "1"
[1]=>
string(9) "Product 1"
["product_name"]=>
string(9) "Product 1"
}
[1]=>
array(4) {
[0]=>
string(1) "2"
["id"]=>
string(1) "2"
[1]=>
string(9) "Product 2"
["product_name"]=>
string(9) "Product 2"
}
[2]=>
array(4) {
[0]=>
string(1) "3"
["id"]=>
string(1) "3"
[1]=>
string(9) "Product 3"
["product_name"]=>
string(9) "Product 3"
}
}

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) ([url]http://www.andyh.co.uk[/url])
Space: disk usage analysis tool ([url]http://www.andyhsoftware.co.uk/space[/url])

[Bonge Boo!]

On 20/12/03 14:32, in article [email]d8n8uv4b994m6kp9psqocs9c5pb5b79opv@4ax.com[/email],
"Andy Hassall" <andy@andyh.co.uk> wrote:

>> Ummm. Not sure I understand what you mean. I'm reading that as I could
>> 'reinitialise" the array to the "pre-manipulated" state of $results_array by
>> simply placing
>>
>> $results_array = mysql_fetch_array($result);

>
> No, that won't reinitialise it, it'll fetch the NEXT row. mysql_fetch_array
> doesn't fetch an array of rows, it fetches a row as an array :-)

Didn't realise that was what mysql_fetch_array() was doing. I was assuming
it was grabbing everything that was in the array as a result of the query,
and then I was crunching through this monster array.

You're telling me that it just transferring the first row of that result
array to my variable $results_array.

Me not having the foggiest how it was working. What happens when you jump
into these things. Little bit of knowledge, dangerous...

>> Further down the code that the first repeated block of results.
>>
>> If I do that, I get precisely nothing returned, as I would expect if the
>> array pointer is at the end. So I'm getting the wrong end of your meaning.
>>
>> I think what you're are telling me is that I can't use reset() with any of
>> the msysql realted function, because it just can't do it, and the tool for
>> the job is mysql_data_seek() because it can do it?

>
> Pretty much, but I think maybe you've also got the array for a single row
> confused with the whole result set.

I have.

>> But to clear my mind.
>>
>> If I made my own bog-standard array, I could rewind it using reset(), even
>> if I had gone to the end of the array, and hence would have a value of null?

>
> Yes - if you'd made an array of arrays of rows, i.e. saved the entire result
> set into a PHP array.
>
> Maybe it'll help to go through each step of the process:

<snip>

Big gulp. Glass of wine, watch the rugby. And work out what you are saying.

Thank you VERY much for taking the time. It's amazing how simple it all
seems when you use Golive or Dreamweaver and it just cooks up the code. When
you try to understand it, and you have no programming experience....