PHP foreach到每个级别的可变元素的嵌套列表？

Question

I'm still learning PHP, and have started to understand the working of foreach() loop. I am stuck on something.

I'm working with PHP drawing from a MySQL database, and I want to list how many items share the same "topic_id". With the initial number, I'm trying to make a nested list that identifies what different medium types each item is available in, and how many items are counted in each medium.

This is the database query I'm using:

SELECT 
  m.name AS medium, i.medium_id, f.name AS format, 
  SUM(
    CASE WHEN it.topic_id = '$topicId' AND i.id = it.item_id 
      THEN 1 
      ELSE 0 END
  ) AS sumFormat
FROM items AS i
LEFT JOIN item_topics AS it 
  ON i.id = it.item_id 
LEFT JOIN formats AS f 
  ON f.id = i.format_id 
LEFT JOIN media AS m 
  ON m.id = i.medium_id 
GROUP BY medium, format 
ORDER BY medium ASC

This gives the following result (I've omitted sumFormat=0 results):

+--------------+-------------+--------------+-----------+
| medium       | medium_id   | format       | sumFormat |
+--------------+-------------+--------------+-----------+
| Games        |           1 | NULL         |         1 |
| Magazines    |           2 | Paperback    |        35 |
| Albums       |           3 | CD           |        25 |
| Albums       |           3 | Record       |         1 |
| Books        |           5 | Audiobook    |        38 |
| Books        |           5 | Diary        |         1 |
| Books        |           5 | Dictionary   |         4 |
| Books        |           5 | Ebook        |       421 |
| Books        |           5 | Hardback     |        76 |
| Books        |           5 | Paperback    |       574 |
| Comics       |           6 | Paperback    |         2 |
+--------------+-------------+--------------+-----------+

Depending on the "$topicId" being queried, the results will be different - in some cases, there might not be any items in a given medium or format. I'd like the PHP code to handle this, so only the medium types and formats that are present for the "topic_id" will be listed.

In my PHP code, I've put it together like so:

<ul id="formats">
<?php foreach ($topicFormats as $topicFormat): ?>
    <?php if ($topicFormat['medium'] && $topicFormat['sumFormat']): ?>
        <li><?= $topicFormat['medium'] ?></li>
            <?php if ($topicFormat['sumFormat']): ?>
                <ul>
                <li><?= $topicFormat['sumFormat'] ?>
                    <?php if (!$topicFormat['format']): ?>
                        Games
                    <?php else: ?><?= $topicFormat['format'] ?>
                        <?php endif; ?>
                </li>
                </ul>
            <?php endif; ?>
    <?php endif; ?>
<?php endforeach; ?>

The final HTML looks like this:

    1178 Items
    • Games
        • 1 Games
    • Magazines
        • 35 Paperback
    • Albums
        • 1 Record
    • Albums
        • 25 CD
    • Books
        • 38 Audiobook
    • Books
        • 1 Diary
    • Books
        • 4 Dictionary
    • Books
        • 421 Ebook
    • Books
        • 76 Hardback
    • Books
        • 574 Paperback
    • Comics
        • 2 Paperback

However I want the result below:

    1178 Items
    • Games
        • 1 Games
    • Magazines
        • 35 Paperback
    • Albums
        • 1 Record
        • 25 CD
    • Books
        • 38 Audiobook
        • 1 Diary
        • 4 Dictionary
        • 421 Ebook
        • 76 Hardback
        • 574 Paperback
    • Comics
        • 2 Paperback

I have checked this issue on StackOverFlow but did not find any solution. Any help would be appreciated!

Edit: I haven't had a chance to try out any of your suggestions yet, but in answer to Kapilgopinath, here is the resultant array (I think this is what you're asking for - I've never retrieved a resultant array before!):

Array 
(
[0] => Games
[medium] => Games
[1] => 1 
[medium_id] => 1 
[2] => 
[format] => 
[3] => 1 
[sumFormat] => 1 
) 

("Games" doesn't have a format, so it returns null - that would be where other medium types would list "Paperback", "CD", etc.)

Answer 1

The issue with using a 'foreach' loop is that the next read is not done until the end of the loop, which is too late, when you have a 'nested loop' as here. It can be easier, although not less code, to use a 'read ahead' technique. The advantage is that you do not need an if test to determine what to do with the current entry. Therefore you need an iterator then it is just nested loops. With the read of the next record, immediately after the current one has been processed.

<?php
    $values_from_db = array( array( 'medium' => 'Games', 'format' => 'Games', 'sumFor' => 1, ), array( 'medium' => 'Magazines', 'format' => 'Paperback', 'sumFor' => 35, ), array( 'medium' => 'Albums', 'format' => 'CD', 'sumFor' => 25, ), array( 'medium' => 'Albums', 'format' => 'Record', 'sumFor' => 1, ), array( 'medium' => 'Books', 'format' => 'Audiobook', 'sumFor' => 38, ), array( 'medium' => 'Books', 'format' => 'Diary', 'sumFor' => 1, ), array( 'medium' => 'Books', 'format' => 'Dictionary', 'sumFor' => 4, ), array( 'medium' => 'Books', 'format' => 'Ebook', 'sumFor' => 421, ), array( 'medium' => 'Books', 'format' => 'Hardback', 'sumFor' => 76, ), array( 'medium' => 'Books', 'format' => 'Paperback', 'sumFor' => 574, ), array( 'medium' => 'Comics', 'format' => 'Paperback', 'sumFor' => 2, ), );

    $iterSumFor = new ArrayIterator($values_from_db);
    $curEntry = $iterSumFor->current(); // read ahead -- always a current record to process
?>
<ul>
<?php while ($iterSumFor->valid()): ?>
    <?php $curMedium = $curEntry['medium']; ?>
    <li><?= $curMedium ?></li>
    <ul>
        <?php while ($iterSumFor->valid() && $curEntry['medium'] == $curMedium): ?>
            <li><?= $curEntry['sumFor'], '&nbsp;', $curEntry['format'] ?></li>
            <?php $iterSumFor->next(); ?>
            <?php $curEntry = $iterSumFor->current(); ?>
        <?php endwhile; ?>
    </ul>
<?php endwhile ?>
</ul>