Page 1 of 1
Trying to get main & sub categories
Posted: Thu Aug 09, 2007 8:42 am
by KSquared
Hello,
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)
Code: Select all
<?php
include "include/mysql_connect.php";
$catQuery = "SELECT * FROM dir_categories";
$catResult = mysql_query($catQuery);
$subCatQuery = "SELECT * FROM dir_categories2";
$subCatResult = mysql_query($subCatQuery);
if($catRow = mysql_fetch_array($catResult)){
do{
?>
<ul class="notebookList">
<li><a href="#"><?= $catRow['cat_name']; ?></a>
<ul>
<?php
if($subCatRow = mysql_fetch_array($subCatResult)){
do{
if($subCatRow['cat_rel'] == $catRow['id']){
?>
<li><a href="#"><?= $subCatRow['sub_cat_name']; ?></a></li>
<?php
}//end inside if
}while($subCatRow = mysql_fetch_array($subCatResult));
}
?>
</ul>
</li>
</ul>
<?php
}while($catRow = mysql_fetch_array($catResult));
}
?>
Hope that makes sense
Thanks
Posted: Thu Aug 09, 2007 9:31 am
by VladSun
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:
Code: Select all
if($subCatRow['cat_rel'] == $catRow['id']){
You could use then only one query:
Code: Select all
SELECT dir_categories.cat_name, dir_categories2.sub_cat_name
FROM dir_categories
INNER JOIN dir_categories2 ON dir_categories2.cat_rel = dir_categories.id
Posted: Thu Aug 09, 2007 12:56 pm
by KSquared
Thanks VladSun! The Join works great... but, it now displays like so:
Main Category 1
sub 1
Main Category 1
sub 2
Main Category 2
sub 1
Main Category 2
sub 2
etc.....
Here is the way Im writing it now:
Code: Select all
<ul class="notebookList">
<?php
while($catRow = mysql_fetch_array($catResult)){
?>
<li><a href="#"><?= $catRow['cat_name']; ?></a>
<ul>
<li><a href="#"><?= $catRow['sub_cat_name']; ?></a></li>
</ul>
</li>
<?php
}
?>
</ul>
I need it to display like so...
Main Category
sub 1
sub 2
sub 3
Main Category
sub1
Main Category
sub 1
sub 2
Main Category
sub 1
sub 2
Im not sure how to filter my result now that all the main and sub categories are in one result.
Thanks
Posted: Thu Aug 09, 2007 1:03 pm
by RobertGonzalez
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.
Posted: Thu Aug 09, 2007 1:27 pm
by VladSun
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.
Posted: Thu Aug 09, 2007 2:13 pm
by KSquared
VladSun -
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.
Code:
Code: Select all
<ul class="notebookList">
<?php
while($catRow = mysql_fetch_array($catResult)){
?>
<li><a href="#"><?= $catRow['cat_name']; ?></a>
<ul>
<?php
while($subCatRow = mysql_fetch_array($subCatResult)){
if($catRow['id'] == $subCatRow['cat_rel']){
?>
<li><a href="#"><?= $subCatRow['sub_cat_name']; ?></a></li>
<?php
}
}
?>
</ul>
</li>
<?php
}
?>
</ul>
still confused...

Posted: Thu Aug 09, 2007 3:07 pm
by VladSun
Code: Select all
SELECT dir_categories.id, dir_categories.cat_name, dir_categories2.sub_cat_name
FROM dir_categories
INNER JOIN dir_categories2 ON dir_categories2.cat_rel = dir_categories.id
Code: Select all
<ul>
<?php
$cat_id = 0;
while($catRow = mysql_fetch_array($catResult))
{
if ($cat_id != $catRow['id'])
{
if ($cat_id)
{
echo "</ul></li>";
}
?>
<li><a href="#"><?= $catRow['cat_name']; ?></a><ul>
<?php
$cat_id = $catRow['id'];
}
?>
<li><a href="#"><?= $catRow['sub_cat_name']; ?></a></li>
<?php
}
if ($cat_id)
{
echo "</li></ul>";
}
?>
</ul>
Posted: Thu Aug 09, 2007 3:09 pm
by John Cartwright
That INNER JOIN probably should be a LEFT JOIN
Posted: Thu Aug 09, 2007 3:17 pm
by mikesmith76
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.
something like this should get you started
Code: Select all
<ul class="notebookList">
<?php
$parentId = false;
while($catRow = mysql_fetch_array($catResult))
{
if($parentId != $catRow['id'])
{
$parentId = $catRow['id];
print "</ul>" .
"</li>" .
"<li><a href='#'>{$catRow['cat_name']}</a>" .
"<ul>";
}
print "<li><a href='#'>{$catRow['sub_cat_name']}</a></li>";
}
print "</li></ul>";
?>
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
It should get your in the right direction though.
Posted: Thu Aug 09, 2007 3:26 pm
by mikesmith76
the man beat me to it

Posted: Thu Aug 09, 2007 3:27 pm
by VladSun
Jcart wrote:That INNER JOIN probably should be a LEFT JOIN
Sure ... don't I ask me why I wrote INNER - I don't know

Posted: Thu Aug 09, 2007 3:38 pm
by RobertGonzalez
Code: Select all
<?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>';
}
?>
Posted: Thu Aug 09, 2007 3:57 pm
by KSquared
mikesmith76 -
Perfect!!
Thank you ALL for your persistence and patience!
Lesson learned
Keith
Posted: Thu Aug 09, 2007 4:01 pm
by VladSun
3 bugs in my code fixed - including the one mikesmith76 talked about.
@Everah
Code: Select all
<?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>';
}
?>