CODEWITHSUNDEEP
mysqlsql
Vlad Turak
Vlad Turak

Reputation: 6374

How to reorder records / rows in a MySQL table

I have the table, that has three columns: ID, Name and ParentID. Let's consider this structure:

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 reordering the rows by a user, the JavaScript on the client-side will send an array with the new order of the rows by their IDs. For example, it can be [4, 10, 2, 3, 11, 1].

What query/queries should I use so that I could change the order of the rows in my MySQL table? I want to get something like this:

ID |   Name   | ParentID
 1     Root       NULL
 9   Child  #1     4
 5   Parent #1     1
 4   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

Upvotes: 6

Views: 2898

Answers (2)

Umair Ayub
Umair Ayub

Reputation: 21371

In my view, you can't do it without having an extra column order

You will have to create a new column order for this purpose.

Then update the table as array is passed.

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

EDIT

And when displaying data at frontend, select query will be like

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

Upvotes: 2

Jazi
Jazi

Reputation: 6752

I think You should create new database column (weight for example, INTEGER type) which will specify Your row order. It should be the most optimal solution.

Upvotes: 0

Related Questions