Trying to get main & sub categories

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!

Moderator: General Moderators

Post Reply
KSquared
Forum Commoner
Posts: 25
Joined: Tue Aug 29, 2006 4:07 pm

Trying to get main & sub categories

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
KSquared
Forum Commoner
Posts: 25
Joined: Tue Aug 29, 2006 4:07 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
KSquared
Forum Commoner
Posts: 25
Joined: Tue Aug 29, 2006 4:07 pm

Post 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... :?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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>
Last edited by VladSun on Thu Aug 09, 2007 3:52 pm, edited 3 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

That INNER JOIN probably should be a LEFT JOIN
mikesmith76
Forum Commoner
Posts: 34
Joined: Fri Aug 25, 2006 7:10 am
Location: Manchester, UK

Post 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.
mikesmith76
Forum Commoner
Posts: 34
Joined: Fri Aug 25, 2006 7:10 am
Location: Manchester, UK

Post by mikesmith76 »

the man beat me to it :-)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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 :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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>';
}
?>
KSquared
Forum Commoner
Posts: 25
Joined: Tue Aug 29, 2006 4:07 pm

Post by KSquared »

mikesmith76 -
Perfect!!

Thank you ALL for your persistence and patience!

Lesson learned

Keith
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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>';
}
?>
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply