Displaying Categories?

Hi,

I have a database where the table is set out like this:

id | title | category

There is about 1000 records in there and i want to be able to create a list of the categories but if i do a foreach statement i just get the same categories over and over.

How can i do this so that if the category is the same as another then it will only display the one and not multiple of the same category.

Cheers,
Adam

 

don't do this in php, use a mysql query. The next queries will both return the different categories (each category only 1 time)

SELECT DISTINCT ( category ) FROM mytable

Code (markup):

or:

SELECT category FROM mytable GROUP BY category

Code (markup):

 

I can't believe i did not know about that sql statement.

I guess you really do learn something new everyday.

Thanks a lot

 

Sorry to be a pain but just have one other question. I also have next to category a subcategory field. How would i go about listing the subcategories under the main category?

I tried doing this:

$select = "SELECT subcategory FROM recipes WHERE category = '$row[category]' GROUP BY subcategory";

Code (markup):

But does not output anything.

Any ideas?
Cheers,
Adam

 

Can you show recipes table structure?

 

try this:

$select = "SELECT subcategory FROM recipes WHERE category = '" . $row['category'] . "' GROUP BY subcategory";

PHP:

or use the DISTINCT query

 

Still does not work

I now get this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource on line 66

Here is all the code. It is setup so that each category is added to a new colum so there is 3 colums going down with a category in each. Now i want the sub categories under the main category.

<table width="690" border="0" cellspacing="0" cellpadding="0">
						<?php
												
					$selects = "SELECT category FROM recipes GROUP BY category";
					$result = mysql_query($selects);

					while ($res = mysql_fetch_array($result)) {
						$resu[] = $res;	
					}
					mysql_free_result($result);
					
						$item=0;	
						foreach ($resu AS $row) {

							if($item==0){print "\t<tr><td><tr>\n";}
                  ?>

  <td bgcolor="#E8F3BE"><table width='220' border='0' cellspacing='0' cellpadding='0'>
    
    <tr>
      <td valign="top"><table width='100%' border='0' cellspacing='0' cellpadding='8'>
        <tr>
          <td ><h1> <a href="recipe-details.php?id=<?php echo"".$row['id'].""; ?>"><?php echo"<span class='categories'>".$row['category']."</span>"; ?></a></h1> 			<?php
												
					$selectc = "SELECT subcategory FROM recipes WHERE category = '" . $row['category'] . "' GROUP BY subcategory";
					$resultc = mysql_query($selectc);

					while ($resc = mysql_fetch_array($resultc)) {
						$resuc[] = $resc;	
					}
					mysql_free_result($resultc);
	
						foreach ($resuc AS $rowc) {

                  ?>
				  <a href="recipe-details.php?id=<?php echo"".$rowc['id'].""; ?>"><?php echo"<span class='categories'>".$rowc['category2']."</span>"; ?></a>
				  <?php
				  }
				  ?>
				  </td>
        </tr>
      </table></td>
    </tr>
  </table></td>
      <?php
				$item++;
				if($item==3){$item=0;}
				if($item==0){print "\t</tr>\n";}
					}
				?>
                </table>

Code (markup):

Cheers,
Adam

 

Why the "group by"? Doesn't seem to make sense there. Shouldn't you be grouping by the parent, if anything?

By the way, your script uses far too many queries. I'd use the one:

$selects = "SELECT category, subcategory FROM recipes ORDER BY category ASC, subcategory ASC";

Code (markup):

Then, when looping, add a check (if category != last category, start a new category heading), otherwise just show the subcategory. The "last category" would be a variable set at the end of each loop.

 

I know i was going about it the wrong way :S

So know it looks like this but am unsure on where the if statement fits in?

