MySQL UPDATE列的值和旧值的ADD新值

Question

I have a page that shows the leader board score for all users that have registered in the system, which is stored in my database.

Table 1:

Points_hisotry table:

| points_id || user_id(fk) || point_hist |
|___________||_____________||____________|
|      1    ||     10      ||    100     |  
|___________||_____________||____________|
|      2    ||     11      ||     30     |
|___________||_____________||____________|
|      3    ||     11      ||     70     |
|___________||_____________||____________|
|      4    ||     11      ||    200     |

Table 2:

Users Table:

| users_id ||  username || firstname  || lastname ||
|__________||___________||____________||__________||
|    10    ||   alan1   ||    Alan    ||  Smith   ||
|__________||___________||____________||__________||
|    11    ||   Jaz12   ||    Jass    ||  Hopal   ||
|__________||___________||____________||__________||
|    12    ||   Shubs   ||    shubs   ||  hawash  ||
|__________||___________||____________||__________||
|    13    ||   John    ||    Rob     ||  engli   ||

In the points_history table, I have 3 rows with the same users_id where I need them to be summed up so I end up with total Point_hist for that users_id which should add up to 300.

I need a query to help me join this tables, then sum the rows of the same users_id to one and print it out on my scoreboard.

I have tried plenty of queries, but I have not got it right.

Here is some of my PHP from leaderboard.php:

$sql = "SELECT * FROM users, points_history WHERE users.users_id = points_history.users_id"; 
$user_query = mysqli_query($db_conx, $sql);
    while ($row = mysqli_fetch_array($user_query, MYSQLI_ASSOC)) {
    $username = $row ["username"];
    $point_hist = $row["point_hist"];

The above query is to print out all of the username, and their points from the above two table.

I am new to this, so I need some help please.

Answer 1

You can use SUM() to sum them up (who'd have thought), but you'll need to tell SQL via GROUP BY which rows to sum and which it shouldn't.

SELECT users.*, SUM(points_history.point_hist) AS total_points
FROM users
INNER JOIN points_history
    ON users.users_id = points_history.users_id
GROUP BY users_id

should do the trick