Help needed with sorting by category

[malcontent]

I need to pull information from 2 tables - one is category (id, name, description), the other holds the data (id, catid, info, etc.)
I need the query when run to show each category (if exists) and the items below that category. I only want the category name to show up once as a header for each section. I'm sure it must need some sort of loop or groupby, but I am totally lost here.

any guidance would be appreciated.

[bradgrafelman]

What kind of database? MySQL? I've moved this thread to the Database forum, but you might want to clarify which DB engine you're working with here.

Here's my guess as to what you'd want to do:

$query = 'SELECT cat.id, cat.name, cat.description, GROUP_CONCAT(data.id, \'|<|\', data.info ORDER BY data.id SEPARATOR \'|>|\') AS data_array FROM category cat, data_table data WHERE data.catid = cat.id GROUP BY cat.id';
$exec = mysql_query($query) or die('MySQL error: ' . mysql_error());

while($result = mysql_fetch_assoc($exec)) {

echo 'Category #' . $result['id'] . ' - "' . $result['name'] . '": ' . $result['description'] . "\n";

$data_array = explode('|>|', $result['data_array']);
foreach($data_array as $data) {

	list($data_id, $data_info) = explode('|<|', $data);
	echo ' --- Data ID#' . $data_id . ': ' . $data_info . "\n";

}

}

The above sample code would print something like:

Category #1 - "Cat #1": This is a test of my query. This is the first category.
 --- Data ID#1: data #1 of cat#1
 --- Data ID#2: second data of cat#1
 --- Data ID#5: and this is it for #1
Category #2 - "Cat #2": This is the second category... again, testing...
 --- Data ID#3: um first data of second cat
 --- Data ID#4: 2nd set of data for second cat
 --- Data ID#6: and finally for cat#2

NOTE: You'll have to of course alter the table names to suit your purpose, and you may even need to add fields. Hopefully you can look at my code and see how I isolated fields. If not, post back and I'll explain it to you.

NOTE: I used two sets of separators, |>| and |<| to isolate data. Now, if these strings actually appear in the data you're trying to retrieve... you'll most likely run into problems. So, if you find a separator that you like, you might want to filter what's entered into the DB and make sure these strings don't appear in the data.

[malcontent]

First, I'd like to thank you for your help! This is the error I am getting:
Parse error: syntax error, unexpected '<'

Yes, it is a mysql database. My code with the actual table names,etc.

<?php
include("conn.php");
$checked = $_POST['checkbox'];
$in = implode(",", $checked);

//$query = "select * from protocol where id in ($in)"; - my old query
$query = 'SELECT category.id, category.description, category.explain, GROUP_CONCAT(protocol.id, \'|<|\', protocol.name where protocol.id in ($in) ORDER BY protocol.id SEPARATOR \'|>|\') AS data_array FROM category cat, data_table data WHERE protocol.cat = category.id GROUP BY category.id';
$exec = mysql_query($query) or die('MySQL error: ' . mysql_error());

while($result = mysql_fetch_assoc($exec)) {

echo 'Category #' . $result['id'] . ' - "' . $result['description'] . '": ' . $result['explain'] . "\n";

$data_array = explode('|>|', $result['data_array']);
foreach($data_array as $data) {

    list($data_id, $data_info) = explode('|<|', $data);
    echo ' --- Data ID#' . $protocol_id . ': ' . $protocol_info . "\n";

}

}

It is pulling the query information from an array generated on the prior page.