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

All 2 Replies

I suggest to create one master table say, total slots
slot_master

slot_id
1
2
3
4
5
6
7
8
9

another table is the one you are using
allocated_slot
alloc_slot_id
1
2
3
5

Now you can easily join both table to find free slots

select slot_id from slot_master a left outer join allocated_slot b
on a.slot_id=b.alloc_slot_id
where b.alloc_slot_id is null

Only you need to insert/delete records to /from allocated_slot table as and when it happens

What are you setting the empty slot's value to? Let's say all your empty slots are set to NULL, then you can query for those values first

SELECT slot_number FROM inventory WHERE slot_item = NULL;

/*
   As per your example, if you have items in
   1, 2, 3 and 5, and all empty slots are
   set to NULL, this query will return
   an array: [4, 6, 7, 8, 9]
*/

then just use PHP's min function to find the smallest value, which will be your first available slot.

<?php
    $first_available_slot = min($resulting_array_from_query);
?>
Be a part of the DaniWeb community

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