I have 2 tables (posts, categories), There is one to many relation between both tables(each category could contain more that one post).
posts table
___________________________________________________
| | | | | |
| id | title | content | category_id | posts_order |
|____|_______|_________|_____________|_____________|
| | | | | |
| 1 | test1 | testing | 1 | 0 |
|____|_______|_________|_____________|_____________|
| | | | | |
| 2 | test2 | testing | 1 | 1 |
|____|_______|_________|_____________|_____________|
| | | | | |
| 3 | test3 | testing | 2 | 2 |
|____|_______|_________|_____________|_____________|
| | | | | |
| . | ..... | ....... | . | . |
|____|_______|_________|_____________|_____________|
categories table
___________________________
| | | |
| c_id | c_name | c_order |
|______|________|_________|
| | | |
| 1 | cat1 | 0 |
|______|________|_________|
| 2 | cat2 | 1 |
|______|________|_________|
| | | |
| 3 | cat3 | 2 |
|______|________|_________|
| . | ..... | ....... |
|______|_______|__________|
There is a relation(one-to-many), Using id from posts and c_id from categories.
I want to show the categories in tabs, then inside each tab there will be the related posts:
________________________________________
| | | |
| cat1 | cat2 | cat3 |
| (active) |__________|__________|
| |
| |
| test1 |
| testing |
| |
| test2 |
| testing |
|_______________________________________|
So for example if the first tab is active, The related posts from cat1, Which are test1 and test2 should be shown with there content as an accordion.
I tried:
SELECT * FROM `categories` LEFT JOIN `posts` ON categories.c_id = posts.id order by categories.c_order ASC
Then print that data to the tabs and accordion:
<!-- Nav tabs -->
<ul class="nav nav-tabs" role="tablist">
<?php foreach ($results as $result) { ?>
<li role="presentation">
<a href="#<?php echo $result['c_name'] ?>" aria-controls="<?php echo $result['c_name'] ?>" role="tab" data-toggle="tab">
<?php echo $result['c_name'] ?>
</a>
</li>
<?php } ?> //end foreach
</ul> <!-- Nav tabs -->
<!-- Tab panes -->
<div class="tab-content">
<?php foreach ($results as $result) { ?>
<div role="tabpanel" class="tab-pane" id="<?php echo $result['c_name'] ?>">
<div class="panel-group" id="accordion" role="tablist" aria-multiselectable="true">
<div class="panel panel-default">
<div class="panel-heading" role="tab" id="headingOne">
<h4 class="panel-title">
<a role="button" data-toggle="collapse" data-parent="#accordion" href="#<?php echo $result['id']; ?>_post" aria-expanded="true" aria-controls="collapseOne">
<?php echo $result['title'] ?>
</a>
</h4> <!-- .panel-title -->
</div> <!-- .panel-heading -->
<div id="<?php echo $result['id']; ?>_post" class="panel-collapse collapse in" role="tabpanel" aria-labelledby="headingOne">
<div class="panel-body">
<?php echo $result['content']; ?>
</div> <!-- .panel-body -->
</div> <!-- .panel-collapse -->
</div> <!-- .panel -->
</div> <!-- .panel-group -->
</div> <!-- .tab-pane -->
<?php } ?> //end foreach
</div> <!-- end Tab panes -->
So that I should get 3 tabs, Each tab contains each category posts and content.
But if I have 3 categories and 9 posts for example, I get 9 tabs, Each tab contains 1 post or no posts at all.
Is there is a problem with the query?
Should I change the foreach places?
Instead of using $results = $results->fetchAll(); you can fetch the rows one at a time and group them by category as you go.
while ($row = $results->fetch(PDO::FETCH_ASSOC)) {
$categories[$row['c_name']][] = $row;
}
Then loop to print the tabs can be like this:
// for the tabs, you just need the keys (the distinct categories)
foreach (array_keys($categories) as $category_name) { ...
And for the tab content:
// the tab panes
foreach ($categories as $category_name => $posts) {
// accordion container
foreach ($posts as $post) {
// individual accordion panels
I didn't include the markup so as not to obscure the logic. If you have trouble seeing how to integrate it into your markup, let me know and I'll try to clarify.