While inside while inside while

Question

EDIT - SOLUTION: Moved $sub = ''; to before the first while
I made this code to show a scoreboard of a competition.
The first while: Get all categories.
The second while: Get all subcategories from categories.
The third while: Get all records from subcategories.
The problem is:
If the first loop order by DESC it is printing only the first result and doing
the whiles to this category.
If i change to ASC it prints nothing.
NOTE: Not every subcategories have records.

function SelectAllRecords() {
    global $db;
    try{
        $query = $db->query("SELECT id, name FROM category ORDER BY name DESC");
        while($category = $query->fetch(PDO::FETCH_ASSOC)) {
            $categoryid = $category['id'];
            $sub = '';
            $querysub = $db->query("SELECT id, id_category, name FROM category_sub WHERE id_category=".$categoryid);
            while($subcategory = $querysub->fetch(PDO::FETCH_ASSOC)) {
                if(!is_numeric($subcategory['id'])){
                    continue;
                }else{
                    $subcategoryname = $subcategory['name'];
                    $sub .='<h2>'.$category['name'].'</h2>
                        <div class="widget-box">
                            <div class="widget-title"> <span class="icon"><i class="icon-th"></i></span>
                                <h5>'.$subcategory['name'].'</h5>
                            </div>
                            <div class="widget-content nopadding">
                                <table class="table table-bordered data-table" id="tabledata">
                                    <thead>
                                        <tr>
                                            <th class="columnadjust">Código</th>
                                            <th>Competidor</th>
                                            <th class="columnadjust">Ações</th>
                                        </tr>
                                    </thead>
                                    <tbody id="tablecontent">
                    ';
                    $record = $db->query("SELECT records.id as id, competitor.name as competitor, category.name as category, category_sub.name as subcategory, records.amplifier as amplifier, records.speaker as speaker, records.battery as battery, records.decibels as decibels, records.car as car FROM records LEFT JOIN competitor ON competitor.id = records.id_competitor LEFT JOIN category ON records.id_category = category.id LEFT JOIN category_sub ON records.id_subcategory = category_sub.id WHERE category_sub.name = '".$subcategoryname."' ORDER BY points ASC");
                    while($records = $record->fetch(PDO::FETCH_ASSOC)) {
                        if(!is_numeric($records['id'])){
                            $sub .= '<tr class="gradeX"></tr>';
                        }else{
                            $sub .= '
                            <tr class="gradeX">
                                <td class="columnadjust">'.$records['id'].'</td>
                                <td>'.$records['competitor'].'</td>
                                <td class="columnadjust">
                                    <a data-id="'.$records['id'].'" title="Editar" class="itemedit btn btn-warning btn-mini">Editar</a>
                                </td>
                            </tr>';
                        }
                    }
                    $sub .= '</tbody></table></div></div>';
                }
            }
        }
        $result = $sub;
        echo $result;
        return true;
    } catch (PDOException $pe) {
        return false;
    }

}

*The first while is resulting everything and then printing the second?* - What is it printing and what should it print? Can you show an example? — Aydin4ik, Oct 06 '17 at 01:21
all I can get from this is that you're not calling your `SelectAllRecords()` method; least from what you posted anyway. — Funk Forty Niner, Oct 06 '17 at 01:27
`WHERE category_sub.name = '.$subcategoryname.'` that, seems to be a string here. — Funk Forty Niner, Oct 06 '17 at 01:29
@axlj The code needs improvement on many levels.. The problem is that suggesting an edit here could break the code elsewhere. — Aydin4ik, Oct 06 '17 at 01:34

Aydin4ik · Accepted Answer · 2017-10-06T01:54:22.193

0

Maybe it could help to reformat the queries to reflect the variable values off the bat (instead of processing them later - which may or may not be the case in your software): Replace

$querysub = $db->query("SELECT id, id_category, name FROM category_sub WHERE id_category=$categoryid");

with

$querysub = $db->query("SELECT id, id_category, name FROM category_sub WHERE id_category=".$categoryid);

And replace

$record = $db->query("SELECT records.id as id, competitor.name as competitor, category.name as category, category_sub.name as subcategory, records.amplifier as amplifier, records.speaker as speaker, records.battery as battery, records.decibels as decibels, records.car as car FROM records LEFT JOIN competitor ON competitor.id = records.id_competitor LEFT JOIN category ON records.id_category = category.id LEFT JOIN category_sub ON records.id_subcategory = category_sub.id WHERE category_sub.name = '.$subcategoryname.' ORDER BY points ASC");

with

$record = $db->query("SELECT records.id as id, competitor.name as competitor, category.name as category, category_sub.name as subcategory, records.amplifier as amplifier, records.speaker as speaker, records.battery as battery, records.decibels as decibels, records.car as car FROM records LEFT JOIN competitor ON competitor.id = records.id_competitor LEFT JOIN category ON records.id_category = category.id LEFT JOIN category_sub ON records.id_subcategory = category_sub.id WHERE category_sub.name = '".$subcategoryname."' ORDER BY points ASC");

Also, you are stopping the entire code from execution on this line:

if(!is_numeric($subcategory['id'])){
    continue;
}

Replace continue with break to stop only that iteration and continue with the next one:

if(!is_numeric($subcategory['id'])){
    break;
}

edited Oct 06 '17 at 01:54

answered Oct 06 '17 at 01:29

Aydin4ik

1,782
1
14
19

1

so we need to go through your code to compare what you did? You need to explain this answer. – Funk Forty Niner Oct 06 '17 at 01:30
The first sentence explains what I did. – Aydin4ik Oct 06 '17 at 01:31
@Aydin I edited the problem and the code – Site Factory Oct 06 '17 at 01:45
@Fred-ii- I edited the problem and the code – Site Factory Oct 06 '17 at 01:45
@SiteFactory remove `return true` from the end. Next time a solution answers your question, accept that answer and continue the conversation - don't change the question to ask a new question. – Aydin4ik Oct 06 '17 at 01:48
@Aydin Sorry, im new here. Thank you – Site Factory Oct 06 '17 at 01:50
@Aydin removing the return true didnt solved but I will keep trying. thanks for your help – Site Factory Oct 06 '17 at 01:51
@SiteFactory edited the answer, see the bit towards the end. – Aydin4ik Oct 06 '17 at 01:54

While inside while inside while

1 Answers1