#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Posts
    35
    Rep Power
    8

    Another riddle to solve - missing links


    Hello all, I have yet another riddle I'm trying to sort out and having a heck of a time doing so.

    I have a table (links) set up as follows:

    ID (primary, autoInc)
    description (varchar, 255)
    parent (int)
    storyID(int)
    pageID(int)
    sortOrder(int)

    The field I am concerned with is "sortOrder".

    This field stores a number from 1 - 4, because there are only 4 options allowed: Option1 - Option4.

    On my site, I allow users to change the sort order of their options, either moving them up or down, so the number in the field will change, but again, will always be a number between 1 and 4.

    Here is the issue I have:

    Users may add only 1 option, 2, 3, or all 4 options to begin with. They are stored in order entered.

    Users may also DELETE an option. This is the main problem, because if they delete an option, there is now a blank space. For example, if they delete #2, the order will be 1, 3, 4 (if they have all 4).

    I need to figure out a way in which, if they delete an option, the remaining options are put back in order. So, if they delete 2, 3 becomes 2, and 4 becomes 3, leaving the blank spot at the end so another option can be added in it's place (being assigned #4).

    This must be done because of the order swapping. If there is a blank space, they will not be able to swap the position of their options around, it requires that both fields have a current position # in order to work.

    I hope I've made myself clear. Care to take a crack at this?

    Thank you!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Posts
    35
    Rep Power
    8
    Well, after having written out my problem, it helped me to think of a solution. It's not pretty, by far, but it works perfectly. In case anyone is wondering, here is what I did:

    PHP Code:
    //Get total number of options for current page
    $sql="SELECT * FROM links WHERE storyID='$storyID'";
    $result=mysql_query($sql$db);
    $totalOptions=mysql_num_rows($result);

    //Get the record for the specific linkID
    $sql="SELECT * FROM links WHERE pageID='$link_page_ID'";
    $result=mysql_query($sql$db);
    $row=mysql_fetch_array($result);

    //Find the sort order of that linkID
    $deletedOrderNumber=$row['sortOrder'];

    //Delete the linkID record and the associated page for it
    $sql="DELETE FROM links WHERE pageID='$link_page_ID'";
    $result=mysql_query($sql$db);

    $sql="DELETE FROM pages WHERE storyID='$link_page_ID'";
    $result=mysql_query($sql$db);

    //Update the sort order of any options following the deleted option
    IF ($totalOptions>1)
        {
    $swap=($deletedOrderNumber+1);
    $sql="UPDATE links SET sortOrder='$deletedOrderNumber' WHERE sortOrder='$swap' AND storyID='$storyID'";
    $result=mysql_query($sql$db);
        }
    IF (
    $totalOptions>2)
        {
    $swap=($deletedOrderNumber+2);
    $newOrder=($deletedOrderNumber+1);
    $sql="UPDATE links SET sortOrder='$newOrder' WHERE sortOrder='$swap' AND storyID='$storyID'";
    $result=mysql_query($sql$db);
        }
    IF (
    $totalOptions>3)
        {
    $swap=($deletedOrderNumber+3);
    $newOrder=($deletedOrderNumber+2);
    $sql="UPDATE links SET sortOrder='$newOrder' WHERE sortOrder='$swap' AND storyID='$storyID'";
    $result=mysql_query($sql$db);
        };

        
    ?> 
    If anyone has any comments on how I could improve this, please do express them. I'm trying to learn as best I can so different ideas are more than welcomed.

IMN logo majestic logo threadwatch logo seochat tools logo