使用制表符和手风琴显示结果

Question

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?

Answer 1

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.