Grouping database output

ali_p

Hi,

I currently have data coming out of my sql table looking like this:

1 2
Management Development 2nd November 2006
Management Development 3rd November 2006
Management Development 4th November 2006
Team Development 8th November
etc 20th November

1 being one field and 2 being another .
I would like this information to look like this

Management Development 4th November 2006, 3rd November 2006,4th November 2006
Team Development 8th November
etc 20th November

So that where the programme_id is the same the date fields are grouped.
I'm currently using a regular while loop to display the results.

Any suggestions anyone.

Here is my code:


//list the staff here
$color1 = "#EFF9FB"; 
$color2 = "#CEEFF6"; 
$row_count = 0; 

$sql="
SELECT entry_id,programme_name,DATE_FORMAT(programme_date,'%D %M %Y') as 'datey', prog_name,prog_id
FROM calendar,programmes
WHERE calendar.programme_month = '$today'
AND calendar.programme_name = programmes.prog_name
ORDER BY programme_date ASC
";
$result = mysql_query($sql, $conn)or die(mysql_error());
while ($row =mysql_fetch_array($result)){
	$entry_id = $row['entry_id'];
	$programme_name = $row['programme_name'];
	$programme_date = $row['datey'];
	$prog_id=$row['prog_id'];



/* Now we do this small line which is basically going to tell 
PHP to alternate the colors between the two colors we defined above. */

$row_color = ($row_count % 2) ? $color1 : $color2;

// Echo your table row and table data that you want to be looped over and over here.

$display_block.= "<tr bgcolor=\"$row_color\" height=\"20\">
<td>
<a href=\"programmelist.php?prog_id=$prog_id\">$programme_name</a></td><td valign=\"top\" background=\"blu_darker.gif\" class=\"maintext1\">$programme_date</td>
</tr>";

// Add 1 to the row count

$row_count++;
}

Thanks in advance

sneakyimp

I would alter your query a bit:

$sql="
SELECT entry_id,programme_name,DATE_FORMAT(programme_date,'%D %M %Y') as 'datey', prog_name,prog_id
FROM calendar,programmes
WHERE calendar.programme_month = '$today'
AND calendar.programme_name = programmes.prog_name
ORDER BY prog_id ASC, programme_date ASC
";

then i would loop through the results, only creating a new output string every time the entry_id changes....something like

$prev_id = '';
$data_ary = array();
$i = -1;
while($row = mysql_fetch_array($result) {
  if ($row['prog_id'] != $prev_id) {
    $i++;
    $data_ary[$i] = array();
    $data_ary[$i]['id'] = $row['prog_id'];
    $data_ary[$i]['name'] = $row['programme_name'];
    $data_ary[$i]['dates'] = array();
  }

  $data_ary[$i]['dates'][] = $row['datey'];
  $prev_id = $row['prog_id'];
}

then you can loop thru the array you've built and output stuff like this:

foreach($data_ary as $key=>$row) {
  echo 'id:' . $row['id'] . '<br>';
  echo 'name:' . $row['name'] . '<br>';
  echo 'dates:' . implode(',', $row['dates']);
}

or something like that...i haven't tested it but you should get the idea.

EDIT: I had to edit this a few times for typos...