[SOLVED] Displaying Categories and Sub Categories?

adamjblakey · June 28, 2007

Hi,

I am having a problem with one of my sites when trying to display categories.

I have a sql table which is set out like this:

title | category | category2 | description | tags

There are thousands of entrys in the table and some have the same category and same category2

What i am trying to achieve is a list of all the categorys then a list of all the category2 under the categorys so basically it is like a directory with your main cat and then the sub cats under.

Just like a directory e.g.: nameplot.com

This is just rough but this is how i want it to display:

foundrecipes.com/cats.jpg

<table width="690" border="0" cellspacing="0" cellpadding="0">
					<?php

				$selects = "SELECT DISTINCT(category2), category FROM recipes ORDER BY category ASC, category2 ASC";
				$result = mysql_query($selects) or die ( mysql_error ( ) );

				while ($res = mysql_fetch_array($result)) {
					$resu[] = $res;	
				}
				mysql_free_result($result);

					$item=0;	
					$last_category = '';
				foreach ($resu AS $row) {

					if($item % 3 == 0)
						print "  <tr>\n";

					if ($row['category'] != $last_category) {
				?>
					<td bgcolor="#E8F3BE">
					 <a href="recipes.php?category=<?php echo"".$row['category'].""; ?>" class="categories"> <h1><?php echo $row['category']; ?></h1></a>
					  <br>
				<?php
					}
				?>
					  <a href="recipes.php?category2=<?php echo"".$row['category2'].""; ?>" class="subcategories">
						<?php echo"".$row['category2'].""; ?>
					  </a>
				<?php
					if ($row['category'] != $last_category) {
				?>

					</td>
				<?php
						$item++;
					}

					if($item % 3 == 0)
						print "  </tr>\n";
					$last_category = $row['category'];
				}
			?>
                </table>

At the moment it is displaying like this foundrecipes.com so i have done something wrong.

Any help would be great.

Cheers,

Adam

Wildbug · June 28, 2007

SELECT DISTINCT category,category2 FROM recipes ORDER BY category ASC, category2 ASC

That's not working? If you only want a category or category2 listed ONCE, you can do it in two separate queries.

SELECT DISTINCT category FROM recipes ORDER BY category
SELECT DISTINCT category2 FROM recipes ORDER BY category2

If you just want a list of all category and category2 without necessarily separating them (just one list of them combined), you can use a UNION.

(SELECT category FROM recipes) UNION (SELECT category2 FROM recipes) ORDER BY category

Wildbug · June 28, 2007

<table width="690" border="0" cellspacing="0" cellpadding="0">
<?php

$selects = "SELECT DISTINCT category2,category FROM recipes ORDER BY category,category2";
$result = mysql_query($selects) or die ( mysql_error ( ) );
if ($result && mysql_num_rows($result)) {
$item = 0;	
$last_category = '';
while ($res = mysql_fetch_assoc($result)):
	if ($item % 3 == 0) print "\t<tr>\n";
	if ($row['category'] != $last_category):
?>
	<td bgcolor="#E8F3BE">
		<a href="recipes.php?category=<?php echo $row['category'] ?>" class="categories"> <h1><?php echo $row['category'] ?></h1></a><br>
<?php
	endif;
?>
<a href="recipes.php?category2=<?php echo $row['category2'] ?>" class="subcategories"><?php echo $row['category2'] ?></a> 
<?php
	if ($row['category'] != $last_category) echo "\t\t</td>";
	$item++;
	if ($item % 3 == 0) print "\t</tr>\n";
	$last_category = $row['category'];
endwhile;
}
?>
</table>

Barand · June 28, 2007

or

<?php
$cnx = mysql_connect('localhost');
mysql_select_db ('test3', $cnx);

$sql = "SELECT category, GROUP_CONCAT(DISTINCT category2 ORDER BY category2 SEPARATOR ', ') as subcats
        FROM recipes
        GROUP BY category" ;
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
$k=0;
echo '<table cellspacing="1" cellpadding="4" border="1">';
while (list($cat, $subs) = mysql_fetch_row($res))
{
    if ($k % 3 == 0) echo '<tr>';
    echo "<td><b>$cat</b><br>$subs</td>";
    $k++;
    if ($k % 3 == 0) echo '</tr>';
}

