How to add to unused ID without auto_increment? 3

[AcidReignX]

I'm having a small problem with mysql - there is a particular ID field that I do not want to have increase automatically, but I would like to insert it to an unused location without having to do it manually.

for example, if the following fields are present:
| ID | Name | Miscellaneous |
| 00 | John | Other
| 02 | Fred | Other
| 08 | Jack | Other

How can I have a form using the post method that will automatically add a name and miscellaneous field to the first unused ID? (or any ID that isn't used... even if it is the next higher, that's still decent I guess - I just can't set the field to use auto_increment for other reasons)

The form would be something basic:
Name: <input type="text" name="name">
Other: <input type="other" name="misc">

It's the ID that I'm confused on...

 

[spookie]

You have to check manually which id is skipped and then insert a record .
something like
1)get max id in a table
2)use a for loop starting from 0 to max id increment by one
3)query db to check if the id is present in table
4)if not insert a record using insert statment mentioning which id to insert

Well there can be easier method than that, which you can get from other members.

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[jpadie]

i tend not to use auto_increment but instead generate a random number to use. i grab the number and populate (say) a form with it in a hidden field. i then use the "Replace" syntax in my sql statement (or insert with a duplicates check) in order to get around the reload and reenter problem (i tend to use autoprocessing pages).

 

[sleipnir214]

You can pick your own IDs. Just keep in mind that your algorithm for picking the "next" ID must keep concurrency into account.

Suppose that "a.php" is going to examine the table and generate a new ID. Users "A" and "B" both run "a.php" with the right timing that A's run of the script could pick a new ID, then B's run of the script could pick the same ID before A's run of the script can actually use it. You would then have a collision.

As jpadie has said, you could use a random ID-generating algorithm. Just make sure that the universe of random numbers from which you pick an ID is large enough to reasonably pick non-colliding IDs.

Most Linux systems I've come across have a program called uuidgen, which generates those UUIDs that Microsoft is so fond of. You can run that app externally and generate a time-based UUID that have an excellent chance of not colliding.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[kainaw]

Rather than query repeatedly, you could do this in one query (reading many rows). The following is a PHP function - so it should be easy to convert it to any other language:

function db_next_int($table, $id)
{
$result = mysql_query("select $id
from $table
order by $id");
$last = null;
while($row = mysql_fetch_object($result))
{
if($last == null)
$last = $row->$id;
elseif(($row->$id - $last) > 1)
return ($last + 1);
else
$last = $row->$id;
}
if($last == null) return 0;
return ($last + 1);
}

This will only do one query to the database. I assume you indexed the $id field since it should be the primary key. Otherwise, sorting on $id will be slow. Once a gap is found, the missing number is immediately returned.