xtopolis Posted August 3, 2008 Share Posted August 3, 2008 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? Quote Link to comment Share on other sites More sharing options...
dannyb785 Posted August 3, 2008 Share Posted August 3, 2008 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 Quote Link to comment Share on other sites More sharing options...
xtopolis Posted August 3, 2008 Author Share Posted August 3, 2008 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? Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted August 3, 2008 Share Posted August 3, 2008 does positioning matter or just that it is random? Quote Link to comment Share on other sites More sharing options...
Barand Posted August 3, 2008 Share Posted August 3, 2008 if you want filemask1 moved to 3, then filemask2 moves up to 2, occupying the now vacant position Quote Link to comment Share on other sites More sharing options...
xtopolis Posted August 3, 2008 Author Share Posted August 3, 2008 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) Quote Link to comment Share on other sites More sharing options...
Barand Posted August 3, 2008 Share Posted August 3, 2008 [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 Quote Link to comment Share on other sites More sharing options...
xtopolis Posted August 3, 2008 Author Share Posted August 3, 2008 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 Quote Link to comment Share on other sites More sharing options...
unkwntech Posted August 3, 2008 Share Posted August 3, 2008 @xtopolis your website gave me a headache. Quote Link to comment Share on other sites More sharing options...
xtopolis Posted August 3, 2008 Author Share Posted August 3, 2008 ^^, 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.