if ($k % 3 != 0) 
{
    while ($k++%3 != 0 ) echo '<td> </td>';
    echo '</tr>';
}

echo '</table>';
?>

Wildbug · June 28, 2007

I love GROUP_CONCAT(); I can't believe they didn't add it until 4.1. One change to Barand's code, however, since you want linked subcats:

GROUP_CONCAT(DISTINCT '<a href="recipes.php?category2=',category2,'" class="subcategories">',category2,'</a>' ORDER BY category2 SEPARATOR ', ')

Barand · June 28, 2007

Double quotes need to be escaped

GROUP_CONCAT(DISTINCT '<a href=\"recipes.php?category2=',category2,'\" class=\"subcategories\">',category2,'</a>' ORDER BY category2 SEPARATOR ', ') as subcats

adamjblakey · June 29, 2007

Great work there, its now works fine.

Thanks a lot.

adamjblakey · June 29, 2007

Sorry just one other point it seems that it is linking all the sub categories as one link which i need it to be seperate as they each need to link to another page. Any ideas?

This is the new code.

     <?php

		$sql = "SELECT category, GROUP_CONCAT(DISTINCT category2 ORDER BY category2 SEPARATOR ', ') as subcats FROM recipes GROUP BY category" ;
			$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
			$k=0;
			echo '<table width=690 border=0 cellspacing=3 cellpadding=6>';
				while (list($cat, $subs) = mysql_fetch_row($res))
				{
					if ($k % 3 == 0) echo '<tr>';
					echo "<td bgcolor='#E8F3BE' valign='top'><a href='recipes.php?category=$cat' class='categories'> <h1>$cat</h1></a><a href='recipes.php?category2=$subs' class='subcategories'>$subs</a></td>";
					$k++;
					if ($k % 3 == 0) echo '</tr>';
				}

				if ($k % 3 != 0) 
				{
					while ($k++%3 != 0 ) echo '<td> </td>';
					echo '</tr>';
				}

			echo '</table>';
	?>

Barand · June 29, 2007

You didn't add Wildbug's addition to my GROUP_CONCAT.

Try

<?php
$sql = "SELECT category, 
        GROUP_CONCAT(DISTINCT '<a href=\"recipes.php?category2=',category2,'\" class=\"subcategories\">',category2,'</a>' ORDER BY category2 SEPARATOR ', ') as subcats 
        FROM cats 
        GROUP BY category" ;
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
$k=0;
echo '<table width=690 border=0 cellspacing=3 cellpadding=6>';
	while (list($cat, $subs) = mysql_fetch_row($res))
	{
		if ($k % 3 == 0) echo '<tr>';
		echo "<td bgcolor='#E8F3BE' valign='top'><a href='recipes.php?category=$cat' class='categories'> <h1>$cat</h1></a>$subs</td>";
		$k++;
		if ($k % 3 == 0) echo '</tr>';
	}

	if ($k % 3 != 0) 
	{
		while ($k++%3 != 0 ) echo '<td> </td>';
		echo '</tr>';
	}

echo '</table>';
?>

adamjblakey · June 29, 2007

Whoops, DOH!

Just getting a little problem now with the way they are displaying

This is the new code:

<?php

		$sql = "SELECT category, GROUP_CONCAT(DISTINCT '<a href=\"recipes.php?category2=',category2,'\" class=\"subcategories\">',category2,'</a>' ORDER BY category2 SEPARATOR ', ') as subcats FROM recipes GROUP BY category" ;
			$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
			$k=0;
			echo '<table width=690 border=0 cellspacing=3 cellpadding=6>';
				while (list($cat, $subs) = mysql_fetch_row($res))
				{
					if ($k % 3 == 0) echo '<tr>';
					echo "<td bgcolor='#E8F3BE' valign='top'><a href='recipes.php?category=$cat' class='categories'> <h1>$cat</h1></a>$subs</td>";
					$k++;
					if ($k % 3 == 0) echo '</tr>';
				}

				if ($k % 3 != 0) 
				{
					while ($k++%3 != 0 ) echo '<td> </td>';
					echo '</tr>';
				}

			echo '</table>';
	?>

Take a look here: foundrecipes.com

Thanks again

Barand · June 29, 2007

SELECT category, GROUP_CONCAT(DISTINCT '',category2,'' ORDER BY category2 SEPARATOR ', ') as subcats FROM cats GROUP BY category

