Help with reordering array... php/mysql

  • lido
  • Newbie
  • Newbie
  • lido
  • Posts: 5

Post 3+ Months Ago

Hello all... I am hoping your expertise may well be able to help me out. I have a mysql table which stores 2 values (name and number)

Example: Red 1
Blue 2
Green 3

I want to be able to display this list to the user (not difficult) Where my problem lies is that I want the user to be able to reorder the items and then have it write the new order to the table. My idea is that next to each item is a move up/move down button. For example, if the user clicks the move up on the Blue row, the new order that would be written to the table would be:

Blue 1
Red 2
Green 3


I've tried to write the code on my own, and am completely STUMPED! Thank you for your help!!

-L
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • rtm223
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1855
  • Loc: Uk

Post 3+ Months Ago

to swap elements $a and $b

create a temporary variable ($temp)

then
Code: [ Select ]
$temp=$a;
$a=$b;
$b=$temp;
  1. $temp=$a;
  2. $a=$b;
  3. $b=$temp;


You will either need two temporary variables or something else if you want to move the numbers as well but that is the basic algorithm you need to adapt.

If you want them reordered in the database, things are a little more tricky lol.
  • lido
  • Newbie
  • Newbie
  • lido
  • Posts: 5

Post 3+ Months Ago

Thank you for the reply. Of course, I would have to be the one to choose to be 'tricky'.

I need the new order rewritten to the database... obviously the names won't change, but the rank order numbers associated with the names will need to be written to the database.

Any help/direction is appreciated!

-L
  • rtm223
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1855
  • Loc: Uk

Post 3+ Months Ago

are those numbers the order, you are ordering by the numbers yes?

do exactly what I said with the temp variable, but do it on the numbers. When you get the data from the database remember to order by the number and you will be sorted.

or hang on. you want to move one to a given place place and everything else moves to accomodate it?. Or just a straght swap between two records?
  • _Leo_
  • Proficient
  • Proficient
  • User avatar
  • Posts: 279
  • Loc: Buenos Aires, Argentina

Post 3+ Months Ago

You will have to start by adding a new integer column for specifying the row order at will. Then, you will have to show the list to the user with a couple of links up/down. That link should pass the row order number to the script, where you will have to make two updates:
Code: [ Select ]
// Sample UP script
UPDATE mytable SET order=($id - 1) WHERE order=$id
UPDATE mytable SET order=$id WHERE order=($id - 1)
  1. // Sample UP script
  2. UPDATE mytable SET order=($id - 1) WHERE order=$id
  3. UPDATE mytable SET order=$id WHERE order=($id - 1)
  • lido
  • Newbie
  • Newbie
  • lido
  • Posts: 5

Post 3+ Months Ago

Thanks for the input! I'll make some attempts and recoding and post my results.

To give an idea of exactly what I am going to be doing, here are the data points

ID Name Order
1 Chicago 1
2 Omaha 2
3 Boston 3
4 Portland 4

The table when called will pull the name and order, and order by order ASC. If a user clicks up/down, then the page will refresh with the new information. For example... if a user clicks on the move up for Boston, then the table should be writtien to look like:

ID Name Order
1 Chicago 1
2 Omaha 3
3 Boston 2
4 Portland 4

When the user clicks the move up or down button, the data should be written to the databse, and the page refresh with the new order.

Does that help clarify? The hardest part that I see is reordering the array that's already populated in the memory. Since it will be pulling the data from the database and displaying it - how do I get it to reorder, then remember those changes (since the user might make multiple changes) and then write the final order to the database. Would a temporary table be needed where it could write each change made to that temp table, then once a user clicks on submit changes, it would populate the correct database with the temp table information, and then the final step could be to clear the temp table?

Thanks!

-L
  • _Leo_
  • Proficient
  • Proficient
  • User avatar
  • Posts: 279
  • Loc: Buenos Aires, Argentina

Post 3+ Months Ago

Well, if you don't mind a new hit to the webserver for every movement, you can use the solution I wrote before. If you have an array, you will need to update the full table I guess.
  • lido
  • Newbie
  • Newbie
  • lido
  • Posts: 5

Post 3+ Months Ago

I don't mind a hit to the server with every movement. Is there a way it could be done without a hit to the server for every movement, yet still display the changes in real time?

-L
  • _Leo_
  • Proficient
  • Proficient
  • User avatar
  • Posts: 279
  • Loc: Buenos Aires, Argentina

Post 3+ Months Ago

No, I mean, you may build a form and use JavaScript to make the users view more accurate to what he is doing. But it isn't true realtime, because the database will not be updated until form submission.

Upon form submission you will have to update the modified rows in database.
  • lido
  • Newbie
  • Newbie
  • lido
  • Posts: 5

Post 3+ Months Ago

Leo-

Thanks for the reply. My knowedlge of custom JS is very little. Are you familiar with any pre-existing JS that does something like this?

Thank you!

-L
  • _Leo_
  • Proficient
  • Proficient
  • User avatar
  • Posts: 279
  • Loc: Buenos Aires, Argentina

Post 3+ Months Ago

Not really, and, unless you are sure your end user will make a LOT of changes in the order, I don't think you should go fight with JS right now.
I would go for the PHP only solution, it's not that bad. And if you want to minimize the "server overload impact", you may use a new broser windows with no pictures in there and just a few of HTML.

Post Information

  • Total Posts in this topic: 11 posts
  • Users browsing this forum: No registered users and 80 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.