Looping with while and foreach in PHP

Question

I cannot seem to get foreach to work. Maybe I do not understand it correctly.

Here is my code:

$statement = "SELECT * FROM categories ORDER BY name ASC";
$query = mysql_query($statement)

...
...

$cals = array("sports","general","other","clubs");

foreach ($cals as $value)
{
/*  echo "<h3>".$value."</h3>"; 
*/  echo "<table width='100%'>";
while ($array = mysql_fetch_array($query))
    {
        if ($array['calendar'] == $value)
        {?>
<tr>
  <td><?php echo $array['name']; ?></td>
  <td><a onclick="update_form('<?php echo $array['name']; ?>', '<?php echo $array['calendar']; ?>')" href="#">Edit</a></td>
</tr>
<?php }
    }
    echo "</table><br />Value: $value";
}

The goal of this is to have the foreach change the if statement. I had planned for the if statement to say: if ($array['calendar'] == "sports") the first time, if ($array['calendar'] == "general") the second time, and so on. However, it shows all of the tables (in the source code), but no table rows are created after the first for each array value. For example, I correctly see the sports table, but I do not see any table rows for general, other, or clubs. There are records in that database that should appear in each of those. Could it be a problem with the while and if statements? If I manually set the $value in the if statement to one of the values in the array, it shows the correct records.

What am I missing?

Sample Data:

in the MySQL database -

categories table. fields:

• id
• name
• num_events
• calendar
• calendar_url

All of these fields except the calendar field has dummy data in it.

Currently, I have 5 records in there. Each one has a different calendar value. One is sports, one is clubs, and one is general. Depending on what value I place first in the array, it only shows that one table, of all of the values with whatever the first value in the array is.

Here is the source code from the resulting page:

<table width='100%'><tr>
  <td>test4</td>
  <td><a onclick="update_form('test4', 'sports')" href="#">Edit</a></td>
</tr>
<tr>
  <td>test5</td>

  <td><a onclick="update_form('test5', 'sports')" href="#">Edit</a></td>
</tr>
</table><br />Value: sports<table width='100%'></table><br />Value: general<table width='100%'></table><br />Value: other<table width='100%'></table><br />Value: clubs

Answer 1

Try this instead...

$result = mysql_query($query);
while ($array = mysql_fetch_array($result)) {
...
}

Answer 2

First, just a point of style. You might consider renaming your variable $query to something like $results. It's holding the result of a query, not a query itself.

The problem is that you're not resetting $results. After the first table, you've iterated all the way through the array. When you get to the end, and there are no more rows to iterate over, mysql_fetch_assoc() returns false.

So try this:

    foreach ($cals as $value)
    {
        while ($array = mysql_fetch_array($query))
        {
             if ($array['calendar'] == $value)
             {
?>
    <tr>
      <td><?php echo $array['name']; ?></td>
      <td><a onclick="update_form('<?php echo $array['name']; ?>', '<?php echo $array['calendar']; ?>')" href="#">Edit</a></td>
    </tr>
    <?php
             }
        }
        echo "</table><br />Value: $value";
        mysql_data_seek($query,0); // <=== Set the resultsets internal pointer back to zero (the first record).
    }

The important bit is the mysql_data_seek() on the second to last line.

You could also stick that mysql_data_seek() right before the while() line, if you prefer. You just need to make sure that for each iteration of the foreach loop, the array pointer is reset before you hit while().

Hope this is clear, it's late here and I'm tired.

EDIT: s/reset/mysql_data_seek/

Answer 3

mysql_fetch_array return array indexed by integer if you want asoc array change

while ($array = mysql_fetch_array($query))

to this

while ($array = mysql_fetch_assoc($query))

Answer 4

If I understand the question correctly, you've got numerous values in the "calendar" column of "categories" table; and you want to show only the categories that have calendar = sports, general, other or clubs... in that order. Right?

First thing to note is that you have a while loop inside the foreach loop that exhausts the data in first go. Lets try to dry run the code:

Inside the first iteration of the foreach loop, $value is "sports". Inside this iteration the code loops over all records in the table using the while loop, displaying record only if it is a "sports" record.

Upon next iteration(s) of the foreach loop, the line that says while ($array = mysql_fetch_array($query)) will continue to return false because all records were fetched in the first iteration of the foreach loop.

To fix this, move the following line inside the foreach loop:

$query = mysql_query($statement);

This will re-query the table for each item in the $cals array so that for each iteration inside the foreach loop you while-loop over all the records in the table.

Secondly, if it is OK that the records be sorted by the "calendar" column; it is possible to display and group them in one go (i.e. 1 loop and 1 query). Let me know if you're interested.

Answer 5

As jcubic and timdev pointed out, there are a couple problems with the code as written. However, the algorithm you're trying to use is very inefficient because it loops over the entire result set for every calendar type. Instead, you can use a multi-column sort in SQL to do it in one pass:

$query = "SELECT * FROM categories ORDER BY calendar, name";
$results = mysql_query($results)
...
...
$last_cal = '';
while ($array = mysql_fetch_assoc($query))
{
  if (!$last_cal) {
    echo '<table>';
  }
  else if ($array['calendar'] != $last_cal) {
    echo '</table>';
    echo '<table>';
  }
  ?>
   ....HTML for table row...
  <?php
  $last_cal = $array['calendar'];
}