Jump to content

Archived

This topic is now archived and is closed to further replies.

ShopMAster

Updating one table with another table's data

Recommended Posts

I have a database with two tables.

1st table is called players (this list about 60 players and thier records) This is the code for my statedit page when I need to update a players info as well as add a new player.

[code]
  if ($id) {

    $sql = "UPDATE players SET player_name='$player_name',player_perc='$player_perc',player_win='$player_win',player_loss='$player_loss',player_lastw='$player_lastw',player_lastl='$player_lastl', player_hs='$player_hs',player_div='$player_div',player_rival='$player_rival', player_rp='$player_rp',player_cfl='$player_cfl',player_hc='$player_hc',player_starless='$player_starless',player_nk='$player_nk',player_team='$player_team' WHERE id=$id";

  } else {

    $sql = "INSERT INTO players (player_name,player_perc,player_win,player_loss,player_lastw,player_lastl,player_hs,player_div,player_rival,player_rp,player_cfl,player_hc,player_starless,player_nk,player_team) VALUES ('$player_name','$player_perc','$player_win','$player_loss','$player_lastw','$player_lastl','$player_hs','$player_div','$player_rival','$player_rp','$player_cfl','$player_hc','$player_starless','$player_nk','$player_team')";

  }

  // run SQL against the DB

  $result = mysql_query($sql);

  
[/code]

The 2nd table is called random_games This table hold games played by the players. Players fill out a form and it goes to this random_games table. This randomgameedit.php page allows me to look at the games and then delete them after I update the stats using the above page. Here is that page:

[code]


if ($submit) {

  // here if no ID then adding else we're editing

  if ($id) {

    $sql = "UPDATE random_games SET random_winner='$random_winner',random_wscore='$random_wscore',random_loser='$random_loser',random_lscore='$random_lscore',random_comment='$random_comment' WHERE id=$id";

  } else {

    $sql = "INSERT INTO random_games (random_winner,random_wscore,random_loser,random_lscore,random_comment) VALUES ('$random_winner','$random_wscore','$random_loser','$random_lscore','$random_comment')";

     }

  // run SQL against the DB

  $result = mysql_query($sql);

  
[/code]

So what I need help with is I want to add an "UPDATE" function at the end of every random game so that the players wins and losses, as well as last_win, and last_loss are updated so I never have to edit each player myself. If I could do this it would take a great load off of having to update stats as there are over 100 games each day.

Please help. How do you update one table by pulling info from another table.

Thanks.

Shopmaster

Share this post


Link to post
Share on other sites
You shouldn't post entire scripts -- it just confuses matters. BTW, you have an encoded HTML entity in one of your INSERT statements.

UPDATE statements can handle multiple tables, just like SELECT queries, so this should not be a problem.

Share this post


Link to post
Share on other sites
Sorry about that. Can you tell me the syntax that I need to use to take the results of one table to another?

Share this post


Link to post
Share on other sites
It's all in [a href=\"http://dev.mysql.com/doc/refman/5.0/en/update.html\" target=\"_blank\"]this refman page[/a] -- I can't give specifics at this point.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.