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.. Quote Link to comment 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??? Quote Link to comment 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? Quote Link to comment 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... Quote Link to comment 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.. Quote Link to comment 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. Quote Link to comment 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 Quote Link to comment 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. Quote Link to comment 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. Quote Link to comment 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 Quote Link to comment 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. Quote Link to comment 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 Quote Link to comment 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. Quote Link to comment 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? 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.