Reputation: 301
Is this possible using only mysql:
Insert a new column and example - "pos". Every row in the table has to have unique,incresing value of "pos" like - 1,2,3,4,5.
In php this would be an easy job:
$query = "SELECT * FROM example";
$result = mysql_query($query) or die(mysql_error());
$counter = 0;
while($row = mysql_fetch_array($result)){
mysql_query( "UPDATE example SET pos = ".++$counter." WHERE id = ".$row['id']." );
}
Upvotes: 0
Views: 72
Reputation: 12538
As already suggested, the most efficient solution is to use auto incremental on pos.
Alternatively if your use case do not permit you, then try similar:
"UPDATE example SET pos = ((select max(pos) from example) +1) WHERE id
= ".$row['id']."
Upvotes: 1
Reputation: 2350
Give your pos
mysql field the auto_increment
attribute - this will index it, make it unique, and increment by 1 on each insert
Upvotes: 0
Reputation: 37243
yes you can do it like that
UPDATE example SET pos = pos+1 WHERE id = ".$row['id']."
Upvotes: 1