Inserting value from one table to other table's column for specific user

[don1081]

Hello experts,

I need help. Please take a look at this code:

PHP Code:

$user_td = $db->fetch_array($result);
$result = $db->query("insert into ts_users(total_score) select sum(score) from ts_game_sessions where user_id = $user_id;"); 


This code is summing the all values of column "score" from tabel ts_game_sessions for "user_id". And then inserting sum of scores in column "total_score" of table ts_users.

Now the problem is, while inserting the summed value, it is creating a new user in ts_users and inserting summed value in "total_score" and autofilling new user details as NULL, NULL, NULL, NULL.

I want to insert summed value in "total_score" of existing user. How to do that?

[Fumigator]

Do that with an UPDATE query. You'll need to do a SELECT query to get the sum(score) value first.

As an aside, it's typically bad practice to store totals. It's better to select sum(score) each time it's needed. Storing totals creates a lot of unnecessary overhead to keep the stored totals up-to-date. The only time it makes sense is when the load on your database becomes so heavy, the sum() queries are too much to handle and you need a performance boost. Even then, I would keep the totals up-to-date be use of a trigger on the game_sessions table, so any time the game_sessions table is changed (inserted into, deleted from, or updated) the total_score is updated too.

[don1081]

Thanks for better suggestion. But the problem is i cannot able to display the total score in user profile. The only way i can display it by storing the total score first and calling it for display, i know it will be a load on server but i have no other option.
I will apreciate if you can help me in any way.

[Fumigator]

You can't SELECT SUM(score) FROM ts_game_session when you display a profile? Why not?

[don1081]

Thanks alot, now problem is solved and its displaying correctly.
Thanks for giving me time.