Reputation: 55
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
Upvotes: 1
Views: 51
Reputation: 156
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.
Upvotes: 1
Reputation: 33823
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.
Upvotes: 0