Hello,
I need for my game inventory function a mysql query which checks the next available free slot id.

example:
I have item on slot 1, 2, 3 and 5.
Slot 4,6,7,8,9 ... are free..

Now I need query which would get the number 4.
Something like
SELECT * FROM inventory WHERE slot >= 1 AND slot <= 10;

But this doesnt really get the first available slot number.

Any expert knows how to do it?

Recommended Answers

Show us the structure of the inventory table. Use the SQL command "describe inventory" to get that data.

Jump to Post

How does your database store slots? If the first available slot simply isn't present in the table, a simpler approach would be to query for used slots:

select slot from inventory order by slot;

Then figure out the first unused one in code:


       
Jump to Post

Do you actuall have a slot column in the database? if so you can use

select min(slot)
from inventory
where item_id is null

I'm not quite sure if that is correct for MySQl since I use Oracle PL/SQL but that should be the gist of it.

Jump to Post

All 11 Replies

Show us the structure of the inventory table. Use the SQL command "describe inventory" to get that data.

struct:
id int
owner_id int
item_id int
pos int
place int

First time I hear about

SQL command "describe inventory"

still need help y.y

How does your database store slots? If the first available slot simply isn't present in the table, a simpler approach would be to query for used slots:

select slot from inventory order by slot;

Then figure out the first unused one in code:

int next_slot = 1;

for (auto it : slots) 
{
    if (*it != next_slot)
    {
        break;
    }

    ++next_slot;
}

Do you actuall have a slot column in the database? if so you can use

select min(slot)
from inventory
where item_id is null

I'm not quite sure if that is correct for MySQl since I use Oracle PL/SQL but that should be the gist of it.

The place row is going from 0-4. Depends how many inventory bags the user has. If he only got 1 bag then he only have place 0. If he got 2 bags then place 0 and 1.
Pos depends on the size of the bag. 4/8/12/16 are available.

select min(slot)

Does this query select the lowest number inside database?

what are the actual columns in the database? What are the types and what do they store?

The query just gives the lowest slot number availible in the inventory table where there is no item in that slot. This is if slot is a column.

http://s14.directupload.net/images/140710/tt57wk2v.jpg

example:
place 0 pos 0 =
item is in bag 1 on position 1
place 1 pos 1 =
item is in bag 2 on position 2
place 1 pos 0 = item is in bag 2 on pos 1

maximum place = 4 (0 to 4)
maximum pos = 4/8/12/16 (depends on which bag you have) There are 4 different bags

Is the image you poseted the inventory table?

Yes, thats right

I would have to agree with deceptikon on this. Read in the database into your code and then figure out where the free slot is.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.