Coman Paul Coman Paul - 4 months ago 10
MySQL Question

MYSQL inventory slots (add item to next slot free)

$slt = mysql_query("select Slot, ItemId, UserId, max(Slot) Slot
from useritems
group by UserId");

while ($sloot = mysql_fetch_assoc($slt))
Echo "<br>Items with biggest slots are: " . $sloot['ItemId'] . " from user " . $sloot['UserId']. "- in slot-". $sloot['Slot'];

This is the table

Idi Quantity ItemId UserId Slot ExpirationDate

Outputs the smallest Slots...Why?

1.I want to show me the biggest inventory slot from each user, so when i add a new item to his inventory i can add on next slot..From example user Paul has 5 items that ocupies slots 1,2,3,4,5 and the next item will be on slot 6.

2.When a user moves his items on slots 1,2,4,5,6 the next item added will be on slot 3

I did a lot of search but i can't find out myself:) PS:The game wich i
m making it
s just for fun..but maybe someday will be a great game:) (dreams,dreams :)) )


SQLFIDDLE is very good thank you:) it's exactly what i need to learn some SQL

Table useritems

useritems Table IMAGE

items Table

My Echo shows me that:

Id = 1 and it should be 3; user=4(good); slot=4(good)
Id = 1 and it should be 2; user=5(good); slot=2(good)


I found it!:) i just forgot to select data where UserId=my_id but it shows me the corect output only if the respective user has more than 3 items...

$slt = mysql_query("SELECT * FROM useritems WHERE Slot=(select max(Slot) from useritems) and UserId='$id'");     

and $id = $_SESSION['id'];

$slt = mysql_query("
    SELECT * FROM `useritems` 
    WHERE UserId='$id' AND Slot=(select max(Slot) 
    from useritems where UserId='$id')

EDIT2:I found the best way with all i was searching but i don`t knwo how to use WHERE clause userId='$id'

$slt2 = mysql_query("select l.Slot + 1 as start
    from useritems as l
    left outer join useritems as r on l.Slot + 1 = r.Slot
    where r.Slot is null and l.UserId=4;
") or die(mysql_error());   `

With this query the item should be placed on Slot 2(wich is missing) but it`s dysplays Slot 6 (wich is the highest for the user with UserId=4)

Finally this is the last edit

$slt2 = mysql_query("SELECT  Slot + 1 as start
   FROM    useritems mo
       FROM    useritems mi 
       WHERE   mi.Slot = mo.Slot + 1 AND mi.UserId = '$id'  
") or die(mysql_error());   `

This is what i was searching.:X