CODEWITHSUNDEEP
phpmysqlsql
charlie
charlie

Reputation: 1384

List mysql data grouped together and show num_rows for each group

i have this SQL Code that select rows from a table and groups them together by the category column

echo '<strong>Categories</strong><br>';
    $sql="SELECT * from tickets where deleted = '' and DAY(datetime) = '04' and MONTH(datetime) = '".date("m")."' and YEAR(datetime) = '".date("Y")."' group by category order by datetime ASC ";
    $rs=mysql_query($sql,$conn);
    while($result=mysql_fetch_array($rs))
    {
        echo $result["category"].' ('.mysql_num_rows($rs).')<br>';
    }

when displaying the number of rows, its showing the same number on each one.

how can i make this display the correct number next to each 'category' ?

Upvotes: 0

Views: 296

Answers (2)

ChrisGuest
ChrisGuest

Reputation: 3608

You probably want to do a query like this where you are using the COUNT function to tell you how many tickets are in each category grouping. 

SELECT  category, COUNT(id) AS NumTickets, MIN(datetime) AS FirstDateTime
FROM tickets 
WHERE deleted = '' AND DAY(datetime) = 4 AND
        MONTH(datetime) = 12 AND YEAR(datetime) = '2012' 
GROUP BY category 
ORDER BY MIN(datetime)

Upvotes: 0

djot
djot

Reputation: 2947

SELECT *, COUNT(category) AS my_groupcount from tickets

echo $result["category"].' ('.$result["my_groupcount"].')<br>';

Upvotes: 1

Related Questions