Kill me now!

I have two tables in my DB. One has a list of certifications and one has a list of exam boards.

I need to list the exam boards in a foreach loop giving each one an accordion. The certifications must be listed under their correct exam board. I have the code below but it of course lists things all over the shop. Any help would be appriciated. I can't figure out the best method to list the correct courses under their correct exam boards using a nested foreach loop. Thanks for any help!

<div class="row-fluid ">
<div class="span12">
    <?php
    $mysqli = mysqli_connect('localhost', 'root', '', 'studybubble');
    $query = "SELECT * FROM `vendors` ORDER BY `vendor` ASC";
    $result = $mysqli->query($query);

    $accordionNum = 1;
    $collapseNum = 1;

    while($vendorInfo = $result->fetch_array())
        {$vendors[] = $vendorInfo;}

        foreach($vendors as $vendorInfo){
            echo'
            <div class="portlet box blue">
                    <div class="portlet-title">
                        <div class="caption">
                        <i style="margin-right: 10px;" class="icon-check"></i>'.$vendorInfo['vendor'].'</div>
                        <div class="tools">
                            <a href="javascript:;" class="collapse"></a>
                        </div>
                    </div>
                    <div class="portlet-body">
                        <div class="accordion" id="accordion'.$accordionNum.'">
                            <div class="accordion-group">';

        $query2 = "SELECT * FROM `courses` WHERE `vendorid` = '".$vendorInfo['id']."' ORDER BY `title` ASC";
        $result2 = $mysqli->query($query2);

        while($courseInfo = $result2->fetch_array()){$courses[] = $courseInfo;}

foreach($courses as $courseInfo){
    echo'    
        <div class="accordion-heading">
            <a class="accordion-toggle collapsed" data-toggle="collapse" data-parent="#accordion" href="#collapse_'.$collapseNum.'">
            <i style="margin-right: 12px;" class="icon-plus"></i>'.$courseInfo['title'].'</a>
                        </div>

                        <div id="collapse_'.$collapseNum.'" class="accordion-body collapse">
                            <div class="accordion-inner">
                                <p>
                                    Content
                                </p>
                            </div>
                        </div>
                    </div>
                </div>';

            $collapseNum++;}

            $accordionNum++; }

            echo('</div>');

            ?>

            </div> 
        </div>

If you create a single query which joins both tables, and order the results in the right way, you can suffice with a single foreach.

I'm struggling to see how I could use that to seperate accordian titles from the collapsable sections? Wouldn't I still be in the same situation but with just one query? Or am I not thinking hard enough.. Probably the latter

Please give a sample of the tables.
For example, every column name and perhaps a brief description.

You can make 1 query return information from 2 or more tables using INNER JOIN.

'courses' table:
* id
* vendorid
* course_title
* date_added
* last_updated
* author

'vendors' table:
* id
* vendor_name

vendorid in 'courses' table is the same as id in the 'vendors' table. That is their link.

    SELECT
      c.id AS CID, c.vendorid AS VID,
      c.course_title, c.author,
      c.date_added, c.last_updated
    FROM `courses` AS c
    JOIN `vendors` AS v
    ON VID=v.id;

Print the result and think about it for a while

That style of querying is very new to me so thank you for introducing it. Would you mind explaining how I would use the result when querying like this?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.