How to - sql update sort order after delete

Question

The code below works fine but I'm wondering if there are alternative ways of handling this. Basically, after removing an entry, I need to update the sort order.

$sql = 'DELETE FROM `ee_blog_category` WHERE id = '.$category_id;
mysqli_query($con,$sql);
if(mysqli_error($con)){
    echo mysqli_error($con);
}
// update cat sort
$i = 1;
$sql = 'SELECT * FROM `ee_blog_category` ORDER BY `sort`';
$result = mysqli_query($con,$sql);
while ($row = mysqli_fetch_array($result)){
    $sql1 = 'UPDATE `ee_blog_category` SET `sort` = '.$i.' WHERE `id` = '.$row['id'];
    mysqli_query($con,$sql1);
    $i++;
}

Answer 1

Yes, there is a different (I think better) way:

1. get the sort value of the deleted item (of course before you delete it).
2. Update all the items which have a greater sort value with sort=sort-1

In your case this would be something like:

$sql = "Select sort from ee_blog_category where id=$category_id";
// execute, fetch, catch errors...
$sort_original = $row['sort'];

// now update all the others
$sql = "UPDATE ee_blog_category SET sort=sort-1 where sort > $sort_original"; // maybe add a filter here (by group f.e.)
// execute, check for errors.

NOTE:
You could be open to sql-injection.
Better use prepared statements!