John Davidson

Message:

I list of tasks. Everyone task has Id in DB. Id column has setted up autoincrement.

I made this list sortable via jquery by sortable() function from jquery ui.

Now if I change order of times I change order of Id in DB. But bcz of UPDATE does not work when values of Id are same - I need to change ordering one time from 200 and second time from 0. Is it good solution? Ans is it okay to change Id or shoula I create another column for this purpose?

Thanks

$i = 0;



// load all data from db in ascending order bcz of find first id value

$first_index = $database -> select (

    'items', // table

    'id', // column

    [ 'ORDER' => ['id' => 'ASC' ] ]// ordering

    );



// if first id value is equal to 0 then set up i as 1000 bcz if there are same values UPDATE table below will not work

if ( $first_index[0] == 0 ) {

    $i = 200;

} else if ( $first_index[0] == 200  ) {

    $i = 0;

};



// convert array (item[]=1&item[]=2) to separate items

foreach ($_POST['item'] as $value) {

    // Execute statement:

    $affected = $database -> update ('items',

    [ 'id' => $i ], // SET (set id as $i which is increasing for each value)

    [ 'id' => $value ] // WHERE

    );

$i++;

}

---

My Answer:
> It is generally not recommended to change the IDs in the database when reordering a list. The primary key (ID) in a database table should ideally be a unique identifier for each record and should not be changed frequently.

Instead of changing the IDs, you can add a new column in your database table to store the order of the tasks. This way, you can update the order of tasks without affecting the primary key IDs. You can then use this new column to display the tasks in the desired order.

By adding a new column for ordering, you can maintain the integrity of your database and avoid potential issues that may arise from changing primary key IDs. This approach is more flexible and allows you to easily update the order of tasks without impacting the database structure.