PDO run query inside while loop showing only 1 result

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.

There should be a link between the two tables. Go back to your entity relationship diagram. I hope you have one — Rotimi
– Rotimi, Commented Oct 28, 2017 at 12:10
@Akintunde - there is a link between the tables, c.id = sc.cat_id obviously. — ArtisticPhoenix
– ArtisticPhoenix, Commented Oct 28, 2017 at 12:13

Your Common Sense · Accepted Answer · 2017-10-28 15:49:46Z

4

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.

edited Oct 28, 2017 at 15:49

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

answered Oct 28, 2017 at 12:08

ArtisticPhoenix

21.7k2 gold badges27 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

ArtisticPhoenix Over a year ago

simple, Left join then

Your Common Sense Over a year ago

so in fact you need just $data = $conn->query($sql)->fetchAll(PDO::FETCH_GROUP);

ArtisticPhoenix Over a year ago

@YourCommonSense - I actually never used that before it's not covered well on the php.net website. So thanks, learn something new every day, 6 years PHP and never noticed that one.

ArtisticPhoenix Over a year ago

Satisfied ( deleted )? I'm tired been up about 27 hours, not that it matters in the grand scope of things. But I'm just trying to help the OP out. TBH, I'm surprised he didn't get like a billion down votes yet.

ArtisticPhoenix Over a year ago

And Just FYI, I'm mostly self taught, and I blame the php.net site for lack of documentation on that fetch mode. php.net/manual/en/pdostatement.fetchall.php There is what like half a sentence about it?

|

Collectives™ on Stack Overflow

PDO run query inside while loop showing only 1 result

1 Answer 1

17 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related