CODEWITHSUNDEEP
phpmysql
Stephen McVicker
Stephen McVicker

Reputation: 1

Loop through and update MySQL after specific row in PHP

I'm working on a game that allows players to store items in a MySQL database. I've got a table that stores every player's items. It's slot based, with each item having a unique value. So the table would have something like: id, itemvalue, slot, playerID.

I'm trying to figure out a way in PHP to allow the player to withdraw or deposit an item in a specific slot.

I need help updating entries after an action (withdraw or deposit) has taken place. For example: Say a player can have 10 slots for themselves. If the player withdraws an item from slot 4, how do I loop through their items from slots 5 to 10 and update the values to be -1 for their slot.

Of course, I'm getting the information from the table, let's call it "ItemTable" by using:

$sql = mysql_query("SELECT * FROM ItemTable WHERE playerID ='$_playerID' ORDER BY id ASC LIMIT 10");

Upvotes: 0

Views: 109

Answers (2)

Flash Thunder
Flash Thunder

Reputation: 12045

Would be something like this:

"SELECT * FROM ItemTable WHERE id > (SELECT itemID FROM ItemTable WHERE playerID='$_playerID') t2 ORDER BY id ASC LIMIT 10"

I mean to update, you do that:

"UPDATE ItemTable WHERE id > (SELECT itemID FROM ItemTable WHERE playerID='$_playerID') t2 SET (here you go with your updates) ORDER BY id ASC LIMIT 10"

(not sure if select should be named in this case, if it won't work, just remove "t2" from above queries)

Upvotes: 0

goodevans
goodevans

Reputation: 136

Something like:

UPDATE ItemTable SET slot=slot-1 WHERE playerID='$_playerID' AND slot>$withdrawnItemSlot

That should shuffle all the other items up a slot.

Upvotes: 1

Related Questions