title: PHP foreach to nest list with variable elements at each level?
body: <p>I'm still learning PHP, and have started to understand the working of foreach() loop. I am stuck on something.</p>
  
  <p>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. </p>
  
  <p>This is the database query I'm using:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This gives the following result (I've omitted sumFormat=0 results):</p>
  
  <pre><code>+--------------+-------------+--------------+-----------+
  | 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 |
  +--------------+-------------+--------------+-----------+
  </code></pre>
  
  <p>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.</p>
  
  <p>In my PHP code, I've put it together like so:</p>
  
  <pre><code>&lt;ul id="formats"&gt;
  &lt;?php foreach ($topicFormats as $topicFormat): ?&gt;
      &lt;?php if ($topicFormat['medium'] &amp;&amp; $topicFormat['sumFormat']): ?&gt;
          &lt;li&gt;&lt;?= $topicFormat['medium'] ?&gt;&lt;/li&gt;
              &lt;?php if ($topicFormat['sumFormat']): ?&gt;
                  &lt;ul&gt;
                  &lt;li&gt;&lt;?= $topicFormat['sumFormat'] ?&gt;
                      &lt;?php if (!$topicFormat['format']): ?&gt;
                          Games
                      &lt;?php else: ?&gt;&lt;?= $topicFormat['format'] ?&gt;
                          &lt;?php endif; ?&gt;
                  &lt;/li&gt;
                  &lt;/ul&gt;
              &lt;?php endif; ?&gt;
      &lt;?php endif; ?&gt;
  &lt;?php endforeach; ?&gt;
  </code></pre>
  
  <p></p>
  
  <p>The final HTML looks like this:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>However I <em>want</em> the result below:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>I have checked this issue on StackOverFlow but did not find any solution.
  Any help would be appreciated!</p>
  
  <p><strong>Edit:</strong> I haven't had a chance to try out any of your suggestions yet, but in answer to <strong>Kapilgopinath</strong>, here is the resultant array (I think this is what you're asking for - I've never retrieved a resultant array before!):</p>
  
  <pre><code>Array 
  (
  [0] =&gt; Games
  [medium] =&gt; Games
  [1] =&gt; 1 
  [medium_id] =&gt; 1 
  [2] =&gt; 
  [format] =&gt; 
  [3] =&gt; 1 
  [sumFormat] =&gt; 1 
  ) 
  </code></pre>
  
  <p>("Games" doesn't have a format, so it returns null - that would be where other medium types would list "Paperback", "CD", etc.)</p>