Finding lowest available ID algorithm

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Finding lowest available ID algorithm

Post 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
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

Why do you test against CountryID but use itemID?
Heh never minds its corrected!
Last edited by magicrobotmonkey on Mon Apr 05, 2004 1:58 pm, edited 1 time in total.
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

sorry corrected it - I just turned CountryID to ItemID to make it generic. Everything is as it should be now.
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

No, you're perfectly sane - I edited it.
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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?
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

true - but isn't $previtemid incremented by the fetch_array each time to while loop is cycled to the next row?
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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...
Last edited by magicrobotmonkey on Mon Apr 05, 2004 2:06 pm, edited 1 time in total.
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post 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?
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

yea i see it all now - look at my edit on the previous post - what's your query?
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post 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
}
Last edited by mjseaden on Mon Apr 05, 2004 2:09 pm, edited 1 time in total.
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

Query is:

$result = mysql_query("SELECT ItemID FROM Items");
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

hi mark - so would it be $result that then returns the lowest avilable ItemID?
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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.
Post Reply