PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
Iam trying to display categories and sub-categories from 2 tables in my db.
The first do while iterates through the main level categories. there are 4 of them. so the loop goes 4 times. Now... each of those iterations Im grabing the sub category from 'dir_categores2' table. So in essence I would think ... for each iteration it should display the sub categories for each of the main categories, but it only displays the first main categories subs and not the other 3.
Do I have to query the db on each of the 4 iterations to get each main categories subs? Cant I use a reference variable in some way instead of hitting the db 4 times? (Im not very clear on how reference works)
1. You should use while () {} statement instead of do {} while() and redundant ifs.
2. You should use JOIN at DB level instead doing it at code level. That is for:
I have actually posted a solution to this same question several times in the past. You need two queries and two loops. One query fetches the categories, the other fetches the subcats. Loop the categories result, and while you are in that loop, loop the subcat data set matching catid between the two so that only subcats with catids are shown while in a cat loop.
Everah wrote:You need two queries and two loops. One query fetches the categories, the other fetches the subcats. Loop the categories result, and while you are in that loop, loop the subcat data set matching catid between the two so that only subcats with catids are shown while in a cat loop.
It is bad for speed, but good for minimizing resource usage ... I would alway prefer to do this at DB level.
There are 10 types of people in this world, those who understand binary and those who don't
I tried it your way, but am having an issue as I stated above...
Everah -
Also tried it your way but still only gives results for the first Main Category. It does display the all 4 main cats but only subcats for the first main cat.
If I was doing this I would go with VladSuns suggestion with the one query. On each iteration of the loop compare the current parent id with the previous parent id. If the two are different you have a new parent to output.
The above code fragment is not perfect though. On the first iteration of this code it will create html to close an unopened list element, so you should add an extra check for when parentId === false
<?php
include 'include/mysql_connect.php';
// Fetch the categories
$sql = "SELECT * FROM dir_categories";
if (!$result = mysql_query($sql)) {
die('There was an error in the query:<br /><strong>' . $sql . '<br />' . mysql_error());
}
// Fill the category array, initializing it first
$cats = array();
while ($row = mysql_fetch_array($result)) {
$cats[] = $row;
}
$cat_count = count($cats);
// Now the sub categories
$sql = "SELECT * FROM dir_categories2";
if (!$result = mysql_query($sql)) {
die('There was an error in the subcategory query:<br /><strong>' . $sql . '<br />' . mysql_error());
}
// And fill the subcategory array
$subcats = array();
while ($row = mysql_fetch_array($result)) {
$subcats[] = $row;
}
$subcat_count = count($cats);
// Only show stuff if there are categories to show
if ($cats_count) {
// If we are at this point, there is at least one category...
// Start looping those, opening a list tag to start
echo '<ul class="notebookList">';
for ($i = 0; $i < $cats_count; $i++) {
echo '<li><a href="#">' . $cats[$i]['cat_name'] . '</a>';
// Again, only loop subcats if there are some
if ($subcats_count) {
echo '<ul>';
for ($j = 0; $j < $subcat_count; $j++) {
if ($subcats[$j]['cat_rel'] == $cats[$i]['id']) {
echo '<li><a href="#">' . $subcats[$j]['sub_cat_name'] . '</a></li>';
}
}
echo '</ul>';
}
echo '</li>';
}
echo '</ul>';
}
?>
<?php
include 'include/mysql_connect.php';
// Fetch the categories
$sql = "SELECT * FROM dir_categories order by id";
if (!$result = mysql_query($sql)) {
die('There was an error in the query:<br /><strong>' . $sql . '<br />' . mysql_error());
}
// Fill the category array, initializing it first
$cats = array();
while ($row = mysql_fetch_array($result)) {
$cats[] = $row;
}
$cat_count = count($cats);
// Now the sub categories
$sql = "SELECT * FROM dir_categories2 order by cat_rel";
if (!$result = mysql_query($sql)) {
die('There was an error in the subcategory query:<br /><strong>' . $sql . '<br />' . mysql_error());
}
// And fill the subcategory array
$subcats = array();
while ($row = mysql_fetch_array($result)) {
$subcats[] = $row;
}
$subcat_count = count($cats);
$pos = 0;
// Only show stuff if there are categories to show
if ($cats_count) {
// If we are at this point, there is at least one category...
// Start looping those, opening a list tag to start
echo '<ul class="notebookList">';
for ($i = 0; $i < $cats_count; $i++) {
echo '<li><a href="#">' . $cats[$i]['cat_name'] . '</a>';
// Again, only loop subcats if there are some
if ($subcats_count && $subcats_count > $pos) {
echo '<ul>';
for ($j = $pos; $j < $subcat_count; $j++) {
if ($subcats[$j]['cat_rel'] == $cats[$i]['id']) {
echo '<li><a href="#">' . $subcats[$j]['sub_cat_name'] . '</a></li>';
}
else
{
$pos = $j;
break;
}
}
echo '</ul>';
}
echo '</li>';
}
echo '</ul>';
}
?>
There are 10 types of people in this world, those who understand binary and those who don't