Aiko Posted May 31, 2012 Share Posted May 31, 2012 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? Quote Link to comment https://forums.phpfreaks.com/topic/263423-fetching-the-first-missing-number-in-a-column/ Share on other sites More sharing options...
Jessica Posted May 31, 2012 Share Posted May 31, 2012 Why? Quote Link to comment https://forums.phpfreaks.com/topic/263423-fetching-the-first-missing-number-in-a-column/#findComment-1350047 Share on other sites More sharing options...
Aiko Posted May 31, 2012 Author Share Posted May 31, 2012 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? Quote Link to comment https://forums.phpfreaks.com/topic/263423-fetching-the-first-missing-number-in-a-column/#findComment-1350049 Share on other sites More sharing options...
Barand Posted May 31, 2012 Share Posted May 31, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/263423-fetching-the-first-missing-number-in-a-column/#findComment-1350051 Share on other sites More sharing options...
Aiko Posted May 31, 2012 Author Share Posted May 31, 2012 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? Quote Link to comment https://forums.phpfreaks.com/topic/263423-fetching-the-first-missing-number-in-a-column/#findComment-1350053 Share on other sites More sharing options...
cyberRobot Posted May 31, 2012 Share Posted May 31, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/263423-fetching-the-first-missing-number-in-a-column/#findComment-1350054 Share on other sites More sharing options...
trq Posted May 31, 2012 Share Posted May 31, 2012 SELECT house+1 FROM tbl LEFT JOIN tbl t ON (t.house = tbl.house+1) WHERE t.house IS NULL LIMIT 1 Quote Link to comment https://forums.phpfreaks.com/topic/263423-fetching-the-first-missing-number-in-a-column/#findComment-1350055 Share on other sites More sharing options...
Barand Posted May 31, 2012 Share Posted May 31, 2012 <?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 Quote Link to comment https://forums.phpfreaks.com/topic/263423-fetching-the-first-missing-number-in-a-column/#findComment-1350056 Share on other sites More sharing options...
Aiko Posted May 31, 2012 Author Share Posted May 31, 2012 <?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? Quote Link to comment https://forums.phpfreaks.com/topic/263423-fetching-the-first-missing-number-in-a-column/#findComment-1350065 Share on other sites More sharing options...
Jessica Posted May 31, 2012 Share Posted May 31, 2012 What is the House column used for? Quote Link to comment https://forums.phpfreaks.com/topic/263423-fetching-the-first-missing-number-in-a-column/#findComment-1350072 Share on other sites More sharing options...
PFMaBiSmAd Posted May 31, 2012 Share Posted May 31, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/263423-fetching-the-first-missing-number-in-a-column/#findComment-1350075 Share on other sites More sharing options...
Aiko Posted May 31, 2012 Author Share Posted May 31, 2012 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.. Quote Link to comment https://forums.phpfreaks.com/topic/263423-fetching-the-first-missing-number-in-a-column/#findComment-1350077 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.