moving rows up or down, promoting and demoting, in a database?

[seniramsu]

This is written in php, but it involves dealing with my database with php through mysql_queries.

 

I'm creating a CMS where my client can manage his own photo gallery. I'm having trouble getting "move up" and "move down" buttons to work for the albums to be promoted or demoted both within his CMS and within the database.

 

When the client clicks on the "move up" button, the album will be moved toward the top of the page. Just the opposite with move down. Once I figure out how to work one of the buttons, I can just do the opposite to code the other.

 

Once the button is pressed, it will query the database to increment its album_id by 1, and simultaneously decrement the album_id for the album above it by 1. Essentially performing an album_id SWAP. (For this I figured if a primary key was established it wouldn't allow two album_id's to be the same at one time, so I tried disabling it, performing my query, then re-enabling it).

 

I'm having a few issues with the code:

1) promoting albums closer to album_id = 0 (ex. where album_id=2) can somehow push the album with album_id=1 to 0 or -1.

2) If i have, say, 6 albums, and I try to promote album 3, albums 1, 2, and 3 will disappear. When I reload the page they will reappear in the correct order.

3) albums 5 and 6 give me an error message (there is no album above it to swap album_id's with...don't know why it doesn't work for album 5).

 

Here's the code. Any ideas?

 

//////////////////// BEGIN QUERY FOR DATABASE VALUES AFTER AN ALBUM HAS BEEN MOVED UP ////////////////////

if (isset($_POST['moveUp_' . $album_id])) {

	$promote = $album_id++;
	$demote = $promote--;

	$queryAbove = "SELECT * FROM `ridgeline`.`albums` WHERE `album_id` = " . $album_id . " + 1";
	$resultAbove = mysql_query($queryAbove, $db_server);
	$rowAbove = mysql_fetch_assoc($resultAbove) or die("There is nothing above this album. It is already at the top." . mysql_error());
	$next = $rowAbove['album_id'];

	$queryDropPrimary = "ALTER TABLE `albums` DROP PRIMARY KEY(`album_id`)";
	$resultDropPrimary = mysql_query($queryDropPrimary, $db_server);

	$queryPromote = mysql_query("UPDATE `ridgeline`.`albums` SET `album_id` =  " . $promote . " WHERE `albums`.`album_id` = " . $album_id . "", $db_server);
	$querySwap = mysql_query("UPDATE `ridgeline`.`albums` SET `album_id` =  " . $demote . " WHERE `albums`.`album_id` = " . $next . "", $db_server);

	$queryAddPrimary = mysql_query("ALTER TABLE `albums` DROP PRIMARY KEY, ADD PRIMARY KEY(`album_id`)", $db_server) or die("fail." . mysql_error());


	$queryRefresh = "SELECT * FROM `ridgeline`.`albums` WHERE `album_id` IS NOT NULL ORDER BY `album_id` " . $orderBy . "";
	$resultRefresh = mysql_query($query, $db_server);



	while (isset($resultRefresh)) {
		$rowRefresh = mysql_fetch_assoc($resultRefresh);

			$album_id = $rowRefresh['album_id'];
			$user_id = $rowRefresh['user_id'];
			$name = $rowRefresh['name'];

		$deleteConfirm = NULL;


	////////// BEGIN REFRESH PAGE AFTER AN ALBUM HAS BEEN MOVED UP //////////
		if (isset($album_id)) {	
			$newAlbum = "<table id=\"adminAlbums\">
					<tr>

						<td id=\"thumbnailContainer\"></td>
						<td id=\"albumName\"><h2><a href=\"adminPhotos.php?name=" . $name . "\">". $name . "</a></h2></td>
						<td id=\"albumThumbnail_control\">
							<form name=\"albumThumbnail_control\" enctype=\"multipart/form-data\" action=\"adminAlbums.php\" method=\"POST\">
								<input name=\"albumRename_" . $album_id . "\" id=\"albumRename_" . $album_id . "\" type=\"text\" />
								<input name=\"albumRenameSubmit\" id=\"albumRenameSubmit\" type=\"submit\" value=\"Rename\" />
							</form>
						</td>       

						<td id=\"adminAlbumsControl\">
							<form name=\"control\" id=\"control\" enctype=\"multipart/form-data\" action=\"adminAlbums.php\" method=\"POST\">
								<label id=\"checkbox\" ><input type=\"checkbox\" id=\"checkbox_" . $album_id . "\" value=\"\" checked=\"true\" /> Show</label> 
								<input name=\"moveUp_" . $album_id . "\" id=\"moveUp_" . $album_id . "\" type=\"submit\" value=\"Move Up\" />
								<input name=\"moveDown_" . $album_id . "\" id=\"moveDown_" . $album_id . "\"type=\"submit\" value=\"Move Down\" />  
								<input name=\"delete_" . $album_id . "\" onclick=\"deleteConfirmTarget()\" id=\"delete_" . $album_id . "\" type=\"submit\" value=\"Delete\" />
							</form>"
							. $deleteConfirm .
						"</td>

					</tr>
				</table>";	
			echo $newAlbum;
		}
		elseif (!isset($album_id)) {
			break;
		}
	////////// END REFRESH PAGE AFTER AN ALBUM HAS BEEN MOVED UP //////////

	} // End while

} // End if

//////////////////// END QUERY FOR DATABASE VALUES AFTER AN ALBUM HAS BEEN MOVED UP ////////////////////

 

There's much more code connected with this. Just didn't want it to be overwhelming. Also, I still need to do some cleaning up.

thanks!

[andrewgauger]

You need another column in your database for the order.  Your SQL is going to bring your db to its knees trying to keep up.  All you need is to :

 

ALTER TABLE 'albums' ADD COLUMN sequence int;

UPDATE 'albums' set sequence=album_id

 

and then alter your code to use sequence instead of altering table on every swap.

 

also a better query would be:

 

UPDATE 'ridgeline'.'album' AS album1 JOIN 'ridgeline'.'album' AS album2 ON (album1.album_id = $album_id AND album2.album_id =$album_id-1) SET album1.sequence=album2.sequence, album2.sequence=album1.sequence

 

But this is untested.  Please, someone with JOIN experience critique this solution.