Grouping rows by category

[DrFishNips]

I've never figured out how to do this. Lets say I have a database with fruits and vegetables in it. I want to display the contents of the DB but I need it to display them according to their category right now it just lumps them into the same category. For example I want to display the results like this:

 

Fruits:

Apples

Oranges

Mangos

 

Vegetables:

Carrots

Onions

Broccoli

 

right now I can only get it to display results like

Fruits

Apples

Oranges

Mangos

Carrots

Onions

Broccoli

 

So my problem is that all these entries are in the same DB table but I need to display them under different categories but I have no idea how. I've tried long and hard but can't figure it out so its time to get some help. Can anyone help me out with this?

 

Heres the code I'm using

$query = "SELECT * FROM food";

$result = mysql_query($query);



$num=mysql_num_rows($result);



$i=0;

while ($i < $num) {



$food = mysql_result($result,$i,"food");

$info = mysql_result($result,$i,"info");

$cat = mysql_result($result,$i,"cat");


echo "<h4>$food</h4>";
echo "<p>$info</p>";
++$i;



}

[kickstart]

Hi

 

Think you want something like this (with pretty minimal changes). If so really a php issue rather than a MySQL issue.

 

<?php
$query = "SELECT * FROM food ORDER BY cat";
$result = mysql_query($query);
$num=mysql_num_rows($result);
$i=0;
$prevCat = "";

while ($i < $num) 
{
$food = mysql_result($result,$i,"food");
$info = mysql_result($result,$i,"info");
$cat = mysql_result($result,$i,"cat");
if ($prevCat != $cat)
{
	echo "<b>$cat</b>";
	$prevCat = $cat;
}
echo "<h4>$food</h4>";
echo "<p>$info</p>";
++$i;
}
?>

 

Using more conventional MySQL reads of the database:-

 

<?php
$query = "SELECT * FROM food ORDER BY cat";
$result = mysql_query($query);
$prevCat = "";

while ($row = mysql_fetch_assoc($result))
{
$food = $row["food"];
$info = $row["info"];
$cat = $row["cat"];
if ($prevCat != $cat)
{
	echo "<b>$cat</b>";
	$prevCat = $cat;
}
echo "<h4>$food</h4>";
echo "<p>$info</p>";
}
?>

 

All the best

 

Keith