how do you run an insert query for the next available row
Moderator: General Moderators
how do you run an insert query for the next available row
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
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
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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
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
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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
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++;
}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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.
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..
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
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