Re-order database rows using PHP

It have two table parent table and children table
Parent Table

id page-title page-content

Children Table (where parent_id = id) id from parent table

child_id parent_id child-title child-content

Im using PDO mysql for data fetch


above picture is dashboard where data is displayed from database
now user should be able re-order the rows either by input value or drag drop

how can user can re-order the row from dashboard

what will be the process for this re-ordering rows. i count find the solution

1 Like

solve with the concept of picture-3, still wondering if i could do it with drag and drop

How is the order determined? From something that’s already there, or do you want to allow the user to choose a “display order” that isn’t based on a normal sort on existing data? If the latter, you’ll probably need to add a column to the table that contains the users specific chosen sequence, and use that in your ORDER BY clause.

As for drag-and-drop, I would imagine that’s more likely to be something you would need to use Javascript to achieve, at least on the front end.

1 Like

It is not easy. But yes for the UI drag-and-drop can be useful. There are other options; you have seen other options.

The hard part is the database. I struggled with a similar problem extensively. I am going to try to make it seem really hard to motivate you to search for previous answers so you can prove me wrong. You need to do that.

One problem I had is the database update. If you store the order number in the database and if you want to avoid replacing all the data (to avoid bad performance) each time the order number is changed then a likely result will be frustration.

The database is the hard part. I said that already, but it needs to be emphasized. If it is possible to store the order number outside the database then that can make things easier, or in a separate table.

1 Like

So you want to order children of a given parent only? Each parent has their own order set?

Not so difficult really.

Let the user play around with the order in the front end with Javascript. Once they’re settled on and committed to their order,

INSERT INTO children (child_id,order) VALUES (X1,O1), (X2,O2).... ON DUPLICATE KEY UPDATE order=VALUES(order)

The Javascript should be able to determine the order by the row’s position within the DOM. If you want to add some fanciness for checking the original order vs the current one, it could cut down on the amount of rows the query messes with.

1 Like