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. :(
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 »

Roberdin that query doesn't work, it's much the same as the one I posted earlier and the problem seems to be that the + 1 in the WHERE part is being ignored entirely. The query returns the same value regardless of anything, in your case '2'.

The query should theoretically look at each row in the shout_id column and add 1 to it and effectively call it 'a', then look to see if there are any rows which already have the value 'a', if not return it, if they do then try a different value.
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 »

Did you read what I wrote?

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 »

Well I personally can't see why you care about unused values, especially as they will be used up eventually if you use enough values (as psoTFX said) but if you do care so much, it would probably be less load to write a script than to come up with a query to do it. :P (I welcome corrections)

Code: Select all

<?php 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 = $db->sql_fetchrowset($result);

   if( !($tables_row) )
   {
      return 0;
   }
   else
   {

       // Go through every row, and compare it with the next one. If this row
       // Plus one ISN'T the next row's value, then we know that there's an
       // empty value waiting to be used there. We do check the last row because
       // when the script tries to check the (non-existant) row after it it will
       // find 0 and therefore output one more than the last row.
         
       for( $i = 0; $i < count($tables_row); $i++ )
       {
           if( ($tables_row[$i][$dbfield] + 1) != intval($tables_row[$i + 1][$dbfield]) )
           {
                return intval($tables_row[$i][$dbfield] + 1);
           }
       }

   }

   // Huh!? Something weird's happening if we haven't been returned yet...
   // Better take care of it just in case.
   
   return count($tables_row);

}
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 »

Actually someone has suggested a query which seems to be working perfectly over at the Dev Shed forums:
Click here to view the solution

The reason is that I really want to be able to replace rows in a table.
A shoutbox I think is probably a bad example but it wasn't an important table so I decided to mess around with it.

The purpose behind this unique id finder is that I'm trying to get the front end for a gaming server software up and it has a very strict file structure which I'm trying to adhere to so that no kind of conversion between the row IDs is necessary.
I didn't program the server software so I can't really say more on why it has to be like that but as far as my part is concerned it's a million times easier to stick to its standards than try to convert correctly all the time.
Images in sigs! please.

Post Reply