PHP echo repeating rows only once

Question

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.

Tatu Ulmanen · Accepted Answer · 2010-01-01 20:25:13Z

3

$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

edited Jan 1, 2010 at 20:25

answered Jan 1, 2010 at 19:48

Tatu Ulmanen

125k34 gold badges190 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Devner Over a year ago

@Tatu, you are missing a comma my friend, in your query. It should have been: s.heading_id ASC, s.sub_head_id ASC Minor correction, but may help anyone in future.

KJS Over a year ago

2016 now, and this is still really genius!

Sander Rijken · Accepted Answer · 2010-01-01 19:43:18Z

3

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

edited Jan 1, 2010 at 19:43

Sander Rijken

21.7k3 gold badges65 silver badges86 bronze badges

answered Jan 1, 2010 at 19:41

jspcal

52k7 gold badges76 silver badges79 bronze badges

1 Comment

Devner Over a year ago

Thanks for your comment. You are right about the looping, but actually I needed the Code to do that which the other user (Tatu) has provided. I appreciate your efforts to answer my question.

Collectives™ on Stack Overflow

PHP echo repeating rows only once

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related