« What are some Indigenous Group that have been colonised by Europeans?
How do you write a letter of query for Grad school? »

How can I select the next empty value when inserting into a database using PHP and SQL?

For instance. i have a database table. this table has 31 rows in it. the first is owner which determines who "owns" the list. the rest are called "slot" followed by a number in order. "slot1" "slot2" all the the way to slot30.

what im trying to do is add to this table a value in the next empty slot.

i’m unsure how to achieve that however.

Since all slots have the letters "slot" in front of the number, you’d only store the number. (Storing "slot" violates normalization.) Just use max(slot) + 1 as the next number.

This entry was posted on Friday, December 11th, 2009 at 5:55 am and is filed under select into. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “How can I select the next empty value when inserting into a database using PHP and SQL?”

  1. Normally as part of the design, you must:

    First have some way of identifying an empty slot.
    Next, you must have some way of "traversing" the list of slots. Once you see an empty slot, thats it.

    End
    References :

  2. Since all slots have the letters "slot" in front of the number, you’d only store the number. (Storing "slot" violates normalization.) Just use max(slot) + 1 as the next number.
    References :

  3. select max(CAST(stuff(col_name, 1, 4, ”) as int))+1 from Table_name
    where col_name like ’slot%’
    OR
    select max(CAST(replace(col_name, ’slot’, ”) as int))+1 from Table_name
    where col_name like ’slot%’
    References :
    http://www.sql-ex.ru/

  4. One problem I see with your structure is that, while you may see the slot sequence as ’slot1′, ’slot’2′, ’slot3′, etc.; the computer will see it as ’slot1′, ’slot10′, ’slot11′…, ’slot2′, ’slot20′,… You may want to change ’slot1′ – ’slot9′ to ’slot01′ – ’slot09′ respectively so the DBMS sequences them the way you expect.

    However, a bigger problem is that you are basically using the same column to denote two different things – owner or slot (a big no-no in proper database design.) What I would do is this:

    table list
    list_id (autonum PK)
    owner (probably a FK to an owner table)

    table slots
    list_id (FK to list table, part of PK)
    slot_id (smallint, part of PK)
    CONSTRAINT valid_slot CHECK (slot >= 1 AND slot <= 30)

    With this structure, you can have as many different owners as needed, each with their own list (or even multiple lists for a given owner if need be). If a slot is not used, the row doesn’t exist. To get the next-available slot if slots always assigned (and unassigned) in sequence:

    SELECT MAX(slot) + 1 AS next_slot FROM slots s, list l
    WHERE owner = <whatever> AND s.list_id = l.list_id

    If there possibly are gaps, it gets a bit more complicated:

    SELECT MIN(next_slot) AS next_slot FROM
    (SELECT 1 AS next_slot FROM list WHERE NOT EXISTS
    (SELECT 1 FROM slots s, list l
    WHERE owner = <whatever> AND s.list_id = l.list_id AND slot = 1)
    UNION
    SELECT MIN(slot) +1 AS next_slot FROM slots s1, list l
    WHERE owner = <whatever> AND s.list_id = l.list_id
    AND NOT EXISTS
    (SELECT 1 FROM slots s2
    WHERE s2.list_id = s1.list_id AND s2.slot = s1.slot + 1))
    References :

Leave a Reply