CODEWITHSUNDEEP
phphtmlmysql
user3443701
user3443701

Reputation: 41

MySQL UPDATE Value of column and ADD new value with old value

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.

Upvotes: 1

Views: 1420

Answers (1)

kero
kero

Reputation: 10638

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

Upvotes: 1

Related Questions