group and show results from mysql

[CodilX]

Hi,

I can't get this to work..

I have a db, like this:

id ¦ value ¦ category
----------------------
 1 ¦ value1 ¦ category1
 2 ¦ value2 ¦ category1
 3 ¦ value3 ¦ category2
 4 ¦ value4 ¦ category3

and I want the output to be like this:

Category1:
value1
value2

Category2:
value3

Category3:
value4

of course I can use WHERE category = category1 .. etc, but how do I make the script automatically group all values and show them this way?

[mooger35]

You'd have to do that after pulling the records from the database.

//query
$sql = mysql_query("SELECT * FROM db ORDER BY category ASC");

$cat = ""; //initialize $cat variable
while($row = mysql_fetch_assoc($sql)){
if($row['category'] != $cat) echo "<h3>".$row['category']."</h3>\r\n";
echo "<p>".$row['value']."</p>\r\n";
$cat = $row['category'];
}

[LifeinAsia]

SELECT value, category
FROM YourTable
ORDER BY category, value

I don't know about PHP, but other scripting languages allow you to loop within the query results to easily display what you want. Otherwise, you'll have to write your own outputting- basically checking to see if the current category is the same as the previous category.

[CodilX]

mooger35: works charms, thank you so much

[mooger35]

You're welcome.

[CodilX]

oh and a follow up..

how can I just show the list of categories?

[mooger35]

If all you want is the categories:

$sql = mysql_query("SELECT DISTINCT(category) FROM db ORDER BY category ASC");

while($row = mysql_fetch_assoc($sql)){
echo "<p>".$row['category']."</p>\r\n";
}