How to move rows in mysql

Question

So, I have a table that includes 3 columns: ID, Name and ParentID. Something like that:

ID | Name | ParentID 1 Root NULL 2 Parent #1 1 3 Parent #1 1 4 Parent #1 1 5 Parent #1 1 6 Child #1 2 7 Child #1 2 8 Child #1 3 9 Child #1 4 10 Child #1 5 11 Child #1 5 

After some manipulations using strings by the user, Ajax sends an array with a new order of strings by their identifiers. For example, it can be 4, 10, 2, 3, 11, 1, etc.

Question: what queries / queries can I use so that I can reorder rows in my table? So, I only have a row id and nothing else. And I need to move all the data in the row: with the name and ParentID (and not just with the ID). I want to get something like this:

 ID | Name | ParentID 1 Root NULL 9 Child #1 4 5 Parent #1 1 2 Parent #1 1 3 Parent #1 1 6 Child #1 2 7 Child #1 2 8 Child #1 3 2 Parent #1 1 10 Child #1 5 11 Child #1 5 

Thank you very much.

Answer 1

In my opinion, you cannot do this without the extra order column

For this purpose you will need to create a new order column.

Then refresh the table as you pass the array.

 foreach($passed_array as $order => $val){ $q = "Update table SET `order` = $order WHERE ID = $val"; // run query here } 

EDIT

And when displaying data in the front, the query will look like

 "SELECT * FROM table `Order` BY `order` ASC" 

Answer 2

I think you should create a new database column ( weight for example, type INTEGER) that will indicate the row order. This should be the most optimal solution.