Sorting and count the value

Question

Exactly i want to sorting the "refferals" table below:

     Referral            | IP
     X                   |x.x.x.x
     X                   |x.x.x.x
     X                   |x.x.x.x
     nazmi               |x.x.x.x
     nazmi               |x.x.x.x
     Y                   |x.x.x.x

And i am going to return this to a table like this sorting ascending :

No    | username   | Refferals
1     | X          | 3
2     | Nazmi      | 2
3     | Y          | 1
untill the-20th

How do i code this thing? , here is my current code:

 <?php

            $getref = CMS::$MySql->Query("SELECT username FROM referrals GROUP BY username HAVING COUNT(username) !=0 ORDER BY COUNT(*) DESC");
             $num = CMS::$MySql->Query("SELECT username FROM referrals");

              for ($i = 1; $i <= 10; $i++) {
               while($row = $getref->fetch_assoc() && $count = $num->fetch_assoc()){

                      echo '<table><tr>';

                      echo '<td>'.$i.'</td>';
                      echo '<td>'.$row['username'].'</td><br>';
                      while($count = $num->fetch_assoc()){
                      echo '<td>'.$count->num_rows.'</td>';
                      }
                      echo '</tr>';


                                                }}
                         echo '</table>';

              ?>

I failed on counting the how many refferals does it have.

Scony · Accepted Answer · 2013-12-03 02:36:35Z

2

You can modify query to :

SELECT username, count(username) as cnt FROM referrals GROUP BY username HAVING COUNT(username) !=0 ORDER BY COUNT(*) DESC

Then you can simply use $row['cnt'] as your desired count

<?php

$getref = CMS::$MySql->Query("SELECT username, count(username) as cnt FROM referrals GROUP BY username HAVING COUNT(username) !=0 ORDER BY COUNT(*) DESC");

echo '<table>';
for($i = 1; $i <= 10 && $row = $getref->fetch_assoc(); $i++)
  {
    echo '<tr>';
    echo '<td>'.$i.'</td>';
    echo '<td>'.$row['username'].'</td>';
    echo '<td>'.$row['cnt'].'</td>';
    echo '</tr>';
  }
echo '</table>';

edited Dec 3, 2013 at 2:36

answered Dec 3, 2013 at 2:21

Scony

4,1581 gold badge19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sorting and count the value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related