john_6767 Posted June 23, 2007 Share Posted June 23, 2007 I would like to implement a column in my mysql database that allows me to sort my records by this column. I assume that numbers is the best way to go, (ie orders are 1,2,3,4,5 and i can sort by order ascending). My question is in regards to maintaining this column as if i delete a record in the middle of the order i would have to move every order number that is higher down a number so there isn't a gap in numbers. And to update i would have to move all the other records around this record which are effected by the change. Is this the way to approach this problem, is there a better way or can you point me towards some script to do this.. Link to comment https://forums.phpfreaks.com/topic/56797-maintaining-an-order-field/ Share on other sites More sharing options...
teng84 Posted June 23, 2007 Share Posted June 23, 2007 that is not possible if your were talking about html but using update you can obtain that but ID are primary key so why do you have to cahnge thhose??? Link to comment https://forums.phpfreaks.com/topic/56797-maintaining-an-order-field/#findComment-280577 Share on other sites More sharing options...
pocobueno1388 Posted June 23, 2007 Share Posted June 23, 2007 If you just using the column to order your results, why does it matter if there is a gap in the numbers? Link to comment https://forums.phpfreaks.com/topic/56797-maintaining-an-order-field/#findComment-280578 Share on other sites More sharing options...
john_6767 Posted June 23, 2007 Author Share Posted June 23, 2007 Thanks for your quick reply, I don't want to change the pk and i am after a server side (php) script to do this, i would like to have a seperate field for 'order by' so the user can choose the order of records, i.e. like for a photo gallery the user can choose which photos are displayed before others without having to put them into the gallery, i have seen this done before but can't figure it out.. i assume its common practise and there are php scripts out there for it.. it involves something like, change current record from 'oldValue' to 'newValue' if another record has 'newValue' then change new record etc... Link to comment https://forums.phpfreaks.com/topic/56797-maintaining-an-order-field/#findComment-280580 Share on other sites More sharing options...
john_6767 Posted June 23, 2007 Author Share Posted June 23, 2007 pocobueno1388 - your right a gap doesn't matter, do you know how to do this with a gap? it would just be 'neater' and the numbers stay smaller with no gaps.. Link to comment https://forums.phpfreaks.com/topic/56797-maintaining-an-order-field/#findComment-280581 Share on other sites More sharing options...
pocobueno1388 Posted June 23, 2007 Share Posted June 23, 2007 I don't think you should be concerned with the numbers staying neater/smaller....but thats completely up to you. I don't really understand what your trying to do, so I can't give you an answer to if I know how to implement your idea. Do you have a website you could show that will give an example of what your talking about? I understand you want them to be able to choose which order the gallery pictures show up in, but I don't know how you want the users to be able to choose. Are you wanting them to be able to number the pictures? Drag and drop in the order they want them (would need javascript)...explain it a bit more. Link to comment https://forums.phpfreaks.com/topic/56797-maintaining-an-order-field/#findComment-280583 Share on other sites More sharing options...
teng84 Posted June 23, 2007 Share Posted June 23, 2007 i think waht jhon mean is that he dont want the number to increment or grow because he delete some record Link to comment https://forums.phpfreaks.com/topic/56797-maintaining-an-order-field/#findComment-280585 Share on other sites More sharing options...
pocobueno1388 Posted June 23, 2007 Share Posted June 23, 2007 From my understanding, he does want the numbers to decrease when he deletes a record. Say he has 5 records in the DB. (1,2,3,4,5). If he deletes record #3 he would want record #4 to become the new record #3, then record #5 to become #4. So now he ends up with 4 rows in the DB since he deleted one, and they are numbered 1,2,3,4 instead of 1,2,4,5. If I'm wrong about that, let me know. But I already said that I don't think he should be worried about that. Link to comment https://forums.phpfreaks.com/topic/56797-maintaining-an-order-field/#findComment-280587 Share on other sites More sharing options...
john_6767 Posted June 23, 2007 Author Share Posted June 23, 2007 pocobueno1388 - its not online yet, but here's an example. Sounds like your onto what i'm after, I have a photo gallery, with 2 fields, (ID, file, caption) I would like to add another field 'order' and then sort by order in the photo gallery so the photo with number 1 is first 2 is second etc... For the backend. I have all the records display in a table, each record as a row. I would like to add a drop and drag using javascript with php that fires when it is dropped to make the update. If this is not possible a simple up link and a down link for every record would suffice. Link to comment https://forums.phpfreaks.com/topic/56797-maintaining-an-order-field/#findComment-280588 Share on other sites More sharing options...
john_6767 Posted June 23, 2007 Author Share Posted June 23, 2007 From my understanding, he does want the numbers to decrease when he deletes a record. Say he has 5 records in the DB. (1,2,3,4,5). If he deletes record #3 he would want record #4 to become the new record #3, then record #5 to become #4. So now he ends up with 4 rows in the DB since he deleted one, and they are numbered 1,2,3,4 instead of 1,2,4,5. If I'm wrong about that, let me know. But I already said that I don't think he should be worried about that. cheers, thats correct Link to comment https://forums.phpfreaks.com/topic/56797-maintaining-an-order-field/#findComment-280590 Share on other sites More sharing options...
pocobueno1388 Posted June 23, 2007 Share Posted June 23, 2007 I know you can find drag and drop tutorials online, I ran into one not long ago. I couldn't help you with that though, I'm not too good with javascript yet. If you wanted to put links under the pictures that said "move up" and "move_down", all you would have to do is update the order columns number up or down with a simple query. I'm not really sure what you actually need help with. Is it the code you don't know how to do, or did you just want ideas on how to implement it? I would go with the javascript way personally, just because it is more appealing. Using "move up/down" links might become a hassle and be time consuming if you have a lot of pictures that you want to sort. So definitely try google for what your looking for and see what you come up with. Link to comment https://forums.phpfreaks.com/topic/56797-maintaining-an-order-field/#findComment-280598 Share on other sites More sharing options...
pocobueno1388 Posted June 23, 2007 Share Posted June 23, 2007 Here are two that I just came across: http://cyberdummy.co.uk/test/dd.php http://isocra.com/articles/table_dnd.php Link to comment https://forums.phpfreaks.com/topic/56797-maintaining-an-order-field/#findComment-280615 Share on other sites More sharing options...
john_6767 Posted June 23, 2007 Author Share Posted June 23, 2007 Thanks, i'll check them out and post back how i go. Link to comment https://forums.phpfreaks.com/topic/56797-maintaining-an-order-field/#findComment-280683 Share on other sites More sharing options...
john_6767 Posted June 24, 2007 Author Share Posted June 24, 2007 checked it all out on the localhost, cheers, Would this work with table rows? I am using http://cyberdummy.co.uk/test/dd.php posted by pocobueno1388 which uses lists, the <li> tags can be manipulated with javascript but i would like to do this same thing with <tr> tags.. is this possible? Link to comment https://forums.phpfreaks.com/topic/56797-maintaining-an-order-field/#findComment-281180 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.