How to add +1 in a sort column

Question

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();
}

If the id already is auto-increment, what additional value is given by the sort-column? — piet.t, Jul 05 '16 at 12:13
You could count the existing records or get max of existing sort and use this in the same SQL to insert — ChrisBint, Jul 05 '16 at 12:13
@piet.t later I have a procedure to change values in the `sort` column according to a given php array, and it's not possible with `id` column, because it has the primary key. — qadenza, Jul 05 '16 at 12:16
I think I might consider restructuring my data in some way. Even if you can achieve what you're looking to do in this instance, it seems to me that you will run into more issues maintaining this kind of data structure in the long term costing more effort than restructuring at this stage. — Henders, Jul 05 '16 at 12:21
@Henders I'm not sure what you mean, what is wrong with maintaining a sort order that the user can manipulate? — jeroen, Jul 05 '16 at 12:25
@jeroen I'm not suggesting that a user shouldn't be able to manipulate the order, I was just saying, if it was me, I might be more inclined to go with a different solution that didn't rely on counting table rows or summing one to the last inserted row. As an example, you could implement some default order (by content creation date assuming you had such) and then use weighting to adjust the final output. — Henders, Jul 05 '16 at 12:30

score 1 · Answer 1 · answered Jul 05 '16 at 12:18

1

try {
$stmt = $db->prepare('INSERT INTO ' . $table. ' (title, content, sort) VALUES (:title, :content, :sort)') ;
$stmt->execute(array(
':title' => $title,
':content' => $content,
':sort' => $sort+1
));
header('Location:admin.php');
exit;

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

answered Jul 05 '16 at 12:18

Kalaivani M

1,250
15
29

how ca I get `$sort` value ? – qadenza Jul 05 '16 at 12:22
`$count = mysql_num_rows(SELECT sort from $table order by sort desc);`by using this query calculate the no of rows if rows 18 means add the sort value+1 – Kalaivani M Jul 05 '16 at 12:45

Nithee · Accepted Answer · 2016-07-05T12:22:50.850

1

Try This Query

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

edited Jul 05 '16 at 12:22

answered Jul 05 '16 at 12:19

Nithee

300
1
12

score 1 · Answer 3 · answered Jul 05 '16 at 12:26

1

You can retrive last value and sotre that value by adding one.

$query=SELECT user_id, value FROM My_TABLE ORDER BY user_id DESC LIMIT 1;

answered Jul 05 '16 at 12:26

Sayali Shete

11
1

score 0 · Answer 4 · answered Jul 05 '16 at 12:21

I am curious as to what you are using sort for. You can just order by the existing id to sort the rows. Since that column is already auto increment as you have said. Having another column duplicate what is already available is generally poor database design. Maybe I am not fully understanding what you are trying to do, could you give a little more explanation as to what it is you are trying to accomplish with the sort column?

If you are set on setting this up the way you specified what you would have to do is make a query and order by the column sort descending.

SELECT sort from $table order by sort desc. then take that value add one then insert the value into the new one.

How to add +1 in a sort column

4 Answers4