Jump to content


Photo

Updating one table with another table's data


  • Please log in to reply
3 replies to this topic

#1 ShopMAster

ShopMAster
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 02 May 2006 - 02:28 PM

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.

  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);

  

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:



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);

  

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

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 May 2006 - 06:57 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 ShopMAster

ShopMAster
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 02 May 2006 - 09:02 PM

Sorry about that. Can you tell me the syntax that I need to use to take the results of one table to another?

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 May 2006 - 05:18 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users