[SOLVED] Need help in changing the order of rows in db

[FVxSF]

A while back I was working on an Image Gallery and one feature was to order the Albums. I abandoned the project a while back and thought I save the code, but unfortunately I can't find the code to reorder my albums and I cannot find the example I based it off any where on the web. If I saw the code I'd know what it is....

 

Ok so left me explain what I was doing months ago. Once the Albums were being listed I had 2 links to move the album Up or Down. I'd simply swap the order values with 1 Query something like:

 

<?php

  list($PrimID, $SecID) = explode(":", $_GET[order]);

  // Don't shoot me for this query I know it wrong. This is what I need help on
  $SQL = "UPDATE my_albums WHERE al_AlbumOrder as alA....."
  $RunRes = mysql_query($SQL);

  if( !$RunRes ) {  die( mysql_error() ); }
?>

 

Any way. From what I can remember, I was creating some kind of Temp field, then once I SET values it'd be like:

 

SET AL_A=$SecID, AL_B=$$PrimID

 

For the life of me I cannot figure this one out. Any one have any ideas?

 

Thanks

[akitchin]

if you want to move an album upward, you need to perform two steps:

 

1. increment every album's order by 1 when their current order is between the album's new order and its current order.

2. set the album's new order to the target value.

 

you can do the same for moving it downward, and this method allows you to move it by any number of spaces (rather than simply switching).  for example:

 

1 - one
2 - two
3 - three
4 - four
5 - five

 

let's say i want to move "four" up to the second order.  i would do this:

 

UPDATE table SET order=order+1 WHERE order < 4 AND order >= 2
UPDATE table SET order=2 WHERE name='four'

 

the first query will change 2 to 3, 3 to 4, and then the second query will change 4 to 2.  make sense?  this is provided your order isn't a UNIQUE key; if it is, you can always change the album's order to 0, THEN perform both updates (otherwise the album just above it will have a duplicate entry).

[FVxSF]

No, my ID_AL is primary auto increment. I understand what you are saying too. I did forget to mention that this is a WordPress Plug in too. When I first started exploring reordering I'd use a similar method to the one you gave, but I would rather not have to +1 or -1.

 

The new way (and more efficient from what I read, though I never tested times) was to create a Temp Table then update the values in respect to the Order Tables. I'm still looking for the examples as I post here in hopes of remembering.

 

I do member it was 1 UPDATE query

[FVxSF]

Ok I think I may be on to something.. not sure though...

 

<?php
  
  list($PrimOID, $SecOID) = explode(":",$_GET[order]);
  $Table = "my_albums";

  /* AA is the Album the User Wants to change
  ** AB is the Target Album
  ** AA should = $SecOID and AB should = $PrimOID
  ** WHERE AA is currently $PrimOID AND AB is currently $SecOID...
  ** I think any way. It's probably been a good 4-6 months since I was working on this!
  */
  $SQL = "UPDATE $Table AS AA, $Table AS AB
              SET AA.al_AlbumOrder = $SecOID, AB.al_AlbumOrder = $PrimOID
              WHERE AA.al_AlbumOrder=$PrimOID AND AB.al_AlbumOrder=$SecOID";

  $RunRes = mysql_query($SQL);
  if( !$RunRes ) {
    die( mysql_error() );
  } else {
    echo "Order Changed!";
  }
?>

 

Any ideas? I think this is right but not 100% sure... or even 75% well maybe 75% sure...

[FVxSF]

Sweet! Ok that Query works like a friggen charm! Here's the code if any one is interested in a simple Reorder!

 

<?php
mysql_connect("localhost", "DBUSER", "DBPASS");
mysql_select_db("YOURDATABASE");

if( isset($_GET[order]) ) {
	list($PrimOID, $SecOID) = explode(":",$_GET[order]);
	$Table = "my_albums";

	$SQL = "UPDATE $Table AS AA, $Table AS AB
		SET AA.al_AlbumOrder = $SecOID, AB.al_AlbumOrder = $PrimOID
		WHERE AA.al_AlbumOrder=$PrimOID AND AB.al_AlbumOrder=$SecOID";

	$RunRes = mysql_query($SQL);
	if( !$RunRes ) {
		die( mysql_error() );
	}
}

$SQL = "SELECT count(*) FROM my_albums";
$RunRes = mysql_query($SQL);
$LastOrder = mysql_result($RunRes, 0,0);

$SQL = "SELECT * FROM my_albums ORDER BY al_AlbumOrder ASC";
$RunRes = mysql_query($SQL);
if( !$RunRes ) {
	die( mysql_error() );
}

while( $Row = mysql_fetch_array($RunRes) ) {
	$AlbumOr = $Row[al_AlbumOrder];

	if( $AlbumOr == 1 ) {
		$AO_U = $AlbumOr + 1;
		$AO_D = $LastOrder;
		$LinkUp = "test.php?order=$AlbumOr:$AO_U";
		$LinkDn = "test.php?order=$AlbumOr:$AO_D";

	} elseif( $AlbumOr == $LastOrder ) {
		$AO_U = 1;
		$AO_D = $AlbumOr - 1;
		$LinkUp = "test.php?order=$AlbumOr:$AO_U";
		$LinkDn = "test.php?order=$AlbumOr:$AO_D";

	} else {
		$AO_U = $AlbumOr + 1;
		$AO_D = $AlbumOr - 1;
		$LinkUp = "test.php?order=$AlbumOr:$AO_U";
		$LinkDn = "test.php?order=$AlbumOr:$AO_D";
	}

	echo "{$Row[al_AlbumName]} - <a href='$LinkDn'>DOWN</a> / <a href='$LinkUp'>UP</a><br>\n";
}
?>