<table width="690" border="0" cellspacing="0" cellpadding="0">
						<?php
												
					$selects = "SELECT category, category2 FROM recipes ORDER BY category ASC, category2 ASC";
					$result = mysql_query($selects);

					while ($res = mysql_fetch_array($result)) {
						$resu[] = $res;	
					}
					mysql_free_result($result);
					
						$item=0;	
						foreach ($resu AS $row) {

							if($item==0){print "\t<tr><td><tr>\n";}
                  ?>

  <td bgcolor="#E8F3BE"><table width='220' border='0' cellspacing='0' cellpadding='0'>
    
    <tr>
      <td valign="top"><table width='100%' border='0' cellspacing='0' cellpadding='8'>
        <tr>
          <td >
		
		  <h1> <a href="recipe-details.php?id=<?php echo"".$row['id'].""; ?>"><?php echo"<span class='categories'>".$row['category']."</span>"; ?></a></h1> 			
				  </td>
        </tr>
      </table></td>
    </tr>
  </table></td>
      <?php
				$item++;
				if($item==3){$item=0;}
				if($item==0){print "\t</tr>\n";}
					}
				?>
                </table>

Code (markup):

Cheers,
Adam

 

Let's start over with that section

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

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

    if ($row['category'] != $last_category) {
?>
    <td bgcolor="#E8F3BE">
      <h1><?php echo $row['category']; ?></h1>
      <p>
<?php
    }
?>
      <a href="recipe-details.php?id=<?php echo"".$row['id'].""; ?>">
        <?php echo"<span class='categories'>".$row['subcategory']."</span>"; ?>
      </a>
<?php
    if ($row['category'] != $last_category) {
?>
      </p>
    </td>
<?php
        $item++;
    }

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

Code (markup):

I don't intersperse PHP/HTML often so that could look cleaner. Anyway, it should work and should be easy to customise to how you want it.

 

I know sorry about this, i think some people ie me was not meant for coding i.e me.

Right i have tried what you suggested but still have problems is it just repeating the categories over and over again. This is the code now with your code i have added.

<table width="690" border="0" cellspacing="0" cellpadding="0">
						<?php
												
					$selects = "SELECT category, category2 FROM recipes ORDER BY category ASC, category2 ASC";
					$result = mysql_query($selects);

					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">
      <h1><?php echo $row['category']; ?></h1>
      <p>
<?php
    }
?>
      <a href="recipe-details.php?id=<?php echo"".$row['id'].""; ?>">
        <?php echo"<span class='categories'>".$row['category2']."</span>"; ?>
      </a>
<?php
    if ($row['category'] != $last_category) {
?>
      </p>
    </td>
<?php
        $item++;
    }

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

Code (markup):

 

Anyone help me on this, almost there

 

change the query to this:

"SELECT category, DISTINCT(category2) FROM recipes ORDER BY category ASC, category2 ASC"

Code (markup):

 

Just tried that but getting the following error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/red/public_html/index.php on line 44

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/red/public_html/index.php on line 47

Warning: Invalid argument supplied for foreach() in /home/red/public_html/index.php on line 51

Code (markup):

 

replace this:

$result = mysql_query($selects);

PHP:

by this:

$result = mysql_query($selects) or die ( mysql_error ( ) );

PHP:

the errors you got mean that there's an error when you try to run the query in mysql_query. mysql_error returns the error that occured when running the query. If you're debugging your scripts, it's best to add the "or die ( mysql_error ( ) )" to every query you run

 

Thank you for your reply and sorry to bother you further but i am getting another error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(category2) FROM recipes ORDER BY category ASC, category2 ASC' at line 1

 

Almost there? Any ideas?

 

lol: i tested it on my server, and the only thing you need to change is switch the order of fields. not:

category, DISTINCT(category2)

Code (markup):

bu:

DISTINCT(category2), category

Code (markup):

 

Thank you for your further reply UnrealEd

It just does not want to work for me though.

This is the code that i am using:

<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">
      <h1><?php echo $row['category']; ?></h1>
      <p>
<?php
    }
?>
      <a href="recipe-details.php?id=<?php echo"".$row['id'].""; ?>">
        <?php echo"<span class='categories'>".$row['category2']."</span>"; ?>
      </a>
<?php
    if ($row['category'] != $last_category) {
?>
      </p>
    </td>
<?php
        $item++;
    }

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

Code (markup):

But what it is doing now is just listing the cats and sub cats all over and not putting the main cats as a heading and listing the cat2 under the main heading it is associated with.

Have a look here: foundrecipes.com

Cheers for your help so far.

 

Would it be easier if i uploaded a snipet of the database and the page at all?