Loop through and update MySQL after specific row in PHP

Question

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

If you're just learning PHP, please, do not learn the obsolete mysql_query interface. It's awful and is being removed in future versions of PHP. A modern replacement like PDO is not hard to learn. A guide like PHP The Right Way can help explain best practices. Always be absolutely sure your user parameters are properly escaped or you will have severe SQL injection bugs. — tadman
– tadman, Commented Jan 19, 2015 at 18:02
Hum.. Are you sure that you really need the 'id' ? You could use as primary key in your table the pair (playerID,slotNumber) and so your problem would be VERY simpler ( something like 'UPDATE ITEMTABLE SET itemvalue=-1 WHERE playerID = ... and slotNum > 4) — marco6
– marco6, Commented Jan 19, 2015 at 18:12
I'll look into more modern ways of using PHP and MySQL. I've only started a few months, so I will find it no problem learning a new API. — Stephen McVicker
– Stephen McVicker, Commented Jan 20, 2015 at 18:46

goodevans · Accepted Answer · 2015-01-19 18:45:00Z

1

Something like:

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

That should shuffle all the other items up a slot.

answered Jan 19, 2015 at 18:45

goodevans

1365 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephen McVicker Over a year ago

Thanks very much, that's the one that I went with and worked. Cheers.

Flash Thunder · Accepted Answer · 2015-01-19 18:49:27Z

0

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)

answered Jan 19, 2015 at 18:49

Flash Thunder

12.1k10 gold badges55 silver badges103 bronze badges

Collectives™ on Stack Overflow

Loop through and update MySQL after specific row in PHP

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related