View Full Version : Inserting value from one table to other table's column for specific user

12-22-2010, 12:01 PM
Hello experts,

I need help. Please take a look at this 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?

12-22-2010, 03:33 PM
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.

12-22-2010, 07:56 PM
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.

12-22-2010, 08:24 PM
You can't SELECT SUM(score) FROM ts_game_session when you display a profile? Why not?

12-23-2010, 11:17 AM
Thanks alot, now problem is solved and its displaying correctly.
Thanks for giving me time.