The sum of the user's points

Question

I am a beginner when it comes to PHP and it is not my specialty, I am rather in the front end and I have a big problem.

I need to edit one of the tables after a guy who doesn't work with our company anymore and it seemed simple to me at first, but I can't find a solution.

I have a table with 4 columns: Date, Full Name, Login, and Points. Records of added points are saved in the database, but each action adding points creates a new record in the database, let's say:

Date	Full Name	Login	Points
04/10/2021	John Kovalsky	koval	10
04/11/2021	John Kovalsky	koval	20
04/12/2021	John Kovalsky	koval	15

The script below works, and I can almost understand the syntax, there is a display limit set here and a table pagination added. The script displays all records in the database.

The problem is that I need exactly the same, but with the sum of the user's points, so that a given user is displayed only once, and in the "Points" column, the sum of all his points is displayed.

I tried with the array_sum () function, but it enumerates all the records in the database for me. The script looks like this:

<?php

$login = $_GET['login'];
$_SESSION["login"] = $login;

include('Pagination.php');
include('config.php');

$limit = 200;
$queryNum = $db->query("SELECT COUNT(*) as ID FROM db_main");
$resultNum = $queryNum->fetch_assoc();
$rowCount = $resultNum['ID'];
$pagConfig = array(
    'totalRows' => $rowCount,
    'perPage' => $limit,
    'link_func' => 'searchFilter'
);
$pagination =  new Pagination($pagConfig);

$query = $db->query("SELECT * FROM db_main LIMIT $limit");
echo "<center>";
echo "<table id=\"tabela\" cellpadding=\"2\" border=1>";
echo "<tr>";
echo "<th>".'Date'."</th>";
echo "<th>".'Full Name'."</th>";
echo "<th>".'Login'."</th>";
echo "<th>".'Points'."</th>";
echo "</tr>";
echo "</tr>";

if($query->num_rows > 0){
  ?>

  <?php
  while($r = $query->fetch_assoc()){

    echo "<tr>";
    echo "<td>".$r['Date']."</td>";
    echo "<td>".$r['Full_name']."</td>";
    echo "<td>".$r['Login']."</td>";
    echo "<td>".$r['Points']."</td>";
    echo "</tr> </center>";

  }

  echo $pagination->createLinks();

}
?>

Please help, I have no idea how to do it. I will be very grateful for your help and hints.

Answer 1

You could do it by using a different query.

First, get all unique users.

$db->query("SELECT COUNT(DISTINCT(Full_name)) as ID FROM db_main");

Now get the collected data for each user.

$db->query("SELECT MAX(Date) AS Date, Full_name, Login, SUM(Points) AS Points FROM db_main GROUP BY Full_name LIMIT $limit");

Using the GROUP option will make sure you get one row per user.

The SUM will give you the amount of all the user's points.

The MAX(Day) will return user's the last day field.

There are two assumptions here, that the login is always the same for the user, and that the Points field is numeric and not a string.