CODEWITHSUNDEEP
phpsql
Koala
Koala

Reputation: 5543

SQL Query increment 1 from last row?

since you can't auto increment two columns what would the Query be to get the last row of the table and +1 value of the column to insert a new row.

For example I have a table named players

looking abit like this:

id    player_id    player_name
 1      15           name1
 2      16           name2
 3      17           name3

I'm trying to make an admin panel to create a player from a form, the column 'id' auto increments but I also need player_id to increment too, but id and player_id are not the same as each other.

A query similar to this (I made this up so you can understand a little better)

$sql="select top 1 * from `players`  order by id desc";
$result =mysql_query($sql);
while ($data=mysql_fetch_assoc($result)){
    $playerid=$data['player_id'];
    Insert into `players` (`player_id`,`player_name`) VALUE ('$playerid' + '1', 'name4');

}

Upvotes: 5

Views: 4321

Answers (3)

VijayS91
VijayS91

Reputation: 1531

Try This Code:

    $sql="SELECT player_id FROM `players` ORDER BY id DESC LIMIT 1";
    $result =mysql_query($sql);
    while ($data=mysql_fetch_assoc($result)){
        $playerid=$data['player_id']+1;
       }
mysql_query("Insert into `players` (`player_id`,`player_name`) VALUES ('".$playerid."','name4')");

Upvotes: 4

Barmar
Barmar

Reputation: 782683

The following query will assign the next available player_id when inserting.

INSERT INTO `players` (`player_id`, `player_name`)
SELECT MAX(`player_id`)+1, 'name4'
FROM `players`;

Upvotes: 3

Sibu
Sibu

Reputation: 4617

Try this way 

  $sql="SELECT * FROM `players` ORDER BY id DESC LIMIT 1";
    $result =mysql_query($sql);
    $data=mysql_fetch_assoc($result);
    $playerid=$data['player_id']+1;

mysql_query("Insert into `players` (`player_id`,`player_name`) VALUES ('".$playerid."','name4')");

Upvotes: 0

Related Questions