Another riddle to solve - missing links

Dev Shed Forums Sponsor:

February 21st, 2013, 03:09 AM

Jax2

Registered User

Join Date: Jul 2007

Posts: 22

Time spent in forums: 5 h 34 m 23 sec
Reputation Power: 0

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!

February 21st, 2013, 03:32 AM

Jax2

Registered User

Join Date: Jul 2007

Posts: 22

Time spent in forums: 5 h 34 m 23 sec
Reputation Power: 0

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.

Another riddle to solve - missing links

Developer Shed Advertisers and Affiliates