Grouping data on query?

[darthmahon]

Hi,

Ok I have a table that contains all of my content for a particular section, each item added to this table has a category name assigned to it so I know what it actually belongs to.

So I want to pull out everything of categoryX and then everything of categoryY.

Now, this is my query:

$query = "
SELECT tbl_content.fld_ID,
TRIM(tbl_content.fld_Title) as fld_Title,
tbl_content.fld_Category
FROM tbl_content
WHERE 
(
(tbl_content.fld_Category like 'newsletter') or
(tbl_content.fld_Category like 'publication') 
)
ORDER BY tbl_content.fld_ID DESC
";

So as usual I am using a while loop to iterate through my results and output them. Problem is, it orders them by the fld_ID field which is fair enough, I told it to. But I want it to list all newsletters items, then all publication items...

Now, to complicate it further - I need a "Newsletters" heading and a "Publications" heading above each relavant section. So I can't do it like this:

<?

// iterate through resultset
while($row = mysql_fetch_object($result))
{

if ($row->fld_Category == "newsletter"){

?>
            <h1>Newsletters</h1>
	<p>Item Name</p>

<? } else if ($row->fld_Category == "publication") { ?>

            <h1>Publications</h1>
	<p>Item Name</p>

<?

}

}

?>

Any ideas on how I seperate this data without using two queries? That seems the simplest thing to me, but there must be a better way to do this.

Cheers,
Chris

[mrhappiness]

outside your while have a var called $old_heading with the value "kjbcwsf6r8rf234" (or anything else which won't appear as real heading)

inside your while compare $old_heading with $row->fld_Category
if they do not match output your heading and do

$old_heading = $row->fld_Category;

[darthmahon]

Hey,

Ok got it working. For future reference on those searching:

<?
// generate and execute query

$query = "
SELECT tbl_content.fld_ID,
TRIM(tbl_content.fld_Title) as fld_Title,
DATE_FORMAT(tbl_content.fld_Date_Start, '%D %b %Y') As fld_Date_Start,
TRIM(tbl_content.fld_Description) as fld_Description,
TRIM(tbl_content.fld_URL) as fld_URL,
TRIM(tbl_content.fld_URL_Text) as fld_URL_Text,
tbl_content.fld_Category,
tbl_content.fld_URL_Text,
tbl_content.fld_URL
FROM tbl_content
WHERE
(
(tbl_content.fld_Category = 'newsletter') or
(tbl_content.fld_Category = 'publication')
)
ORDER BY tbl_content.fld_Category,tbl_content.fld_ID DESC
";

$result = mysql_query($query) or die ("Error in query: $query. " .mysql_error());

// if records present
if (mysql_num_rows($result) > 0)
{

// for category headers
$processed = null;

// iterate through resultset
while($row = mysql_fetch_object($result))
{

	if ($row->fld_Category == "newsletter") {

		if ($row->fld_Category != $processed) {

		   echo "<h1>Newsletters</h1>";
		   $processed = $row->fld_Category;

		}

	?>

		<p><a href="detail.php?ItemID=<? echo $row->fld_ID; ?>"><? echo stripslashes($row->fld_Title); ?></a> 
		- [<? echo $row->fld_Date_Start; ?>] - <a href="detail.php?ItemID=<? echo $row->fld_ID; ?>">Full Details</a></p>

	<? } else if ($row->fld_Category == "publication") {

		if ($row->fld_Category != $processed) {

			echo "<h1>Publications</h1>";
			$processed = $row->fld_Category;

		}

	?>

		<p><a href="detail.php?ItemID=<? echo $row->fld_ID; ?>"><? echo stripslashes($row->fld_Title); ?></a> 
		- [<? echo $row->fld_Date_Start; ?>] - <a href="detail.php?ItemID=<? echo $row->fld_ID; ?>">Full Details</a><br>
		<? echo stripslashes(nl2br($row->fld_Description)); ?></p>

	<?

	}

}

// nothing found
} else {
?>

<p>Sorry, we have no current publication listings at the moment, please try again at a later date.</p>

<?
}

// close database connection
mysql_close($connection);

?>