Page 1 of 1
how do you run an insert query for the next available row
Posted: Mon Apr 10, 2006 6:55 pm
by Neller
title sounds a bit strange but wasnt sure how to put it but here goes
in my database/table i have the normal primary key with auto increment
well say i had 10 records keys 1-10 when i delete a record say number 7 i would have the rows 1,2,3,4,5,6,8,9,10 then when i add a new insert it inserts it as number 11 how can i do it so i inserts it into the next available key which would be 7 in this case
thanks in advance and sorry if the explanation isnt great
Posted: Mon Apr 10, 2006 8:02 pm
by RobertGonzalez
Autoincrement does not allow for this. To get what you want you would have to take autoincrement out and manually change each primary key OR write a query to find your gaps and then use the lowest possible gap as your next key.
Any particular reason why you want to manipulate auto-generated data?
Posted: Mon Apr 10, 2006 8:55 pm
by Neller
thanks alot, what is the code/function that finds the next possible gap ive looked all over cant seem to find it
the reason im trying to do this i have created a small football game
i have my php script reading the teams out the database onto a formations/tactics page and i need all the players numbered 1- (whatever many there are) so when i click
on a player and swap him with another it updates the players number in the database
and switches his place on my formations page (its the only way i can think to do this)
now its easy when i first do it as i can just number the players 1- (whatever)
but after selling/buying players all the numbers would be out of sorts so i was hoping when i bought a player i could use a query that sets his number as the lowest key not in use
once again sorry if this isnt a very good explination im pretty new to forums i normaly just find what i need in books etc but this really has me lost
cheers
Posted: Mon Apr 10, 2006 11:22 pm
by RobertGonzalez
Sounds to me like you need a record_id and a player_id. I would seriously recommend that you do not mess with your autoincrement values. Let that be what it is, your primary key/index. Add another field to the table and use that field for modification. Just a suggestion.
Posted: Tue Apr 11, 2006 5:53 am
by Neller
ok dont no if this is the best way but it works here is how i did it
1st created a new row in my table called player_id to give all the players a number and made it unique so none cna end up with the same number then made the following code up to find the next lowest gap and fill it
Code: Select all
$i = 1;
//get the next available number
$i = 1;
while($i <30) {
$query = "SELECT player_number
FROM `team1` WHERE player_number = '$i' LIMIT 1";
$result = @mysql_query($query);
$row = mysql_fetch_array($result,MYSQL_ASSOC);
if(!$row) {
$player_number = $i;
$i = 30;
}
$i++;
}
Posted: Tue Apr 11, 2006 6:19 am
by rsmarsha
If a players moved to anothe club, you could run another query after the move query. The 2nd query would take all players with id's higher than the one who's moved and -1 from them. This would reorder the id's and not leave a gap. Of course this requires a new field without an auto increment. I did a similar thing with an order column for product categories.
Posted: Tue Apr 11, 2006 8:43 am
by RobertGonzalez
Technically teams are changing, not players, right? Why not leave the player_id alone (since it is unique to the player) and assign a team_id to the player. Then when the player moves to a different team, update that one id. This adds a second layer of protection in that a player cannot play for two teams so they will always have only one team id associated with the and you are not changing your player_id.
Posted: Tue Apr 11, 2006 10:13 am
by infolock
Everah wrote:Autoincrement does not allow for this. To get what you want you would have to take autoincrement out and manually change each primary key OR write a query to find your gaps and then use the lowest possible gap as your next key.
Any particular reason why you want to manipulate auto-generated data?
Another alternative is to create a seperate table called PrimKeys or something of that nature.
It would contain 4 fields : database_name, table_name, table_field, and Current_ID
You could then have that table be your own personal auto-incrementing table. IE: Before every row, you would add a key to the table, and it would return the next available id. Then, just insert it into the table you were wanting to do-away with auto-incs.
This will save lots of headaches down the road. Just a thougt..
Posted: Tue Apr 11, 2006 1:37 pm
by Neller
Cheers guys,
The way i did it is actually working ok at the min so not going to mess with it just yet but i will keep all your ideas down just incase i end up having problems with the way i did it
im not actually changing the player_id that is unique to the player im chaning the player_number row so its always 1-> (how ever many teams are at the club)
then when i buy a player it will take up whatever gap i have in the numbers