PHP echo repeating rows only once
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 cor开发者_运维知识库responding 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.
$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
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
精彩评论