PDO在内部运行查询，而循环只显示1个结果

Question

I want to get categories and sub categories. Both tables has different tables. Please see the below table structures:

tbl_category

 id | name   | order_number
  1 | Allgem | 1
  2 | Vorauss| 2
  3 | Support| 3
  4 | Zielste| 4

tbl_sub_category

 id | cat_id   | sub_name
  1 | 1        | Alter 
  2 | 2        | Trainingsalter
  3 | 2        | Kader
  4 | 2        | Wettkampfsystem
  5 | 3        | Trainingsort
  6 | 3        | Team
  7 | 4        | Betreuung
  8 | 4        | Unterstuetzung

I have tried with the below answer:

query inside while loop only shows 1 result

Below is my code:

<ul class="list-unstyled components">
    <p style="text-align: center;font-weight: 600">Categories</p>
    <?php
        $query = $conn->prepare('SELECT * FROM tbl_category');
        $query->execute();
        $i = 1;
        $firstResults = array();
        while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
        $ID = $row['id'];
    ?>
    <li class="">
        <a href="#homeSubmenu<?php echo $i;?>" data-toggle="collapse" aria-expanded="false"><?php echo $row['cat_name']; ?></a>
        <?php 
            $querySub = $conn->prepare('SELECT * FROM tbl_sub_category WHERE cat_id=:cat_id');
            $querySub->execute(array(':cat_id' => $ID));
            while ($rowSub = $querySub->fetch(PDO::FETCH_ASSOC)) {
        ?>
        <ul class="collapse list-unstyled" id="homeSubmenu<?php echo $i;?>">
            <li><a href="?cat=Allgem&sub=Beschreibung"><?php echo $rowSub['sub_name']; ?></a></li>
        </ul>
        <?php } ?>
    </li>
    <?php $i++; } ?>
</ul>

Please help us guys!!!!

UPDATED QUESTION:

Please see the above table relationship of both tables.

Answer 1

Try a Join like this

SELECT
    c.name,
    sc.*,
    c.order_number
FROM
    tbl_sub_category AS sc
INNER JOIN
    tbl_category AS c ON c.id = sc.cat_id

You can test this one here https://www.db-fiddle.com/f/vX8Z6jPbaweBDjGtvPwRdP/0

There is no need to pull, c.id (it would be ambiguous, anyway ) and you already have it as sc.cat_id. So, what we really need is everything from the sub-category table (alias as sc) and just the category name and order_number from the category table (aliased as c).

If you need main categories without a sub-category then you can do a Left Join. You'll have to account for some null values, which would be the case no matter what.

SELECT
    c.name,
    sc.*,
    c.order_number
FROM
    tbl_category AS c
LEFT JOIN
     tbl_sub_category AS sc ON c.id = sc.cat_id

And this one here https://www.db-fiddle.com/f/kdgD9K5TYnD79boUdaC57k/0

I intentionally left sub-category 6 & 7 out, to show how it works for the left join, this way cat 4 doesn't have any sub-categories. It's a good Idea to have a proper test case if you need to account for those.

After you get your query sorted out (one call instead of O{n} calls), you'd follow pretty standard procedure, structure the data then output it. Like so:

<?php
$data = $conn->query($sql)->fetchAll(PDO::FETCH_GROUP);
?>

First thing to do is group the data by category, so we can have a item for category and several items for sub-categories. The category name $row['name'] works great for this, because we need it outside of the inner foreach loop. As mentioned by @YourCommonSense, in the comments, we can easily do this by using PDO::FETCH_GROUP and putting the name column as the first column in our selection list.

Which will give you something like this:

$data = [
    'Allgem' => [
        ['id'=>'1','cat_id'=>'1','sub_name'=>'Alter','name'=>'Allgem','order_number'=>'1'],
    ],
    'Vorauss' => [
        ['id'=>'2','cat_id'=>'2','sub_name'=>'Trainingsalter','name'=>'Vorauss','order_number'=>'2'],
        ['id'=>'3','cat_id'=>'2','sub_name'=>'Kader','name'=>'Vorauss','order_number'=>'2'],
        ['id'=>'4','cat_id'=>'2','sub_name'=>'Wettkampfsystem','name'=>'Vorauss','order_number'=>'2'],
    ],
    'Support' => [
        ['id'=>'5','cat_id'=>'3','sub_name'=>'Trainingsort','name'=>'Support','order_number'=>'3'],
        ['id'=>'6','cat_id'=>'3','sub_name'=>'Team','name'=>'Support','order_number'=>'3'],
    ]
];

As you can see, now we have a structure grouped by the cat ID. This will work much better for what we need to do next. Because we pre-format the data the way we want it makes our code simpler, more concise and easier to read.

<ul class="list-unstyled components">
    <p style="text-align: center;font-weight: 600">Categories</p>
    <?php
        $i = 0;
        foreach ($data as $cat_name => $sub_categories):
    ?>
        <li class="">
            <a href="#homeSubmenu<?php echo $i;?>" data-toggle="collapse" aria-expanded="false"><?php echo $cat_name ; ?></a>
            <?php foreach ($sub_categories as $row): ?>
                <ul class="collapse list-unstyled" id="homeSubmenu<?php echo $i;?>">
                    <li><a href="?cat=Allgem&sub=Beschreibung"><?php echo $row['sub_name']; ?></a></li>
                </ul>
            <?php endforeach; ?>
        </li>
    <?php
        $i++;
        endforeach;
    ?>
</ul>

You can test it here, but it's near impossible to find a phpSandbox that allows you to mix HTML, PHP, save it, and output in HTML. So it's just the raw source html, but you can see it's syntactically correct and error free.

http://sandbox.onlinephpfunctions.com/code/1030bfd98c73d12b718077363d30ac587166c6cb

On the topic of errors, you also had several other issues. Like the sub-link had a static address <a href="?cat=Allgem&sub=Beschreibung">. You had $row['cat_name'] which dosn't exist in your table schema (its just tbl_category.name). Another one is this <p> tag between the first <ul> and <li> I didn't see a reasonable way to fix that without changing the structure, because well the structure is the issue. See below:

<ul class="list-unstyled components">
    <p style="text-align: center;font-weight: 600">Categories</p> <!-- p tags shouldn't be here -->
    ....
     <li class="">

Personally, I would recommend using a dictionary list, or <dl>

https://www.w3schools.com/tags/tag_dl.asp

Which would let you do something like this

<dl class="list-unstyled components">
    <dt><p style="text-align: center;font-weight: 600">Categories</p></dt
     <dd class=""><!-- instead of the li tag --></dd>
</dl>

Or just remove the <p> tag altogether? Anyway, there may be other issues with your original HTML that I didn't see. But I figure this should get you on the right track.

Cheers.