Hi all,
I am wondering if someone could help me out with this idea a little:

I'm working on an online photo gallery site, and the user will have the option of re-ordering the albums they have and then at a later time, once i get this worked out, they will be able to re-order the pics in the individual albums. I have already figured out how to show the arrows, depending on what the display order is (there's a field in the table with the number in which it displays). The thing I don't know about, is the best way to update the database. Say the user had 5 albums, and they wanted to move album 4 to the 2nd postion. What would the easiest way to move it be? Does that make any sense?? Any help would be greatly appreciated!

Thank you,
Matt

Matt Millican
InternetMill.com
www.internetmill.com
(XP Pro / Dreamweaver MX / Access XP Pro/ MS SQL / ASP / VB Script / PHP, mySQL / Photoshop / Fireworks)

Hi Matt

I started with this, run it in query analyser against a test SQL database, I've commented out the drop/create table code so de-comment then comment out again. Nice problem by the way. This isn't meant to be the answer but a stab at getting you going in the right direction, it would be nice to work this one out though. I'm not SQL guru by any stretch of the imagination, a guru would just throw 5 mins brain time at this and say there you go, so if anyone else has any ideas please post them here.

Anyway here's my first attempt:

-- DROP TABLE tbl_matt_sort
--
-- CREATE TABLE tbl_matt_sort
-- (col_album_id INT PRIMARY KEY IDENTITY,
-- col_album_rank INT NOT NULL UNIQUE,
-- col_album_name VARCHAR(50) NOT NULL)

SET NOCOUNT ON

INSERT INTO tbl_matt_sort(col_album_rank, col_album_name) VALUES(1,'Album One')
INSERT INTO tbl_matt_sort(col_album_rank, col_album_name) VALUES(2,'Album Two')
INSERT INTO tbl_matt_sort(col_album_rank, col_album_name) VALUES(3,'Album Three')
INSERT INTO tbl_matt_sort(col_album_rank, col_album_name) VALUES(4,'Album Four')
INSERT INTO tbl_matt_sort(col_album_rank, col_album_name) VALUES(5,'Album Five')

SELECT * FROM tbl_matt_sort ORDER BY col_album_rank

DECLARE @OldRank INT, @NewRank INT, @OldID INT, @NewID INT, @Multiplyer INT

SET @Multiplyer = 10000
SET @OldID = 2
SET @NewID = 4

SET @OldRank = (SELECT col_album_rank FROM tbl_matt_sort WHERE col_album_id= @OldID) + @Multiplyer
SET @NewRank = (SELECT col_album_rank FROM tbl_matt_sort WHERE col_album_id = @NewID) + @Multiplyer

PRINT @OldRank
PRINT @NewRank

UPDATE tbl_matt_sort SET col_album_rank = @NewRank WHERE col_album_id = @OldID
UPDATE tbl_matt_sort SET col_album_rank = @OldRank WHERE col_album_id = @NewID
UPDATE tbl_matt_sort SET col_album_rank = @NewRank - @Multiplyer WHERE col_album_id = @OldID
UPDATE tbl_matt_sort SET col_album_rank = @OldRank - @Multiplyer WHERE col_album_id = @NewID

SELECT * FROM tbl_matt_sort ORDER BY col_album_rank

SET NOCOUNT OFF

Had a little thinking about this one. A question popped up when writing the code above, I've written the code that will swap the ranking of the albums, is that what you want or is to adjust the ranking so that the album sitting in rank 2 becomes 3, 3 becomes 4 etc.? Thinking about this, I've got a feeling that's what you're after. I'll give this some more thought, let me know, I've done ranking before but quite a while ago and depending on how many records you have, it can be quite demanding on the server.

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Lee,
Thanks for the reply. I will hopefully be able to try this idea over the weekend. Unfortunately, other sites are kicking me from behind that need to get done, so they're taking priority, but I hope to have a little time this weekend to look at this more.

Thanks again,
Matt

Matt Millican
InternetMill.com
www.internetmill.com
(XP Pro / Dreamweaver MX / Access XP Pro/ MS SQL / ASP / VB Script / PHP, mySQL / Photoshop / Fireworks)