i have a slight problem i have a member script with a database when they apply for membership it INSERT INTO the next record after the last one but how can i get it to INSERT INTO the first available space as i am constantly deleting old members so spaces arise?
Insert into first available space
i see this type of question come up from time to time and i always have to give my stock answer: don't. don't worry about gaps in the primary key field of your tables, that is perfectly normal. PK's are designed to be totally unique values. hence, once a PK is used is should never be reused...ever. this is a basic part is relational database design. this can become very messy when you have multiple related tables that rely on each other's PK's. again, the simple answer is: don't worry about it.
ok then fair enough thanks 
Do you mean that you want them to be added into an existing sequence? I.e if you have deleted number 4 you want the next member to "take over" that id?
Generally speaking; the next "available space" is always where you end up inserting. From there it is just a question of sorting.
johanny currently if i delete a member say member 4 then the database goes member 1 , 2, 3 , 5 what i wanted to do is get eh next pplication to fil slot 4 got me?
In that case I have to agree with 'devinemke', just don't...
You can end up in all sorts of trouble if you start messing around with the prim.keys
homer09001 wrote:johanny currently if i delete a member say member 4 then the database goes member 1 , 2, 3 , 5 what i wanted to do is get eh next pplication to fil slot 4 got me?
...and what if there is another record in another table using that field as a foreign key? that record is assuming that "4" refers to the record that you deleted. what if there are 10 other tables all relying on that "4" to mean a specific record that is no longer there. this can quickly turn into a data mintenance nightmare. don't do it.
devinemke wrote:...and what if there is another record in another table using that field as a foreign key? that record is assuming that "4" refers to the record that you deleted. what if there are 10 other tables all relying on that "4" to mean a specific record that is no longer there. this can quickly turn into a data mintenance nightmare. don't do it.
ahh see thats wqhere youve lost me havent the fogiest what a foreign key is all i can guesss is that it has somnething to do with linking one tablel to anouther lol
thanks for your help anyway
5 months later
Wandering into this several months later I just need to observe something:
devinemke wrote:...and what if there is another record in another table using that field as a foreign key? that record is assuming that "4" refers to the record that you deleted. what if there are 10 other tables all relying on that "4" to mean a specific record that is no longer there.
In that case the whole foreign key relationship would be broken: it should not be possible to delete a record that other records are relying on to still exist; or if the record is deleted, all those dependents should be deleted as well.
@weed:
Yes, but now in practice... 'Should' is not always 'is', now is it 
6 years later
The code I'm about to give you should not be used if your joining 2 tables using primary keys. You could modify it to be able to use if two tables are joined but this code is for 1 table. There are two ways to do it as far as I know.
Way 1: It finds the missing number from 1,3,4,... and turns the 3 into a 2, then the 4 into the 3. This uses a lot of database resources if the database table holds a lot of data.
Way 2: It inserts the data into the last field of the table. In this case it's 5 because there are only 4 things in the table. It finds the missing number from 1,3,4,... and turns the Primary Key field that was inserted into the table into a 2.
In both of these ways, it resets the primary key autoincrementor.
<?php
$user = "EnterUserNameHere";
$password = "EnterPasswordHere";
$database = "Enter DatabaseNameHere";
$host = "EnterHostNameHere";
$table = "EnterTableNameHere";
$primaryKeyID = "EnterPrimaryKeyIDFieldNameHere";
$name=$POST['txtContactName'];
$email=$POST['txtContactEmail'];
$question=$_POST['txtContactQuestion'];
$insert = "INSERT INTO $table VALUES('NULL','$name','$email','$question')";
mysql_connect($host,$user, $password);
@mysql_select_db($database) or die( "Unable to select database");
///////////////////////////////////////////////
//////////DO NOT EDIT BELOW THIS LINE//////////
///////////////////////////////////////////////
////////WAY 1////////
////////////////////////
/////Primary Key Gap Filler Code/////
$countFunction = "SELECT COUNT('$primaryKeyID') AS num FROM $table";
$data = mysql_query($countFunction) or die(mysql_error());
$row = mysql_fetch_assoc($data);
$numRows = $row['num'];
if ( $numRows > 1 )
{
$currentRow = 1;
$PKIDvalue = $currentRow;
while ($currentRow <= $numRows)
{
$rowIDquery = mysql_query("SELECT FROM $table WHERE $primaryKeyID = $PKIDvalue");
$rowID = mysql_num_rows($rowIDquery);
while ($rowID < 1)
{
$PKIDvalue = $PKIDvalue + 1;
$rowIDquery = mysql_query("SELECT FROM $table WHERE $primaryKeyID = $PKIDvalue");
$rowID = mysql_num_rows($rowIDquery);
if ($rowID != 0)
{
mysql_query("UPDATE $table SET $primaryKeyID=$currentRow WHERE $primaryKeyID = $PKIDvalue");
}
}
$currentRow = $currentRow + 1;
$PKIDvalue = $currentRow;
}
}
$reset = "ALTER TABLE $table AUTO_INCREMENT = 1";
mysql_query($reset);
/////Insert Input into Database/////
mysql_query($insert) or die ("error updating database");
/////Redirect to View Page/////
echo "<meta http-equiv=\"refresh\" content=\"0;URL=../View/View.php\">";
mysql_close();
?>
///////////////////////////////////////////////
//////////DO NOT EDIT BELOW THIS LINE//////////
///////////////////////////////////////////////
////////WAY 2////////
///////////////////////
/////Primary Key Gap Filler Code/////
$countFunction = "SELECT COUNT('$primaryKeyID') AS num FROM $table";
$data = mysql_query($countFunction) or die(mysql_error());
$row = mysql_fetch_assoc($data);
$numRows = $row['num'];
if ( $numRows > 1 )
{
$currentRow = 1;
while ($currentRow <= $numRows)
{
$rowIDquery = mysql_query("SELECT * FROM $table WHERE $primaryKeyID = $currentRow");
$rowID = mysql_num_rows($rowIDquery);
if ($rowID < 1)
{echo $rowID; echo "<br/>";
$query = "SELECT $primaryKeyID FROM $table ORDER BY $primaryKeyID DESC LIMIT 0,1";
$result = mysql_query($query) or die(mysql_error());
$grab = mysql_fetch_assoc($result);
$max = $grab[$primaryKeyID];
echo $max;
mysql_query("UPDATE $table SET $primaryKeyID=$currentRow WHERE $primaryKeyID = $max");
}
$currentRow = $currentRow + 1;
}
}
$reset = "ALTER TABLE $table AUTO_INCREMENT = 1";
mysql_query($reset);
/////Insert Input into Database/////
mysql_query($insert) or die ("error updating database");
/////Redirect to View Page/////
echo "<meta http-equiv=\"refresh\" content=\"0;URL=../View/View.php\">";
mysql_close();
?>