How to Loop Through a MySQL Result Set More Than Once Using the MySQL_* Functions

How can I loop through a MySQL result set more than once using the mysql_* functions?

This is how you can do it:

$result = mysql_query(/* Your query */);
while($row = mysql_fetch_assoc($result)){
 // do whatever here...
}

// set the pointer back to the beginning
mysql_data_seek($result, 0);
while($row = mysql_fetch_assoc($result)){
 // do whatever here...
}

However, I would have to say, this doesn't seem the right way to handle this. Why not do the processing within the first loop?

PHP: cannot loop through mysql rows more than once

This is the error

while ($row = mysql_fetch_array($result)) {
   $total_images++;
}

once you fetched the array the array pointer set to the end.

use this

 $total_images=mysql_num_rows($result);

and

$images_to_offset=mysql_num_rows($result);

OR

To reset the position of pointer Use mysql_data_seek(). it moves internal result pointer

A way around having to loop through results to access more information using the first result set

Simple aggregation and a left join or two on your main query should do it. A cross join could be used to identify the unique test types and by cross joining this to a customer/address 1:1 relationship we generate a row for each test type for every customer. That way each customer will have one of each type listed with the appropriate count (0) when no tests of that type exist for an account.

SELECT C.accountId, A.companyName, Z.TestType, Count(distinct T.TestID)
FROM Customers C
LEFT JOIN Address A
  ON C.addressId=A.addressId
CROSS JOIN (Select Distinct TestType from Tests) Z
LEFT JOIN Tests T
  on T.AccountID = C.AccountID
 and Z.TestType = T.TestType
 and ReceiveDT>=SomeDate
 and ReceiveDT<=SomeOtherDate
GROUP BY C.accountId, A.companyName, Z.TestType

LEFT Joinn will return all customer records and only those with address that match and only records from test matching a customer record. So count will be 0 when no type/test match.

Customer:Address:TestTypes:Tests cardinality expected: 1:1:M:M

so if there were only 3 types of tests I would expect to see 3* the number of records in customers in the results. This is because Address and customers appear to be a 1:1 the number of test types will determine how many times we replicate a customer record and the left join to tests is being aggregated so it will not add to the row count.

Obviously you can add a where clause if you need to limit to a specific customer, but I thought you wanted all customers's test type counts for all possible test types.

Reuse a mysql query resultset on same PHP page

Honestly, you've already done 99% of the work.

You can replace this:

while ($row = mysqli_fetch_assoc($resultAuthor)) {
  if ($row['book_author_first_name_only'] == 0 ) {
    echo "<a class='fw-bold author-link' href='#" . $row['book_id'] . "'>" . $row['book_author_first_name'] . ' ' . $row['book_author_last_name'] . "</a>";
  } else {
    echo "<a class='fw-bold author-link' href='#" . $row['book_id'] . "'>" . $row['book_author_first_name'] . "</a>";
  }
}

With:

$rows = mysqli_fetch_all($resultAuthor, MYSQLI_ASSOC);

To fetch all the rows at once and store them in an array ($rows).

you'll notice this looks very similar to the 1-record-at-a-time version you were using in your while (...) { } loop.

Now you can simply iterate over this data as often as you like:

foreach ($rows as $row) {
    if ($row['book_author_first_name_only'] == 0 ) {
        echo "<a class='fw-bold author-link' href='#" . $row['book_id'] . "'>" . $row['book_author_first_name'] . ' ' . $row['book_author_last_name'] . "</a>";
    } else {
        echo "<a class='fw-bold author-link' href='#" . $row['book_id'] . "'>" . $row['book_author_first_name'] . "</a>";
    }
}

// and again
foreach ($rows as $row) {
    // ... do something else with each $row
}

// and again
foreach ($rows as $row) {
    // ... do something else with each $row
}

how to iterate through mysql_query() result

while($row = mysql_fetch_assoc($result)) {
   // Your code goes here...
   // OR
   echo "<pre>"; print_r($row); echo "</pre>";
}

Use PHP Loop to Query MYSQL Database Multiple Times

It was actually much easier than I thought; maybe I over-explained the issue. Here is what I did.

1) Once I returned my array $levels which looked like this:

$levels = Array( [0] => 3 [1] => 1 [2] => 2 ) 

2) I created an empty array called $result_set
2) Then looped through the $levels array with a foreach loop and ran the query with the first $level in the $levels array. However, rather than just returning the $result_set with the first row returned, I inserted the first row into the empty array I created in step 2. After each loop through, I inserted the new row from MYSQL into the now filled $result_set array.

Here is what it looked like in the end:

$result_set = array();
foreach ($levels as $level) {
    $query = "SELECT *, CardHP as EncounterCardHP, CardAP as EncounterCardAP";
    $query .= " FROM Cards";
    $query .= " WHERE CardTypeId = 1 AND CardRarity = {$level}";
    $query .= " ORDER BY RAND() LIMIT 1";
    $result = mysqli_query($query, $connection);
    $result_set[] = mysqli_fetch_array($result);    
    confirm_query($result_set);
}
return $result_set;     

My $result_set now has 3 monsters in it, a level 3, level 1 and level 2, all chosen randomly from the database for each respective level.

Thanks for all the help.

---

Related Topics