php mysql sort order. Change sort order with hyperlink (up/down arrows)

Question

If I have 4 db rows, I want to be able to sort them using column 'position'.

id - name - position

1  - row1 -    1
2  - row2 -    2
3  - row3 -    3
4  - row4 -    4

I know how to sort with php SELECT SORT BY etc

But I want to be able to press a hyperlink which would swap positions.

Row   img     img
row1  (down)
row2  (up)    (down)
row3  (up)    (down)
row4  (up)

ie, Press up on row 2 would change row2 position -> 1 and would change row 1 position -> 2

Any help would be greatly appreciated.

What exactly do your want? Is it just a JavaScript you're interested in? Or PHP code + database structure for re-arranging data in a database table? — Salman Arshad
– Salman Arshad, Commented Feb 9, 2012 at 11:42
either, was after advice into what ppl thought was the best solution. If you have ideas pls let me know. — Codded
– Codded, Commented Feb 9, 2012 at 12:38
Understood, but the way you've put your question it seems like people are thinking this is a JavaScript/GUI question. You should re-phrase your question, emphasizing that your have a position column and want a solution to manipulate its value. — Salman Arshad
– Salman Arshad, Commented Feb 9, 2012 at 13:29

gibbotronic · Accepted Answer · 2012-02-09 11:30:20Z

0

On the hyperlink, load a PHP page which takes a few parameters:

The id of the row you want to move up/down
The current position of the row you want to move up/down
The id of the row beneath/above the row you want to move
The current position of the row you want to move up/down
Whether you want to move up or down of the row you're moving (such as its id), and the current position, as well as the new position.

From this, you will know exactly what you need to do, and can fashion a query to swap these items in the database. After this is done, reload the page.

answered Feb 9, 2012 at 11:30

gibbotronic

818 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stelian Matei · Accepted Answer · 2012-02-09 11:34:06Z

0

The easiest thing to do is to use a javascript library that allows item reordering. Check this out: http://jqueryui.com/demos/sortable/

Once the user has finished reordering, you can save the current positions of all your elements. Using this method allows the user to rearrange all the items as he likes without having to refresh the page each time he clicks on "Up" or "Down".

answered Feb 9, 2012 at 11:34

Stelian Matei

11.6k3 gold badges28 silver badges29 bronze badges

3 Comments

Codded Over a year ago

Thanks, sounds good. How would this save for the next time they visit the page.

Stelian Matei Over a year ago

After the user has finished ordering the items, he should click on a button/link to save the current order. On that event, you need to serialize the items and send them to the server.

Stelian Matei Over a year ago

If you need more info about saving the ordered items, I just found another question exactly about that: stackoverflow.com/questions/7342727/…

ThElitEyeS · Accepted Answer · 2012-02-09 11:34:19Z

0

um what about this

while ($row = mysql_fetch_array($query)) {
@$group[] = $row['img'];
}

and call it
echo $group[1];
echo $group[3];
echo $group[0];
echo $group[2];

etc. idk if this what you meant

answered Feb 9, 2012 at 11:34

ThElitEyeS

6473 gold badges7 silver badges16 bronze badges

Comments

rajesh · Accepted Answer · 2012-02-09 11:49:10Z

if you want to swap the position in the UI only, you can use javascript.

eg:

<div id="1" > <div id="data_1">data goes here for row1</div> </div>
<div id="2" > <div id="data_2">data goes here for row2</div> <a onclick="moveUp(2);"> UP</a> <a onclick="moveDown(2);"> Down</a></div>
<div id="3" > <div id="data_3">data goes here for row3</div> </div>

<script>
function moveUp(n){
  var m = n-1;
  var own_id = 'data_'+n;
  var new_id = 'data_'+m;
  var tmp_data = '';
  var own_data = $(own_id).innerHtml;
  if($(new_id) != 'undefined'){
    tmp_data = $(new_id).innerHtml;
    $(new_id).innerHtml = own_data;
    $(own_id).innerHtml = tmp_data;
  }

}

function moveDown(n){
  //same logic can be applied here
}
</script>

hope it will help.

penkzhou · Accepted Answer · 2013-11-10 13:11:36Z

0

I also meet the same problem and you can try this kind of solution. http://www.phpfreaks.com/tutorial/php-custom-list-order

answered Nov 10, 2013 at 13:11

penkzhou

1,21014 silver badges32 bronze badges

Collectives™ on Stack Overflow

php mysql sort order. Change sort order with hyperlink (up/down arrows)

5 Answers 5

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related