Dynamically move records up and down a list then save the new sequence

loficat

I have a MySQL database table that has a list of of photo albums with accompanying descriptions. I have added a column to hold a unique number representing the priority and manually populated it.

gallery_id
gallery_name
gallery_description
gallery_sequence

A select query ordered by gallery_sequence now displays the albums in the correct order, but I would like the gallery administrator to be able to dynamically rearrange the album order and save the new sequence. In addition, new galleries added to the table need to have the next highest number added to the gallery sequence field.

Thoughts on a solution:

The list of galleries should be loaded into an array. The array is then dynamically updated (via Javascript?) as the administrator moves the albums around (via graphical arrows?). When they are done changing the order of the photo albums, the administrator presses a submit button and the records are saved (updating the "sequence" column). In addition, when a new gallery is created, the highest gallery_sequence number is checked and 1 is added to it to populate the gallery_sequence field in the new record.

Although this may sound simple, programming it is beyond my skills at this time. I've seen features like this in apps, but have been unable to extract the method utilized for my own use.

Your assistance is appreciated.

Thanks,

Rob

ratass2002

Do not give up.

My solution. My first assumption is that there will be only a few albums to play around like a yahoo photo album type of deal.

After the Admin press update, you need just to do one query in a loop assuming that your photo albums use an increasing id(ie. 1,2,3..). what you do is that after your array is ordered with the desired positions, you will have to loop through all elements.
Then each iteration you will find the item and update the id with the loop iteration #.

Ex:

for $i= 1 to $lastIndex
UPDATE Table SET gallery_id = '$i' WHERE gallery_name='$array'

That's the idea. Quotes might be wrong. You could have used id in your name or both I figure u have the name info for sure.

loficat

Thanks for your help. That does clear things up a little when the records are being updated... but I really need for all the blanks to be filled in. I believe I have the logic down, I just don't know how to implement it in PHP and Javascript.

loficat

The first half of my issue has been resolved. Following is a script that allows you to dynamically sort a recordset.

Now I need to update the records. After the main script, the original ColdFusion update script is included for reference.

<html>
<head>
<title>Dynamically Rearrange Record Sequence</title>
</head>
<body>

<?php
$dbconnect=mysql_connect ("localhost", "name", "password") or die ('I cannot connect to the database.');
mysql_select_db ("database");
$db="database";

$query="SELECT gallery_id, gallery_name, gallery_description, gallery_seq FROM table ORDER BY gallery_seq";
$result = mysql_db_query($db,$query,$dbconnect) or die ("Could not execute query: $query. " . mysql_error());
$recordcount=mysql_num_rows($result);
?>

<p>Select the item you'd like to move, and use the <strong>up</strong> and <strong>down</strong> links to move it.</p>