PHP echo repeating rows only once

Question

Hi all,

I have 2 tables. One table is 'headings' & contains Headings and the other one is 'sub-headings' and contains Sub-headings that are (& must be) classified under the Headings and must have a respective Heading. A Sub-Heading can have only one Heading and many sub-headings can have the same Heading. So consider the following format:

--Table heading

heading_id heading
1           H1
2           H2
3           H3


--Table sub_heading

sub_head_id sub_heading heading_id
1           SH1             1
2           SH2             1
3           SH3             2
4           SH4             3
5           SH5             2
6           SH6             4

What I would like to do is query the database only once and get all sub_headings and their corresponding headings (using Inner Join?) and output a Heading only once and list all the sub_headings listed under their respective Heading. So, in practice, I would like to show up the OUTPUT RESULT AS:

H1
--SH1
--SH2

H2
--SH3
--SH5

H3
--SH4

H4
--SH6

As you can see, the Headings are echo'ed only once and all the sub-headings that belong to a Heading are nicely shown under their respective Heading. So I would like to know what query I would write to do so.

NOTE: I have already written a code to fetch heading_id and while doing so in a loop, query the database for the respective sub-headings. This again results in loop of the sub_headings. But (I feel) the drawback in this is that for every single Heading that exists, the DB will be queried at least once to get their corresponding sub_headings. So if 50 Headings exist, while fetching them in a loop, a 2nd query is done to get the sub-headings. This needs me to write down a second query to get the sub-headings and process them in a loop. I want to avoid this.

So it would really help if anyone can tell me how to query the DB only once and output the result as I have indicated above. Thanks in advance.

Answer 1

select * 
from heading h, subheading s 
where s.h = h.id 
order by h.title, s.title

loop over the result and when the heading_id changes, output a new section

Answer 2

$query = "SELECT
    h.heading,
    s.sub_heading 
FROM
    heading h
LEFT JOIN
    sub_heading s
ON
    h.id = s.heading_id
ORDER BY
    s.heading_id ASC,
    s.sub_head_id ASC";

$resource = mysql_query($query);

$previous_heading = false;

// This will print the headings in a unordered list, modify if needed
echo '<ul>';
while($resource = mysql_fetch_assoc($resource)) {
    if(!$previous_heading || $previous_heading != $resource['heading']) {
        if($previous_heading) {
            echo '</ul></li>';
        }
        echo '<li>'.$resource['heading'].'<ul>';
    }
    echo '<li>'.$resource['sub_heading'].'</li>';
    $previous_heading = $resource['heading'];
}
echo '</ul></ul>';

Will output something like

• H1
  
  • SH1
  • SH2
• H2
  
  • SH3
  • SH5
• H3
  
  • SH4
• H4
  
  • SH6