Does mysql_fetch_array reset the query var?

Question

For example I have a mysql query that gets some data. Then runs another query based some of the data that it got.

If i just return the first query, in my case $qOne. Everything works great.

BUT, after using my while loop while ($row = mysql_fetch_array($qOne)) It then returns as an empty array. (but the second query I return DOES work)

I tried to see if I could "save" the first query in another var im not messing with like this $savedResult = $qOne, then i'd just return the $savedResult but that did not work.

Does anyone know how I can get my function below to return both of the results? Thanks!

function getFoods($sort, $start, $limit) {

        $qOne = mysql_query("SELECT a.id, a.name, a.type, AVG(b.r) AS fra, COUNT(b.id) as tvotes FROM `foods` a LEFT JOIN `foods_ratings` b ON a.id = b.id GROUP BY a.id ORDER BY fra DESC, tvotes DESC LIMIT $start, $limit;");

        $i = 0;
        $qry = "";
        while ($row = mysql_fetch_array($qOne)) {
            $fid = $row['id'];
            if ($i > 0)
                $qry .= " UNION ";
            $i++;
            $qry .= "SELECT fid, ing, amount FROM foods_ing WHERE fid='$fid'";
        }

        $qTwo = mysql_query($qry);

        return array($qOne, $qTwo);
    }

why was this downvoted? I've literally never heard of mysql_data_seek() before or had any concept of resetting the mysql_query() before. But now I get it, and hopefully someone else will learn from this. — brybam, Mar 23 '12 at 03:08
if you need mysql_data_seek(), you are doing something wrong. — Your Common Sense, Mar 23 '12 at 03:10
I'm pretty new to mysql/php. Just trying to learn. You're comment didn't teach me anything. — brybam, Mar 23 '12 at 03:23

score 2 · Accepted Answer · answered Mar 23 '12 at 02:57

2

When you have returned the two query result resources, remember that you will need to fetch from them when you actually want to use them. (we don't see the code where you implement that).

To make use of $qOne after you already have looped through it, you must rewind it back to the first position. That is done with mysql_data_seek()

mysql_data_seek($qOne, 0);

answered Mar 23 '12 at 02:57

Michael Berkowski

267,341
46
444
390

sorry, typo on stackoverflow. It wasn't like that in my code. Really sorry about that. – brybam Mar 23 '12 at 02:59
@brybam Well, there's still the seek issue :) – Michael Berkowski Mar 23 '12 at 03:01
Ah, cool that did it. Kinda strange it needs to be like re-winded. It says I can mark your answer correct in a min. Thanks! – brybam Mar 23 '12 at 03:06
1

@brybam The reason it needs to be rewound is that the resource doens't contain any results. Instead it functions like a conduit into the RDBMS, which holds your result set and fetches rows one at a time. So you just need to tell its pointer to go back to the first row in the result set. – Michael Berkowski Mar 23 '12 at 03:08
Would you happen to know of any articles or something talking about the convention for making a query, then saving it to an array to return? Like I said, I didn't do it because i'm lazy or anything. I literally just had no idea that people don't typically return the query like I did. – brybam Mar 23 '12 at 03:26
@brybam Actually, take a look at the [PHP tag wiki](http://stackoverflow.com/tags/php/info) here on SO. Near the bottom is the canonical method for retrieving rows into an array. In your case, you would then `return $rowset;` – Michael Berkowski Mar 23 '12 at 13:02

Does mysql_fetch_array reset the query var?

1 Answers1