using nested while loops not giving desired output

Question

what i am trying to do is create a list of categories that are pulled from the database, and then list underneath each of those categories the sub-categories that are relevant.

so far i have got:

<?php
// this query lets us know that we are looking for matches that equal 2,      which points to the subject of vehicles.
// from this query i am able to list all categories that are relevant to vehicles.

$getCategoriesQuery = $db->query("SELECT * FROM item_cat WHERE sub_id =  '2'"); //2 = vehicles
?>

<?php
while($row = $getCategoriesQuery->fetch()){
    echo '<ul>'.$row['category'].'';
}
?>

which displays:

- motor vehicles
- railed vehicles
- aircraft

now, when i add the following while loop to the one above:

<?php
while($row = $getCategoriesQuery->fetch()){
    echo '<ul>'.$row['category'].'';

    // this has been added to try and get the sub categories
    $getSubCatQuery = $db->query("SELECT * FROM item_cat, item_sub_cat
                WHERE item_cat.cat_id = item_sub_cat.cat_id "); // cat_id
        while($row = $getSubCatQuery->fetch()){
            echo '<li><a href="vehicles.php?p='.$row['sub_category'].'"  >'.$row['sub_category'].'</a></li>';

        }
    echo '</ul>';
}
?>

i get:

- motor vehicles
   - cars
   - motorbikes
   - buses
   - trucks
   - trains
   - planes
-railed vehicles
   - cars
   - motorbikes
   - buses
   - trucks
   - trains
   - planes
-aircraft
   - cars
   - motorbikes
   - buses
   - trucks
   - trains
   - planes

when what i am wanting to do is:

-motor vehicles
   - cars
   - motorbikes
   - buses
   - trucks
-railed vehicles
   - trains
-aircraft
   - planes

i have tried joining the queries to begin with, but no joy, so i split into 2 queries to try and see things a bit better. i have changed "$row" to "$row2" on the second while loop, but that then gives me an undefined index error for "row2['sub_category']"

i have also tried using a "foreach loop" instead of the second "while loop":

<?php
// this query lets us know that we are looking for matches that equal 2, which points to vehicles.
$getCategoriesQuery = $db->query("SELECT * FROM item_cat WHERE sub_id = '2'"); //vehicles
?>

<?php
while($row = $getCategoriesQuery->fetch()){
    echo '<ul>'.$row['category'].'';

    $getSubCatQuery = $db->query("SELECT * FROM item_cat, item_sub_cat
                    WHERE item_cat.cat_id = item_sub_cat.cat_id "); // cat_id

        foreach ($getSubCatQuery->fetchAll () as $row2) {
        echo '<li><a href="vehicles.php?p='.$row2['sub_category'].'"  >'.$row2['sub_category'].'</a></li>';
        }
    echo '</ul>';
}
?>

i have spent a whole day trying to resolve this, but to no joy - just headache. could anyone please advise where i am going wrong... what am i missing? - i am using PDO also if that is not clear.

thanks in advance!

--UPDATED-- i have changed the while loops part to:

<?php
while($row = $getCategoriesQuery->fetch()){
    echo '<ul>'.$row['category'].'';

    $getSubCatQuery = $db->query("SELECT * FROM item_sub_cat, item_cat WHERE  item_sub_cat.cat_id ='".$row['cat_id']."' ");
        while($sub_row = $getSubCatQuery->fetch()){
            echo '<li><a href="vehicles.php?p='.$sub_row['sub_category'].'"  >'.$sub_row['sub_category'].'</a></li>';

        }
    echo '</ul>';
}
?>

and the output is displaying the things in the correct categories.... however it is duplicating each item by the total records i have in the database:

- motor vehicles
  - cars
  - motorcycles
  - trucks
  - buses
  - cars
  - motorcycles
  - trucks
  - buses
  - cars
  - motorcycles
  - trucks
  - buses
  - cars
  - motorcycles
  - trucks
  - buses
  - cars
  - motorcycles
  - trucks
  - buses
- railed vehicles
  - trains
  - trains
  - trains
  - trains
  - trains

i have tried by adding "LIMIT 1" and "GROUP BY" to the query, but then that only displays 1 item per category.

- motor vehicles
  - cars
- railed vehicles
  - trains

Answer 1

Your inner while loop is reassigning the $row array variable. Change this to another name, such as $sub_row.

Your inner query also does not change depending on the values of the outer query, and as such executes the exact same way each time. Using something like SELECT * FROM item_sub_cat WHERE item_sub_cat.cat_id = $row["category_id"] inside your inner query would allow the results to be dependent on each result of the outer query.

Edit: beware of including tables in your query that, output wise, you don't need. Only include them if you need to perform a join of both tables.

Answer 2

I don't know whether this might be useful but I would think you should be able to use a join to get all the relevant records in one go

select * from `item_cat` c
    right join `item_sub_cat` sc on sc.`cat_id`=c.`cat_id`
where c.`sub_id` =  '2'


create table if not exists `item_cat` (
  `cat_id` smallint(5) unsigned not null auto_increment,
  `category` varchar(50) default null,
  primary key (`cat_id`)
) engine=innodb auto_increment=4 default charset=utf8;

insert into `item_cat` (`cat_id`, `category`) values
    (1, 'aircraft'),
    (2, 'railed vehicles'),
    (3, 'motor vehicles');

+--------+-----------------+
| cat_id | category        |
+--------+-----------------+
|      1 | aircraft        |
|      2 | railed vehicles |
|      3 | motor vehicles  |
+--------+-----------------+


create table if not exists `item_sub_cat` (
  `scid` smallint(5) unsigned not null auto_increment,
  `cat_id` smallint(5) unsigned not null,
  `sub_category` varchar(50) not null,
  primary key (`scid`),
  key `cat_id` (`cat_id`),
  constraint `fk_cat_subcat` foreign key (`cat_id`) references `item_cat` (`cat_id`) on delete cascade on update cascade
) engine=innodb auto_increment=10 default charset=utf8;

insert into `item_sub_cat` (`scid`, `cat_id`, `sub_category`) values
    (1, 3, 'car'),
    (2, 3, 'motorbike'),
    (3, 3, 'buses'),
    (4, 3, 'truck'),
    (5, 2, 'train'),
    (6, 2, 'wagon'),
    (7, 1, 'helicopter'),
    (8, 1, 'plane'),
    (9, 1, 'microlite');

+------+--------+--------------+
| scid | cat_id | sub_category |
+------+--------+--------------+
|    1 |      3 | car          |
|    2 |      3 | motorbike    |
|    3 |      3 | buses        |
|    4 |      3 | truck        |
|    5 |      2 | train        |
|    6 |      2 | wagon        |
|    7 |      1 | helicopter   |
|    8 |      1 | plane        |
|    9 |      1 | microlite    |
+------+--------+--------------+

select * from item_cat c
    right join item_sub_cat sc on sc.cat_id=c.cat_id;

+--------+-----------------+------+--------+--------------+
| cat_id | category        | scid | cat_id | sub_category |
+--------+-----------------+------+--------+--------------+
|      3 | motor vehicles  |    1 |      3 | car          |
|      3 | motor vehicles  |    2 |      3 | motorbike    |
|      3 | motor vehicles  |    3 |      3 | buses        |
|      3 | motor vehicles  |    4 |      3 | truck        |
|      2 | railed vehicles |    5 |      2 | train        |
|      2 | railed vehicles |    6 |      2 | wagon        |
|      1 | aircraft        |    7 |      1 | helicopter   |
|      1 | aircraft        |    8 |      1 | plane        |
|      1 | aircraft        |    9 |      1 | microlite    |
+--------+-----------------+------+--------+--------------+

From the above recordset it should be relatively easy to generate the html layout that you were after - though I might have missed the point of course.