mysql multiple table updates at once

[BankOfMystic]

Ok so I've been trying to wrap my head around this and I can't even figure out where to start. I'm a beginner in mysql so this is what i'm trying to do.

There's 3 tables I need to modify depending on their values.

Tables are items, player_items, and players

English terms - I want to make items that expire.

items has duration field, player_items had purchase timestamp and acitve field, players i need to remove the atk and def that the item gave.

So I need to update player_items set active = n where the time between now and timestamp > duration and update players set atk = atk - itematk, def = def - itemdef.

and I need to do this for multiple users, so basically everyone that owns that item id, it checks if it's active, if it's active it checks when it was bought, if it's expired then it sets active to n and updates all other values accordingly, and it isn't expired then it does nothing and moves to the next player.

[hallsofvallhalla]

maybe i am miss reading but i am not sure how you want you time to be, whether its a long duration or something quick. You have to be careful about not touching the database too much.
the timer function can be used several ways so I will write a quick base code and maybe you can go from there....
I am not sure why you have the second items table.

first lets start by activating the item, this would be done by the player using the item right?

Code: Select all

 $selecteditem="SELECT * from items where id = '$itemused'";
              $selecteditem2=mysql_query( $selecteditem) or die("could not get the selected item");
              $selecteditem3=mysql_fetch_array( $selecteditem2);
$attackbonus =  $selecteditem3[attack];
$defensebonus =  $selecteditem3[defense];

/////update player to receive the items bonus////////////////
$updateplayer="update players set attack='attack+'$attackbonus',defense=defense+'$defensebonus' where name='$player'";
  mysql_query($updateplayer) or die("Could not update player");

//////////////////////start the timer on the item////////////////
$maxtime =  $selecteditem3[maxtime];   
$updateitem="update items set timer='$maxtime',active=1 where id='$itemused'";
  mysql_query($updateitem) or die("Could not update item timer");

///////////////////////////////////////////////////////////////////////////////

now to deactivate the item

Code: Select all

//////////////////start a while statement checking all active items//////////////
   $iteminfo="SELECT * from items where active = 1";
      $iteminfo2=mysql_query($iteminfo) or die("could not select items.");
         while( $iteminfo3=mysql_fetch_array( $iteminfo2))
      {
              /////////////////////////////////////check to see if the item timer is dead/////////////////
      if  ($iteminfo3[timer] < 1)  ////////////////if the timer is dead then deactivate the item
{       
//////////get the player id from the item...who owns the item....  
$attackbonus =  $iteminfo3[attack];
$defensebonus =  $iteminfo3[defense];
$playeritemid = $iteminfo3[playerid];
////update player to negate the items bonus////////////////
$updateplayer="update players set attack='attack-'$attackbonus',defense=defense-'$defensebonus' where id='$playeritemid'";
  mysql_query($updateplayer) or die("Could not update player");

//////////////////////unactivate the item////////////////

$updateitem="update items set timer='0',active=0 where id='$itemused'";
  mysql_query($updateitem) or die("Could not update item timer");

///////////////////////////////////////////////////////////////////////////////

           } }

[hallsofvallhalla]

you still have to hit the database every time the players stats change unless you make a constant variable on the server or if you make a new session variable but that's alot of security issues and/or server data when the player count reaches high. Not only that when the player logs out the server must kill the variable and still update the players table. That's the problem with browser based games is your client is a browser that looses data when it refreshes.

I guess you could create the session data for when the player logs back in and only when a item is used but then again thats still hanging variables all over the place.

[hallsofvallhalla]

(R)

I guess I worded it wrong, I meant to say, in addition to what Jatt said remember that... insert my post here.....

and Jatt is very correct in saying the less DB hits you do the better!