获取行数，其中user = $ _session ['username']

Question

I have a table with the columns: (ID, UserID, Slot, ItemID, Quant)

Lets say I have a row with values: ('1', '10', '2', '35', '200');

This row has a slot value of 2. i have twenty slots in an inventory tab. i want to insert data in ascending numerical order. is there a way to write a query where it checks to see the last slot used so it can assign the item to the next slot? so if slot 2 has been used the next item would then consume slot 3.

Or is there a way to compare multiple variables to a numerical value without having to have both variables equal to that number so like

if($var1 or $var2 or $var3 == 1) {
excute this code
}

instead of doing the following:

if($var1 ==1) {
  execute code
}
if($var2 == 1) {
  execute code
}
if($var3 == 1) {
  execute code
}

Answer 1

I hate to be the bearer of bad news, but IMHO your data model is fundamentally broken. There are two possible cases:

• If and only if you never ever need to access a single slot, i.e. if the slots are a closed entity, you should store a serialized slot structure.

• If you need whatever access to a single slot, I would expect to solve such a problem by having a table slots and another table consuming it via a jointable. Using a slot can then simply be acomplished by repeatedly INSERTing, until no more key violations occur (ofcourse there are more efficient ways)

Edit

In response to @Stoic's request, here is more elaboration on the second bullet: Assume the following:

• the table in the OQ is called useritems
• there is another table called users, where UserID comes from (PK)
• assume, there is another table called items, where ItemID comes from (PK)

Now we create a table userslots

CREATE TABLE userslots (
  UserID int, -- possibly some FK,
  SlotNum int,
  ItemId int, -- possibly some FK,
  ItemCount int,
  -- possibly more, as the game logic needs (Styling, etc.)
  PRIMARY KEY(UserID, SlotNUm),
)

and we create a row for every slot of every user in it, with SlotNum=0..N and ItemID=ItemCount=0 - this gives us also the possibility to have different users own a different number of slots.

Now if we want to insert an item, we go two steps:

• Maybe the USer already has such an item and we just need to increase the count? UPDATE userslots SET ItemCount=ItemCount+1 WHERE UserID=$UserID and ItemID=$ItemID LIMIT 1. If this returns 1 affected row, we are done.

• If not, we need to use a new slot: UPDATE userslots SET ItemID=$ItemID, ItemCount=1 WHERE UserID=$UserID and ItemCount=0 LIMIT 1.

• If we need to remove an Item, we just UPDATE userslots SET ItemCount=ItemCount-1 WHERE UserID=$UserID and ItemID=$ItemID and ItemCount>0 LIMIT 1, the number of affected rows (0 or 1) atomically shows us, if the user had such an item at all: No race with another session, that could lead to double-consumption.