Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Dec 2010
    Posts
    19
    Thanks
    3
    Thanked 0 Times in 0 Posts

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

    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?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • Users who have thanked Fumigator for this post:

    don1081 (12-23-2010)

  • #3
    New Coder
    Join Date
    Dec 2010
    Posts
    19
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You can't SELECT SUM(score) FROM ts_game_session when you display a profile? Why not?

  • Users who have thanked Fumigator for this post:

    don1081 (12-23-2010)

  • #5
    New Coder
    Join Date
    Dec 2010
    Posts
    19
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks alot, now problem is solved and its displaying correctly.
    Thanks for giving me time.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •