PHP/MySQL Reorder Menu with Position set in table

Question

In MySQL I have a table with a name, id, position, and content values. The content is loaded onto a page in php by querying by id. The position is used in php to create a dynamic menu with the menu items ascending by position value.

So for example, say There were 3 rows with each row having a position value of 1, 2, or 3. I am trying to build a form to allow the user to change the position of the menu, items, or add a new one. So if the row in position 3 wants to be changed to position value 1, then 2 needs to become 3 and 1 needs to become 2. Or if a new item is added, the position its taking and all below it need to be shifted up a value.

What would be the best way of approaching this? Any suggestions would be appreciated.

Is your question about how to implement the reorder in the database or how to show the reordered menu on the page? — markus
– markus, Commented Jun 6, 2012 at 8:17

Joe · Accepted Answer · 2012-06-06 07:44:21Z

1

If you add new item- frst update all items with position value equal or more:

UPDATE MenuItems SET position = position + 1 WHERE position >= newItemPosition;

If you are updating - update only item with position higher than higherItemPosition (they will only switch places).

UPDATE MenuItems SET position = position - 1 WHERE position = higherItemPosition;

If newItemPosition is updated to lower value I would do sth like that:

UPDATE MenuItems SET position = position + 1 WHERE position = lowerItemPosition;

edited Jun 6, 2012 at 7:44

answered Jun 6, 2012 at 6:31

Joe

2,6136 gold badges40 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mitch Over a year ago

This is on the right track but not working flawlessly. Adding a new item works. However updating old ones runs into some problems. I'll need to take some time to work through the logic and try a few things, but this answer might get me there.

Joe Over a year ago

If position column is UNIQUE make id in a good order, but I'm sure you know it. Oh - I made mistake in higherItemPosition query (there was >= and should be simply = - corrected)

Jim · Accepted Answer · 2012-06-06 06:31:30Z

0

Two ideas come to mind:

have the menu as an AJAX loaded div and then react to the change and reload the div, which means you could rerun the SQL

load the answers into a javascript array and present them that way as you then have drag change abilities.

answered Jun 6, 2012 at 6:31

Jim

634 bronze badges

Comments

Miguel Pérez · Accepted Answer · 2012-06-06 08:14:27Z

Before we swarm the database with queries, create indexes (e.g. primary key) that covers the position field, update several rows with their index entries per change, and so on, let's consider the following:

How long would a menu get, in the worst case?
Do you really need to obtain individual rows from the menu, or whenever you need to display a menu you need to SELECT all of its rows?

Most often, these cases can be solved with a much simpler, pragmatic approach that will also be far easier for you as a programmer. If the menu isn't going to be too long and you probably need all of it every time you need to read it, why not keep it as a list (of items of whatever type) in your program, and serialize it with whatever method (e.g. JSON or Python Pickle) before storing it in the database as a single row with a text field? If the menu is in a list, reordering its elements will be pretty simple, a read operation for the menu would demand a single row SELECT for the database, and a write operation would require a single UPDATE, without caring for keeping a Position field updated.

Collectives™ on Stack Overflow

PHP/MySQL Reorder Menu with Position set in table

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related