How to group mysql queries in PHP?

varelse

I'm in the process of converting a coldfusion application I wrote awhile ago into PHP. I've hit a wall trying to figure out how to group queries similar to coldfusions CFOUTPUT GROUP attribute (in case you're familiar with it).

Here is what I'm trying to do...
I have a query that outputs something similar to this

I need to group this by the ID and Title so it will basically return a structure like this

1 myTitle
--- myFile_800.jpg | 800x600
--- myFile_1024.jpg | 1024x768
--- myFile_1280.jpg | 1280x1024

2 myTitle
--- myFile_800 ... and so on

in coldfusion I could output this by using
<cfoutput query="myQuery" group="IMAGE_ID">
myID, myTitle
<cfoutput>
myFile, myImageSize
</cfoutput>
</cfoutput>

if anyone is familiar with how to do this in PHP could you please help? I thought of a very tedious way by looping through the results and setting multi-dimesnional arrays one after the other using some logic to determine how to group them but it just seems very messy, time-consuming, and I actually haven't gotten it to work just right yet (some of the arrays aren't getting set right)... I'm assuming there has to be an easier way to do this and it's just my inexperience with PHP that is the reason for the headache, if anyone can help point me in the right direction I would really appreciate it.

mrhappiness

$r = mysql_query('your select-statement');
$helper = -9999; //or any other value that is unlikely to be present in the entries you get
while($row = mysql_fetch_assoc($r))
{
  if ($row['IMAGE_ID'] != $helper)
  {
    echo $row['IMAGE_ID'].' '.$row['IMAGE_TITLE'];
    $helper = $row['IMAGE_ID'];
  }
  echo '<br />--- '.$row['IMGPATH'].' '.$row['IMGSIZE'];
}

something like that?

varelse

yup this is pretty close to what I had, I wasn't exactly just trying to output it like this though... I finally did get it to work, it took me awhile and it takes up about 26 lines of code what would normally take up about 10-15 in coldfusion, was just hoping there was an easier way. Though your post was helpful in sparking an idea in my head as to why what I had wasn't working... Thanks for the help.

Here's the final code I came up with, it could probably be slightly more condensed for someone more fluent in PHP than I, but I might as well post in case others are wondering.

$myIndexIMG = array();
$myHelper = "LOBLAOHF12874hjhiIAFh";
$temp = 0;

for ($i = 0; $i < $qryGetIndexImage_recordcount; $i++)
{
	$id = mysql_result($qryGetIndexImage, $i, 'indexImage_id');
	$title = mysql_result($qryGetIndexImage, $i, 'indexImage_title');
	if ($myHelper <> $id) {
		$myIndexIMG[$temp] = array();
		$myIndexIMG[$temp]['id'] = $id;
		$myIndexIMG[$temp]['title'] = $title;
		$myIndexIMG[$temp]['file'] = array();
		$myHelper = $id;
		$y = 0;
		$group = $temp;
		$temp++;
	}
	$myIndexIMG[$group]['file'][$y] = array();
	$myIndexIMG[$group]['file'][$y]['path'] = mysql_result($qryGetIndexImage, $i, 'lkpIndexImage_path');
	$myIndexIMG[$group]['file'][$y]['size'] = mysql_result($qryGetIndexImage, $i, 'lkpimagesize');
	$myIndexIMG[$group]['hiResPath'] = mysql_result($qryGetIndexImage, $i, 'lkpIndexImage_path');
	$dwnldImageCount = $y;
	$y++;
}