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? 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? 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? 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 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? 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 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 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 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? 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? 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 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.. 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
Archived
This topic is now archived and is closed to further replies.