The query displayed on your site ^ is referring to table "cats" which is the name I used in my test db. Change to "recipes"

Also you have put the anchor/href tags back around "$subs".

$subs is already a list of individual links when you pull it from the query

adamjblakey · June 29, 2007

Thank you i have made those changes but still having problems.

If you look here foundrecipes.com there seems to be a problem when outputting the date. Before they were in 3 columns and displayed fine but now they are in odd columns.

This is the code at present.

   <?php

		$sql = "SELECT category, GROUP_CONCAT(DISTINCT '<a href=\"recipes.php?category2=',category2,'\" class=\"subcategories\">',category2,'</a>' ORDER BY category2 SEPARATOR ', ') as subcats FROM recipes GROUP BY category" ;
			$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
			$k=0;
			echo '<table width=690 border=0 cellspacing=3 cellpadding=6>';
				while (list($cat, $subs) = mysql_fetch_row($res))
				{
					if ($k % 3 == 0) echo '<tr>';
					echo "<td bgcolor='#E8F3BE' valign='top'><a href='recipes.php?category=$cat' class='categories'> <h1>$cat</h1></a>$subs</td>";
					$k++;
					if ($k % 3 == 0) echo '</tr>';
				}

				if ($k % 3 != 0) 
				{
					while ($k++%3 != 0 ) echo '<td> </td>';
					echo '</tr>';
				}

			echo '</table>';
	?>

Barand · June 29, 2007

Have you got any html tags inside the data in the db?

adamjblakey · June 30, 2007

I have in the description field but this is not printed anywhere in that section.

Barand · June 30, 2007

You could try an alternative method using floating divs

<?php
$k=0;
while (list($cat, $subs) = mysql_fetch_row($res))
{

    echo "<div style='float: left; padding:4px; width: 33%; '>
    <a href='recipes.php?category=$cat' class='categories'><h1>$cat</h1></a>
    $subs
    </div>";

    $k++;
    if ($k % 3 == 0) echo "<br style='clear:both' />\n";
}

adamjblakey · July 4, 2007

Thank you Barand that works a treat now.

I just have one little problem then this site is complete thank goodness.

My problem is that if you go here: foundrecipes.com and click on a sub category you will notice that i have included mod rewrite rules to make the site seo friendly.

The problem is that all the other url's have separated by a - but i cannot do this with the sub categories as the link is generated in the sql so i cannot include a function to remove spaces and add - in place.

Any ideas.

This is the current code:

<?php
$strippagename = str_replace("-", " ",$_GET['title']);
		$sql = "SELECT category, GROUP_CONCAT(DISTINCT '<a href=\"/recipessubcat/',category2,'.html\" class=\"subcategories\">',category2,'</a>' ORDER BY category2 SEPARATOR ', ') as subcats FROM recipes GROUP BY category" ;
			$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
			$k=0;
while (list($cat, $subs) = mysql_fetch_row($res))
{

   $cat_url = strtr($cat, "éèêàëâúóíáABCDEFGHIJKLMNOPQRSTUVWXYZ. ","eeeaeauoiaabcdefghijklmnopqrstuvwxyz--");
$cat_url = ereg_replace('[^a-zA-Z0-9_-]', '', $cat_url);
    echo "<div style='float: left; padding:6px; width: 30%; '>
    <a href='recipescat/$cat_url.html' class='categories'><h1>$cat</h1></a>$subs</div>";

    $k++;
    if ($k % 3 == 0) echo "<br style='clear:both' />\n";

	}			
?>

Cheers,

Adam

Wildbug · July 4, 2007

The problem is that all the other url's have separated by a - but i cannot do this with the sub categories as the link is generated in the sql so i cannot include a function to remove spaces and add - in place.

In SQL, ...REPLACE(column,' ','-')...

adamjblakey · July 5, 2007

Thank you for your reply, where abouts would i put this into my current sql statement as i have tried a few places which give me errors.

Wildbug · July 5, 2007

GROUP_CONCAT(
DISTINCT '<a href=\"/recipessubcat/',
REPLACE(category2,' ','-'),'.html\" class=\"subcategories\">',category2,'</a>' 
ORDER BY category2
SEPARATOR ', ') as subcats

adamjblakey · July 5, 2007

Works a treat, thanks a lot

Sign In

[SOLVED] Displaying Categories and Sub Categories?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information