Renumbering MySQL row/entires of images for output

[cgm225]

I have MySQL rows/entries for images stored in a table with the following structure:

 

ID(autoincrement)  |      filename      |    timestamp

1                                image1.jpg          some-timestamp-1

2                                image2.jpg          some-timestamp-2         

3                                image3.jpg          some-timestamp-3

4                                image4.jpg          some-timestamp-4

 

I then query and output the images ORDERed by either ID or timestamp.  However, if I want to rearrange the order they sort based on the ID, what would be the best way to do that?  Or should I add another column like "album_order"?  But even if I do that, how do I do the actually renumbering of the entries so their new order is something like this for example:

 

ID(autoincrement)  |      filename      |    timestamp

1                                image1.jpg          some-timestamp-1

2                                image4.jpg          some-timestamp-4         

3                                image2.jpg          some-timestamp-2

4                                image3.jpg          some-timestamp-3

 

So in this example, I moved image4.jpg up two places in the order (based on ID)

 

 

[craygo]

you can sort any way you like and just use a dummy number for the order instead of the ID

 

<?php
$sql = "SELECT * FROM `tablename` ORDER BY `filename` ASC";
$result = mysql_query($sql) or die(mysql_error());
$i=1;
while($r=mysql_fetch_assoc($result)){
echo $i ." ".$r['filename']." ".$r['timestamp'];
$i++;
}
?>

 

Ray

[cgm225]

Thank you so much for the help.

 

Let me restate what I am trying to get at.  I have a gallery script, and I want to be able to change the order in which the pictures are displayed using a dropdown menu that will read something like "Move to position [dropdown menu with a range of numbers equalling the number of photos in the album (except the number of the position the photo is currently in)]".  Any ideas?

 

The photos are stored in a table as shown above.

[craygo]

OK I got ya, sorry for misunderstanding. Have to think about that one.

[cgm225]

Thank you for the help!

[cgm225]

bump

[craygo]

How did you want to go about moving it up or down?? With a dropdown or maybe just a little arrow to move up or down one at a time??

[cgm225]

When viewing an individual photo in an album, I want to have a dropdown menu under it that will read something like "Move to position [dropdown menu with a range of numbers equaling the number of photos in the album (except the number of the position the photo is currently in)]". 

 

Any ideas?

 

(Thank you for your help!)

[craygo]

I found a function which will move a portion of an array up or down and preserve the keys. This example i use a couple of arrows to go up or down, I have not tried the dropdown yet but you might be able to do it with a quick loop to drop the id down a couple spots at once.

 

Here is the function

<?php
function array_move_element($array, $value, $direction = 'up') {

    $temp = array();

    if(end($array) == $value && $direction == 'down') {
        return $array;
    }
    if(reset($array) == $value && $direction == 'up') {
        return $array;
    }

    while ($array_value = current($array)) {

        $this_key = key($array);

        if ($array_value == $value) {
            if($direction == 'down') {
                $next_value = next($array);
                $temp[key($array)] = $next_value;
                $temp[$this_key] = $array_value;
            } else {
                $prev_value = prev($array);
                $prev_key = key($array);
                unset($temp[$prev_key]);
                $temp[$this_key] = $array_value;
                $temp[$prev_key] = $prev_value;
                next($array);
                next($array);
            }
            continue;
        } else {
            $temp[$this_key] = $array_value;
        }

        next($array);
    }
    return $temp;

}
?>

 

And this is what I used to show and move the id's

<?php
if(!isset($_GET['ids'])){
  $list = array();
  $sql = "SELECT * FROM roster ORDER BY sort ASC LIMIT 5";
  $res = mysql_query($sql) or die(mysql_error());
  while($r = mysql_fetch_assoc($res)){
  $list[] = $r['id'];
  }
$ids = implode("|", $list);
}


if(isset($_GET['moveid'])){
$list = explode("|", $_GET['ids']);
$move = explode("|", $_GET['moveid']);
$list = array_move_element($list, $move[0], $move[1]);
$ids = implode("|", $list);
}

foreach($list as $id){
$sql = "SELECT handle, id, real_name FROM roster WHERE id = '$id'";
$res = mysql_query($sql) or die(mysql_error());
$r = mysql_fetch_assoc($res);
echo $r['id']." ".$r['handle']." ".$r['real_name']." <a href=\"$self?ids=$ids&moveid=$id|up\"><img src=\"images/upshrink.gif\" border=0></a>  <a href=\"$self?ids=$ids&moveid=$id|down\"><img src=\"images/downshrink.gif\" border=0></a><br />\n";
}
?>

 

Obviously change the query I used just a table I had set up. If you can't figure out the drop down part let me know.

 

Ray