Finding unused integer values with MySQL

Want to chit chat about anything, do it here ... posting here won't increase your post count (or shouldn't!). Please do not post any "phpBB" specific topics here unless they do not fit into the category above. Do not post bug reports, feature or support requests!
Forum rules
Please do not post any "phpBB" specific topics here unless they do not fit into the category above.

Do not post bug reports, feature or support requests! No really... Do not post bug reports, feature or support requests! Doing so will make Bertie a very sad bear indeed. :(
Post Reply
User avatar
haravikk
Registered User
Posts: 292
Joined: Sun Apr 20, 2003 5:05 pm
Contact:

Finding unused integer values with MySQL

Post by haravikk »

Okay, I know I'm all problems lately but here it goes with another problem that is probably simple but I can't find the answer to myself:

Quite simply I want a MySQL query than can search a column of a table and return the earliest unused integer. For example, I have five rows in a table as follows:

Code: Select all

+---+---------+
| 0 |  item1  |
| 1 |  item2  |
| 3 |  item3  |
| 5 |  item4  |
| 6 |  item5  |
+---+---------+
Now, in this case I would query the first column (id) and in this case it should notice that '2' is unused and return it so it can be used. Once something has been added with the id value '2' the query can be run again this time finding that '4' is unused. When 4 is also used the query would find '7' as the next unused integer and so on. I'm not fussy about it finding '0' if the table is empty as simply getting PHP to look for an empty array returned by the query will do that just fine.

I have constructed a query but it does not properly work:

Code: Select all

SELECT a.shout_id 
FROM site_shoutbox a, site_shoutbox b 
WHERE (a.shout_id + 1) <> b.shout_id 
ORDER BY shout_id ASC 
LIMIT 1
This query in theory should look at the id in the shoutbox table, and add 1 to it then see if there is a row in the same table which already has that id, if so then try another, if not then return that result (and only that result).

This however does not work, I presume because the + 1 is not doing anything and the first time that a.shout_id is not equal to b.shout_id is when a.shout_id = 0 and b.shout_id = 1 so the query always returns 0.

Thanks,
Images in sigs! please.

DoD
Registered User
Posts: 360
Joined: Sat Aug 30, 2003 11:32 am

Re: Finding unused integer values with MySQL

Post by DoD »

try

Code: Select all

SELECT a.shout_id 
FROM site_shoutbox a, site_shoutbox b 
WHERE ((a.shout_id + 1) <> b.shout_id) AND (a.shout_id <> 0)
ORDER BY shout_id ASC 
LIMIT 1

User avatar
haravikk
Registered User
Posts: 292
Joined: Sun Apr 20, 2003 5:05 pm
Contact:

Re: Finding unused integer values with MySQL

Post by haravikk »

Thanks for the idea but it doesn't work, it simply returns 1 instead of 0
Images in sigs! please.

sparkster
Registered User
Posts: 182
Joined: Mon Jan 05, 2004 1:18 am

Re: Finding unused integer values with MySQL

Post by sparkster »

I might have misunderstood the question but what about

Code: Select all

select
	max(shout_id) + 1 as new_shout_id
from
	site_shoutbox

Roberdin
Registered User
Posts: 1546
Joined: Wed Apr 09, 2003 8:44 pm
Location: London, United Kingdom

Re: Finding unused integer values with MySQL

Post by Roberdin »

The script is supposed to find the lowest unused integer => 0.
Rob

User avatar
haravikk
Registered User
Posts: 292
Joined: Sun Apr 20, 2003 5:05 pm
Contact:

Re: Finding unused integer values with MySQL

Post by haravikk »

Thanks for the idea sparkster but that's not it either, in the example info I gave in the first post that query would return 7 instead of 2.

Thing is that I'm certain that the query I posted at the start should work (theoretically :)) but it doesn't which suggest that MySQL isn't doing anything with the + 1 to a.shout_id which is annoying.

I'm really looking for a simple way to do this, currently I have a very nasty system which loads the entire shout_id column then goes through it looking for an unused integer, it works and it works perfectly but I'd rather not have a loop which can be 500+ repeats long. I'm sure that a single MySQL query should do it but if there is a simple and efficient way to do it in MySQL AND PHP then that's good too.

Here is the code I have now:

Code: Select all

function unique_id ($dbtable, $dbfield)
{
	// Find a unique row ID from the given table

	global $db, $dbprefix;

	$sql = "SELECT " . $dbfield . "   
		FROM " . $dbprefix . $dbtable . "  
		ORDER BY " . $dbfield . " ASC";
	if ( !($result = $db->sql_query($sql)))
	{
		error_message ('db', $dbtable);
	}

	$tables_row = array();
	while ( $row = $db->sql_fetchrow($result) )
	{
		$tables_row[] = $row;
	}

	if (!$tables_row)
	{
		$free_id = '0';
	}
	else
	{
		$free_id = 'unfound';

		$i = $current_id = '0';

		while ( ($i < count($tables_row)) && ($free_id == 'unfound') )
		{
			if ($tables_row[$i][$dbfield] == $current_id )
			{
				$current_id++;
				$i++;
			}
			else
			{
				$free_id = $current_id;
			}
		}

		if ($free_id == 'unfound')
		{
			$free_id = $tables_row[count($tables_row) - 1][$dbfield] + 1;
		}
	}

	return $free_id;
}
Images in sigs! please.

User avatar
psoTFX
Registered User
Posts: 1984
Joined: Tue Jul 03, 2001 8:50 pm
Contact:

Re: Finding unused integer values with MySQL

Post by psoTFX »

Perhaps I'm missing something but I don't see how you can do this with a singular query ... you're basically asking it to compare rown with row(n+1). A subselect may be able to do it, not sure, haven't given it much thought. I wonder actually why you're bothering to do this at all? Just use auto_increment for the id field, it (should) reuse missing values as and when necessary.

Roberdin
Registered User
Posts: 1546
Joined: Wed Apr 09, 2003 8:44 pm
Location: London, United Kingdom

Re: Finding unused integer values with MySQL

Post by Roberdin »

haravikk wrote:

Code: Select all

$tables_row = array();
	while ( $row = $db->sql_fetchrow($result) )
	{
		$tables_row[] = $row;
	}
Correct me if I'm wrong but couldn't this large block be simplified with

Code: Select all

$tables_row = $db->sql_fetchrowset($result);
(Though of course that's not important).

If you choose to ignore psoTFX's advice for whatever reason, wouldn't something like this worK? (In theory it should force the second table to be one row more than the row from a as the row from table b will start at 1 and the row from table a will start at 0.)

Code: Select all

SELECT a.shout_id + 1
FROM site_shoutbox a, site_shoutbox b
WHERE a.shout_id + 1 <> b.shout_id
AND b.shout_id <> 0
LIMIT 1
Rob

DoD
Registered User
Posts: 360
Joined: Sat Aug 30, 2003 11:32 am

Re: Finding unused integer values with MySQL

Post by DoD »

Is it possible to use a break in the while() so, for example, the result is 2 out of 500, it doesnt cycle through the 498 unnessecarily.

That code could be simplified....

ill give it a go one day this week.

psoTFX - not always does auto increment fill in the gaps. My MySQL DB on my home pc for sure doesnt - it inserts the next number set within the table data. I'm using version 4.0.14

User avatar
psoTFX
Registered User
Posts: 1984
Joined: Tue Jul 03, 2001 8:50 pm
Contact:

Re: Finding unused integer values with MySQL

Post by psoTFX »

re: mysql auto_increment ... yes, that's quite correct ... however when the maximum integer for that field type is reached AIUI it'll wrap back to the first available integer.

Post Reply