CODEWITHSUNDEEP
phpmysql
easy pz
easy pz

Reputation: 69

PHP/mysql:Rank users

I have this table which displays data from mysql and ordering them by most of clicks but i don't have #1 or #2 and so on.... here is the code:

<table align="center" style="width:50%">
  <tr>

    <th>User</th> 
    <th>clicks</th>
  </tr>
  <tr>

    <td>

    <?php include 'conn.php';
    $sql = "select user, count(*) duplicates from users group by user order by duplicates desc";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo $row["user"]. "<br>";
        }
    } else {

    }

    ?>
    </td>
    <td>
    <?php
  include 'conn.php';
  $sql = "select user, count(*) AS duplicates from users group by user order by duplicates desc";
  $result = $conn->query($sql);

  if ($result->num_rows > 0) {
      // output data of each row
      while($row = $result->fetch_assoc()) {
          echo $row["duplicates"]. "<br>";
      }
  } else {

  }

  $conn->close();
    ?></td>
  </tr>

</table>

output is this in a table:

users    clicks
user1    3
user2    2
user3    1

but i want the output to be:

rank    users    clicks
1       user1    3
2       user2    2
3       user3    1

thanks for answering

Upvotes: 1

Views: 51

Answers (2)

Eren
Eren

Reputation: 116

  1. Add Rank section to table
<tr>
    <th>Rank</th> 
    <th>User</th> 
    <th>clicks</th>
</tr>
  1. Add rank for each result
<td>
    <?php include 'conn.php';
    $sql = "select user, count(*) duplicates from users group by user order by duplicates desc";
    $result = $conn->query($sql);
    $rank = 1;
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo $rank. "<br>";
            $rank++;
        }
    }
    ?>
</td>

Full Code

<table align="center" style="width:50%">
  <tr>
    <th>Rank</th> 
    <th>User</th> 
    <th>clicks</th>
  </tr>
  <tr>
    <td>
    <?php include 'conn.php';
    $sql = "select user, count(*) duplicates from users group by user order by duplicates desc";
    $result = $conn->query($sql);
    $rank = 1;
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo $rank. "<br>";
            $rank++;
        }
    }
    ?>
    </td>
    <td>
    <?php include 'conn.php';
    $sql = "select user, count(*) duplicates from users group by user order by duplicates desc";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo $row["user"]. "<br>";
        }
    }
    ?>
    </td>
    <td>
    <?php
  include 'conn.php';
  $sql = "select user, count(*) AS duplicates from users group by user order by duplicates desc";
  $result = $conn->query($sql);

  if ($result->num_rows > 0) {
      // output data of each row
      while($row = $result->fetch_assoc()) {
          echo $row["duplicates"]. "<br>";
      }
  }
  $conn->close();
    ?></td>
  </tr>
</table>

Upvotes: 2

Kiwagi
Kiwagi

Reputation: 178

using javascript:

<script type="text/javascript">
var nb = document.getElementsByClassName("number");
for (var i = 0; i < nb.length; i++) {
    nb[i].innerHTML = (i+1)+".";
 }
</script>


<table align="center" style="width:50%">
<tr>

    <th>User</th>
    <th>clicks</th>
</tr>
<tr>

    <td class="number">

        <?php include 'conn.php';
        $sql = "select user, count(*) duplicates from users group by user order by 
    duplicates desc";
        $result = $conn->query($sql);

        if ($result->num_rows > 0) {
            // output data of each row
            while($row = $result->fetch_assoc()) {
                echo $row["user"]. "<br>";
            }
        } else {

        }

        ?>
    </td>
    <td>
        <?php
        include 'conn.php';
        $sql = "select user, count(*) AS duplicates from users group by user order by 
   duplicates desc";
        $result = $conn->query($sql);

        if ($result->num_rows > 0) {
            // output data of each row
            while($row = $result->fetch_assoc()) {
                echo $row["duplicates"]. "<br>";
            }
        } else {

        }

        $conn->close();
        ?></td>
</tr>

</table>

Upvotes: -1

Related Questions