get free number between x and x

[Daneos]

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?

[urtrivedi]

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

[DJBirdi]

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