bonaca bonaca - 1 year ago 59
SQL Question

how to add +1 in a sort column

I have a table column named

which is using for sorting rows (1 2 3... etc).

When I insert a new row, how can I automatically add an increment
in this column?

For example, if the last row value is 20, the new should be 21.

I tried to set this column as auto-increment but phpMyAdmin says that only one column can be
in my case).

Another solution would be to insert the new row as the first row, with the value
in the sort column, but how could I in this case change all another values bellow?

Any help?

try {
$stmt = $db->prepare('INSERT INTO ' . $table. ' (title, content, sort) VALUES (:title, :content, :sort)') ;
':title' => $title,
':content' => $content,
':sort' => ???,

} catch(PDOException $e) {
echo $e->getMessage();

Answer Source

Try This Query

"INSERT INTO $table (title, content, sort) VALUES ($title, $content,(SELECT MAX(sort)+1 FROM $table))"