Move up/down display order in one query?

Hi,
I have a table for articles for my page and in the backend I want to add move up & move down buttons to change the display order for the articles on that page.

Here’s my table:

article

  • display_order (this is just an integer, not primary index, but will auto increment when a new article is inserted)
  • name
  • text

My move up button, for example article display_order ‘3’ goes to url move.php?display_order=3&move=up

So far I did this function to take away 1 so that it now becomes 2, but I need to be able to update article 2 so that it now becomes 3. How do I do that?


function move_up()
{
      $display_order = $_GET['display_order']; // Get the article we are moving up
	  $prev_article = ($_GET['display_order'] - 1); // Get the article we are swapping with
	  $connection = db_connect();		
	  $query = "UPDATE article SET display_order = '($display_order - 1)' WHERE display_order = '$display_order'";		
	  $result = mysql_query($query);
		if (!$result)
		{
		  return false;
		}
		else
		{
		  return true;
		}		
	}

$insertpoint = <lowernumber>
UPDATE article SET display_order = (MOD(display_order,$insertpoint)*-1)+$insertpoint+1) WHERE display_order = $insertpoint OR display_order= ($insertpoint + 1)

So for rows 2 & 3 to switch places:
Row 2’s execution:
MOD(2;2) = 0.
0*-1 = 0
0+2 = 2
0+1 = 3. Row 2’s display_order is now 3.

Row 3:
MOD(3;2) = 1
1*-1 = -1
-1+2 = 1
1+1 = 2.
Row 3’s new display_order is 2.

That way, you only need 1 function - just pass it the relevant lower number (passing it 'the current article’s number will move it ‘up’ one number, and passing it the current article’s number -1 will move it ‘down’ one number.

Wow thanks for that, I was looking at all kinds of ways to try to do this and you did it in 1 line :stuck_out_tongue:

I never used MOD before, just looking that up now. Still learning, this is cool!

Just keep in mind this only works if the display_order is NOT a unique/primary field. Otherwise it will collide.

Ah yes thanks, I set it as standard int data for this reason. I love this site, I’ve learned so much here in such a short space of time!

Ah damn, I just discovered a fatal flaw in this idea.
I addded a page_id column in the article table so that I can use a WHERE clause to output articles according to which page they belong to.

Say I have 2 pages each with 4 articles on them, they would look like this:
Page 1

  • article 1
  • article 2
  • article 3
  • article 4
    Page 2
  • article 5
  • article 6
  • article 7
  • article 8

So now if I want to add a new article on page 1, it would be article 9. So now if I move it up, it will make it article 8, thus remaining at the bottom of page 1, producing this:
Page 1

  • article 1
  • article 2
  • article 3
  • article 4
  • article 8
    Page 2
  • article 5
  • article 6
  • article 7
  • article 9

Back to the drawing board :frowning:

Why bother with clunky up and down arrows?

Hehe wow that really does look amazing, I wish I could make it that easy, but the only thing I know about jQuery is how to spell it. :stuck_out_tongue:

I managed to solve my sort order problem by creating a new table for each new page and having each new article in a new row on that table. Maybe not ideal, but it works. I’m still learning…

If you want to really learn, what you’re doing is wrong.

You shouldn’t even have a “page” to begin with.

Simply pull out x number of results, first 4 go into page 1, next 4 go to page 2. There is no reason to explicitly define pages, on top of that - you should NOT be create new tables for each page - that’s just bad on top of bad.

The logic of “pages” should be strictly PHP - the database simply stores them with an order #.

I don’t actually have physical ‘page’, everything runs on index.php. I’m just using ‘page’ as a table name in the db, then I use the url to determine which results should display.
I just can’t think of any other way to be able to move articles up & down other than to separate each ‘page’ & its articles in the db so that each page always has its articles stored in strict numerical oder.

The only other possibility I can think of would be to somehow get php to fetch the id of the previous or next iteration in the loop to swap out with.
if (that’s possible) {
Hooray, Google here I come!
}
:smiley:

Aha, just discovered prev() current() next(), maybe that’s how I can do it?

Question: Will an article ever change pages?

No it won’t… :slight_smile:

Display_order is not unique. (Hint: There can be 2 display_order 1’s, if they belong to different pages… add a AND page = <thepagenumber> and wrap your OR’s in parenthesis)

Hi, I have been looking more at this and the more I do, the more I keep coming back to thinking that the only way to do it would be to somehow capture the display_order value of the previous iteration of the loop (or the next iteration depending on which direction I want to move the article).
If I auto increment the display_order column, then if an item gets deleted, there will be a jump in numbers and the swap won’t work in the example in post #2. Even if I don’t auto increment them and have them user defined, if article #2 gets deleted, then I have to swap article #3 with article #1 * so forth.

I’m going to try to find a way to capture previous & next iteration values to use for post data, but before I go googling away, can this even be done?