SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2007
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trouble with row sequencing script

    Hello everyone,

    I have a database containing a list of references to photos for a photography portfolio. I've built the admin panel of the site so that my client can reorder the photos however he'd like. I created a field in my table called sequence, and that is the field that the photos are always ordered by when displayed. My client can type a number in a text box below each image to specify which position he would like it to move to. First the script below sets up 3 variables:

    sequence - the current position of the photo we are moving.
    new_sequence - the position that we want our image to be moved to.
    category - don't worry about this one.

    And the script below is supposed to do 3 things:

    1. Find the image we are moving and set its sequence to -1 (to get it out of the way)
    2. If we are moving the image up (ie. position 1 to position 3), decrement the sequence of all rows between the original position and the new position.
    Else increment them.
    3. Set the image to its new position.

    Here's the code:

    Code:
    <?php
    
    //Connect to the database
    require '../database.php';
    
    //Set up form variables
    $sequence = $_POST['sequence'];
    $new_sequence = $_POST['new_sequence'];
    $category = $_POST['category'];
    
    //If number wasn't changed, go back to index
    if ($sequence == $new_sequence) header("location: index.php");
    
    //Find the image we are moving and set its sequence to -1
    mysql_query("UPDATE photos SET sequence = -1 WHERE sequence = '".$sequence."' AND cat = '".$category."' LIMIT 1");
    
    
    if ($sequence < $new_sequence) {
    	//If we are moving the image up, decrement the sequence of all images between the original sequence and the new sequence.
    	mysql_query("UPDATE photos SET sequence = sequence - 1 WHERE sequence BETWEEN '".$sequence."' AND '".$new_sequence."' AND cat = '".$category."'");
    } else {
    	//Else increment the sequence of all images between the original sequence and the new sequence.
    	mysql_query("UPDATE photos SET sequence = sequence + 1 WHERE sequence BETWEEN '".$new_sequence."' AND '".$sequence."' AND cat = '".$category."'");
    }
    
    //Set the image to the new position.
    mysql_query("UPDATE photos SET sequence = '".$new_sequence."' WHERE sequence = -1 LIMIT 1");
    
    //Go back to index
    header("location: index.php");
    
    ?>
    What's happening is that sometimes the photo gets moved to the correct position, other times it's one off. And occasionally when I check my database, I have duplicates of sequencing numbers...

    Can anyone figure out what's going on here?

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    header() does nothing more than send an http header. It does not stop script execution. To stop script execution, use exit;

    What type of field is sequence? varchar? int?

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2007
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks crmalibu.

    sequence is an int.

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Provide example values of the new and old sequence which cause each type of problem.

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2007
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, so it seems to be working now... which is weird because I haven't changed anything. So I guess it's alright, but I'll let you know if I find any bugs.

    Thanks for the help.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •