Fetching the first missing number in a column

[Aiko]

Hello everyone!

 

I have a question, is it possible to get the first missing number in a column?

 

As an example:

 

ID       House        Owner
----     ---------    ---------
11        1           Scarlett
12        2           Johan
13        3           Black
14        5           Wid
15        8           Ow

 

In this particular example I need to add the missing house, in this case 4 and 6-8.

While having in mind that I delete rows on a regular basis and I don't really 'need' the ID for this function,

how can I make sure that $openhouse will be replaced by 4, and after that by 6-8?

[Jessica]

Why?

[Aiko]

Well, I'm actually writing a more advanced script, but I never followed any lessons or such. Therefore I do not know how things like this should work. I'm not a noob @ php, there are just a lot of functions I've never heard about.

 

It's supposed to be part of my banking and inventory script. I made it somewhat easier by using another example. If I explained the whole situation I'd probably be busy for another hour, since the table structure and scripts are a bit complex.

 

Anyway, before getting any why's, those aren't really helping. I just want to know how to get something like that work. Either for now or when I need it somewhere else in a script. So can someone help me out with this?

 

 

[Barand]

WhenI started my working life there was a customer at my bank who died with an overdraft of £1,000,000. Because he no longer needed his account number it was allocated to me when I opened an account at the bank. Imagine how I felt when I opened my first statement with an overdraft like that!

 

The way to do it is DON'T reuse ids

[Aiko]

 

WhenI started my working life there was a customer at my bank who died with an overdraft of £1,000,000. Because he no longer needed his account number it was allocated to me when I opened an account at the bank. Imagine how I felt when I opened my first statement with an overdraft like that!

 

The way to do it is DON'T reuse ids

 

I know I know, in this case it's not for the 'ID' column, but for the 'House' column.. and all I asked was for some help.. Is there really nobody that knows how to do this?

[cyberRobot]

Do either of these help?

http://www.xaprb.com/blog/2005/12/06/find-missing-numbers-in-a-sequence-with-sql/

http://www.ehow.com/how_8591747_missing-numbers-php-array.html

[trq]

SELECT house+1 FROM tbl LEFT JOIN tbl t ON (t.house = tbl.house+1) WHERE t.house IS NULL LIMIT 1

[Barand]

<?php
$x = array (1,2,3,5,;

for ($i=0, $k = count($x); $i < $k-1; $i++)  {

    if ($x[$i] != $x[$i+1]-1) {
        for ($j = $x[$i]+1; $j < $x[$i+1]; $j++) echo "$j<br />";
    }
}
?>

 

result

4

6

7

[Aiko]

<?php
$x = array (1,2,3,5,;

for ($i=0, $k = count($x); $i < $k-1; $i++)  {

    if ($x[$i] != $x[$i+1]-1) {
        for ($j = $x[$i]+1; $j < $x[$i+1]; $j++) echo "$j<br />";
    }
}
?>

 

result

4

6

7

 

This script would indeed give me 4, 6 and 7.  But I need to fill in the $x array by hand, but I want the script to look for the missing values..

Also, I'd also only need just 1 result, the next following number that is missing.. Can you help me with that?

[Jessica]

What is the House column used for?

[PFMaBiSmAd]

I delete rows on a regular basis

 

If your design expects a specific list of `house` values, it will be much simpler to just clear the `owner` value, rather than to delete the row. To add an owner to the first available `house` value, you would just use an UPDATE query UPDATE your_table SET owner = '$some_new_value' WHERE owner = '' LIMIT 1

[Aiko]

Okay, how I want this script to work,

there's an items table, which contains the base item.

Also, there's an items_game table, the one we're talking about right now.

this one has 4 columns that are relevant

 

ITEMID, OWNERID, STOREPOSITION and STORETYPE

 

Storeposition tells the gameclient where the item is stored, every square has gotten a nr. so we didn't have to story X and Y locations.

But, we want to read out the items in the bank on the website too. Extra functionality.

 

So, on that page, we have a list with all the items, all items are listed where OWNERID=$sesid <sessionid> and STORETYPE=2 <the ID for bank, 3 is marketstorage, 4 is IN the market>

 

When buying something from the market, which doesn't need a STOREPOSITION, the STORETYPE will be changed from 4 to 3. But, you also have items in the market storage, so we want a position nr to be set. this should be 2 if there is already an item with 1, and it should be 3 when there are items with STOREPOSITION 1, 2 and 4.

 

Every other thing already has a check and stuff, the only thing missing is the STOREPOSITION update, I set it to update it to '1' now by saying STOREPOSITION=$nextpos and $nextpos = '1' (so I can easily edit the script).

 

I hope I made this thing clear.. I'm not the best guy you can have when it comes to explaining stuff..

 

I repeat, the only thing of my script that is missing is the STOREPOSITION update part..