bonaca bonaca - 4 months ago 13
SQL Question

how to add +1 in a sort column

I have a table column named

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

When I insert a new row, how can I automatically add an increment
+1
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
auto
(
id
in my case).

Another solution would be to insert the new row as the first row, with the value
1
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)') ;
$stmt->execute(array(
':title' => $title,
':content' => $content,
':sort' => ???,
));
header('Location:admin.php');
exit;

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

Answer

Try This Query

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