[SOLVED] PHP to change database order

[xtopolis]

Hi,

 

I have a database that contains filemask information, each row has an auto_increment id.

 

I want to give a user the ability to reorder these masks either by changing the auto_inc id, or a separate column... but I'm not sure how to do that? 

 

If I have:

 

[1] Filemask 1

[2] Filemask 2

[3] Filemask 3

 

And now I want [3] to become [1], and have [1] and [2] shift down to become [2] and [3], how can I do this with php, or even mysql?

[dannyb785]

Don't allow the user to alter the primary auto incremented key value. Create a new new column named mask_order or something similar and  allow the user to change those values and then when you display them all, do "ORDER BY mask_order" in your query

[xtopolis]

Yea, I figured as much, but my problem is really "how" to reorder that mask_order column.

 

[iD][mask_order][name]

 

[3][1][Filemask 3]

[1][2][Filemask 1]

[2][3][Filemask 2]

[4][4][Filemask 4]

 

If I want Filemask 1 to become 3rd in the list ( [1][2][Filemask 1] --> [1][3][Filemask 1]) but Filemask 2 already has a position 3, and I can't just pop it to the end of the list because it should be above Filemask 4.... so how can I take those [mask_order] ids and have them renumber themselves after I .. insert it into the position I want, such as "above filemask 4" or "below filemask 2"; and how would I do this insert?  you kinda get what i mean?

[cooldude832]

does positioning matter or just that it is random?

[Barand]

if you want filemask1 moved to 3, then filemask2 moves up to 2, occupying the now vacant position

[xtopolis]

Position matters, if a filename might match both row 1 and row 2 (due to wildcards), if should pickup 1 first, and then stop processing.  I apply a sort by ID(the auto_inc) atm, but I do not have the reposition logic yet, and that has brought me here.

 

@Barand, how do I move it as such?  Does mysql have an insert at [point] and then does it allow me to re number my sort column based on their position (top to bottom, numbering down or w/e)

[Barand]

[pre]

id    position    name                                id    position    name         

----+-----------+----------------+                      ----+-----------+----------------+

1        1        widget                                1        1        widget

6        2        gizmo            ----+                5      3->2        thing

5        3        thing                |                4      4->3        wotsit

4        4        wotsit              |                2      5->4        stuff

2        5        stuff                +--------->      6      2->5        gizmo

3        6        wodyacallit                          3        6        wodyacallit

[/pre]

In the above example, gizmo is moved from pos 2 to pos 5

 

UPDATE table SET position = position - 1 WHERE postion BETWEEN $movefrom+1 AND $moveto

UPDATE table SET position = $moveto WHERE id = 6;

 

Logic is reversed if move is upwards

[xtopolis]

UPDATE table SET position = position - 1 WHERE postion BETWEEN $movefrom+1 AND $moveto

UPDATE table SET position = $moveto WHERE id = 6;

 

Took me a sec to understand and grasp that... but I think that is the statement I'm looking for, exactly.  You have 2 updates... one that sets the "touched" ones (ones that need reordering) by making their POS = POS (+/-) 1 if they are between (element to be moved current pos and final pos), and then lastly you set the final pos of the desired element.

 

I gotcha, Thanks a lot, makes a lot of sense.  Awesome

[unkwntech]

@xtopolis your website gave me a headache.

[xtopolis]

^^, yea.  I don't update the main page but once or twice a year...  Someday I'll put something up there.  For now, it's almost completely just used for testing stuff, hosting files, etc.

 

edit, there we go, some delicious black(ish) bg