how do you run an insert query for the next available row

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Neller
Forum Newbie
Posts: 18
Joined: Sun Mar 27, 2005 4:56 am

how do you run an insert query for the next available row

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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?
Neller
Forum Newbie
Posts: 18
Joined: Sun Mar 27, 2005 4:56 am

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Neller
Forum Newbie
Posts: 18
Joined: Sun Mar 27, 2005 4:56 am

Post 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++;
}
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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..
Neller
Forum Newbie
Posts: 18
Joined: Sun Mar 27, 2005 4:56 am

Post 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
Post Reply