[RESOLVED] Group By Mysql

[LiamBailey]

Hi Guys,

When I use GROUP BY in a mysql query, how can I access the results by category, as when I do it, say group by category, and then do a while loop into an associative array it prints only one result from each category?

I have tried:

$query = "SELECT * FROM wap_content GROUP BY Category ORDER BY id DESC";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
echo $row[Article][CategoryName];
}

And with it the other way round as well as echo $CategoryName[$row][Article] and none of it works so I though a little help may be appropriate. Thanks in advance.

[bpat1434]

I think you misunderstand what GROUP BY really does. the GROUP BY clause will actually look at all the rows and based upon the column(s) set in the GROUP BY clause, it will merge those rows together as one single row.

So for example, if you wanted to know how many rows of each category you had, you could execute something like:

SELECT Category, COUNT(*) AS `num` FROM wap_content GROUP BY Category

You probably wanted to ORDER BY Category, id ASC so that the entire result set would be ordered by Category (thus "grouping" them together in one spot) and then ordering them by ID within each category.

[Kudose]

As a side note, you should be using quotation marks for your array subscripts.