CODEWITHSUNDEEP
phpmysql
user2218739
user2218739

Reputation: 5

php count grouped values

I have searched around and have found many solutions to my question, but I am still having one problem. I am trying to create a table that will have four images per row. This has been done. I have told the script to group by rewardid and it does that so now duplicate images do not appear (the steps to do these I have found on the site). What I am having problems with is that I m trying to put a multiplier under each image to count how many was in each group. (i.e. Lets say I have 5 medals. On my page, it only shows one medal, but I really have 5, so I would like for it to say x5 below the image.) This is what I have so far:

print "<div class='style1'><br>  <br><h2>User Medals</div></h3><br />
<table width='80%' class='table' border='0' cellspacing='1'><tr bgcolor='gray'><th></th><th></th><th></th><th></th></tr>";
    $result=mysql_query("SELECT * FROM awardsearned WHERE userid=$userid group by rewardid");
    $count = 0;
    while($res=mysql_fetch_array($result))
    {
    $award = $res['rewardpic'];
    $award2 = $res['rewardid'];
    $result2=mysql_query("SELECT awardid, count(rewardid) FROM awardsearned WHERE rewardid=$award2 AND userid=$userid");
            $count2 = count($result2); 
        if($count==4) //three images per row
        {
           print "</tr>";
           $count = 0;
        }
        if($count==0)
           print "<tr>";
        print "<td>";

        print "<center><img src='$award' width='100' height='100'/><br />x$count2</center> ";

        $count++;
        print "</td>";
    }
    if($count>0)
       print "</tr>";
print "
</table>";

Sorry if this is messed up, never posted here before. Here it is on pastebin if needed http://pastebin.com/iAyuAAzV

Upvotes: 0

Views: 110

Answers (2)

bestprogrammerintheworld
bestprogrammerintheworld

Reputation: 5520

To answer your question:

The value of $result2 would only give TRUE(1) or FALSE(0). If your query executed correctly $count2 will return 1, therefore you always get 1 in your code.

Try and change

$result2=mysql_query("SELECT awardid, count(rewardid) FROM awardsearned WHERE rewardid=$award2 AND userid=$userid");
$count2 = count($result2);

TO:

$result = mysql_query("SELECT awardid, count(rewardid) FROM awardsearned WHERE rewardid=$award2 AND userid=$userid");
$arr = mysql_fetch_array($result);
$count2 = count($arr);

This should give you the actual numbes of records from the resultset.

There are better ways of doing this (look at Bart Friederichs answer!) , like doing only SELECT and loop through resultset once. This would be for performance and for flexibility.

Also take in my consideration that mysql_query is deprecated now, and should not be used. (The preferal methods are to use PDO or mysqli instead)

Upvotes: 0

Bart Friederichs
Bart Friederichs

Reputation: 33511

Update your query like this:

SELECT *,COUNT(rewardid) no_of_rewards 
     FROM awardsearned WHERE userid=$userid group by rewardid

$res['no_of_rewards'] will hold your number.

This will also eliminate the need for the second query.

These things are called "aggregate functions". More in the documentation.

Upvotes: 3

Related Questions