Page 1 of 2

Finding lowest available ID algorithm

Posted: Mon Apr 05, 2004 1:48 pm
by mjseaden
Dear All,

I have the following PHP code:

Code: Select all

$previtemid = 0;						// Meaning we always start with minimum ItemID of 1
    $itemid = -1;
    $breakout = FALSE;

    while($row = mysql_fetch_array($result) && $breakout == FALSE)
    {
    	if( $row['ItemID'] != $previtemid + 1)
        {
            $itemid = $previtemid + 1;
        	$breakout = TRUE;
        }

    	$previtemid = $row['ItemID'];
    }

    if( $itemid == -1 )
    {
    	$itemid = $previtemid + 1;
    }
This is supposed to find the lowest 'ItemID' available above 1 - for example, if ItemID's 1, 2, 3, 6, 7 were occupied, I would like the above algorithm to find ItemID 4 to allocate to the next item to add to the list.

However, from the error messages I am getting this algorithm tries to allocate a new ItemID of 1, when 1 is occupied, and I'm not sure why.

Can anyone explain?

Many thanks

Mark

Posted: Mon Apr 05, 2004 1:54 pm
by magicrobotmonkey
Why do you test against CountryID but use itemID?
Heh never minds its corrected!

Posted: Mon Apr 05, 2004 1:55 pm
by mjseaden
sorry corrected it - I just turned CountryID to ItemID to make it generic. Everything is as it should be now.

Posted: Mon Apr 05, 2004 1:57 pm
by mjseaden
No, you're perfectly sane - I edited it.

Posted: Mon Apr 05, 2004 2:01 pm
by magicrobotmonkey
I see, you never actually increment $previetemid! you always test against it plus one, but you never increment it! I think you need to rethink you're login on this one - want to write what you're trying to do with psuedocode?

Posted: Mon Apr 05, 2004 2:02 pm
by mjseaden
true - but isn't $previtemid incremented by the fetch_array each time to while loop is cycled to the next row?

Posted: Mon Apr 05, 2004 2:03 pm
by magicrobotmonkey
Uhh I don't see why it would be. you either ned $previtemid++ or $previtemid=$previtemid+1 somewhere in there!

edit: oh I see you're doing it with ItemID - why are you doing it that way? Depending on your query, your rows may or may not come out of the DB in order...

Posted: Mon Apr 05, 2004 2:05 pm
by mjseaden
$previtemid is set to the row's ItemID at the end of the while loop. Then the while loop cycles again, and this time the next row in the table is stored in the $row array. Then I'm comparing the ItemID of the current $row to the ItemID of the previous one, stored in $previtemid. Isn't that correct?

Posted: Mon Apr 05, 2004 2:07 pm
by magicrobotmonkey
yea i see it all now - look at my edit on the previous post - what's your query?

Posted: Mon Apr 05, 2004 2:07 pm
by mjseaden
It does this:-

Start with previtemid = 0

while ( cycles through rows in the table )
{
Check that the current row's item ID is not equal to the previous one, plus 1. If it isn't, there's a gap in the item ID sequence, so allocate our new ItemID to that gap number.

If not -> store the current row's ItemID in previtemid for the next cycle to check the above statement
}

Posted: Mon Apr 05, 2004 2:08 pm
by mjseaden
Query is:

$result = mysql_query("SELECT ItemID FROM Items");

Posted: Mon Apr 05, 2004 2:11 pm
by magicrobotmonkey
yea see, they are not coming out in order - so the first one's itemID isn't 1, so your beutiful algorithm which took me a bit to see thinks the sopot is open. Try something like this:
$result = mysql_query("SELECT ItemID FROM Items ORDERBY ItemID");

You might need an ASC OR DESC in there, I forget which ones which

Posted: Mon Apr 05, 2004 3:00 pm
by markl999
Just jumped into this thread so forgive me if i'm off course ;)
If you want to find the lowest available id from a table you could do:

SELECT MIN( a.ItemID +1 ) AS lowest
FROM Items a
LEFT JOIN Items b ON a.ItemID +1 = b.ItemId
WHERE b.ItemID IS NULL

Posted: Mon Apr 05, 2004 3:27 pm
by mjseaden
hi mark - so would it be $result that then returns the lowest avilable ItemID?

Posted: Mon Apr 05, 2004 3:30 pm
by markl999
Well if you did:

$sql = "SELECT MIN( a.ItemID +1 ) AS lowest FROM Items a LEFT JOIN Items b ON a.ItemID +1 = b.ItemId WHERE b.ItemID IS NULL";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($res);

Then $row['lowest'] should contain the lowest available ItemID ... in theory ;) I'm not sure of your exact table/column names so you might have to tweak it slightly.