ordering records

ttnyc3

Hi,

I am trying to move records within a recordset. I am having a problem with the code I have written. It should be setting the moved record's drop_num to zero, updating all records above the new drop_num to drop_num++ then update the moved record to the new drop position. Here it is, can anyone help?

if ($cdrop != $ndrop)
{
$query = "update subscriber set drop_num = 0 where sid = $sid";
mysql_query($query);
$udrop = $ndrop++;
$query = "select drop_num, sid from subscriber where drop_num >=".$ndrop." and rid =".$rid;
$result = mysql_query($query);
while ($row = mysql_fetch_array($result))
{
$uquery = "update subscriber set drop_num = $udrop where sid = ".$row['sid'];
mysql_query($uquery);
$udrop++;
}
$query = "update subscriber set drop_num = $ndrop where sid = $sid";
mysql_query($query);
}

dnast

You didn't say what was wrong and the variables are kind of cryptic so I could be misunderstanding.

Here's my try at it anyway.

if ($cdrop != $ndrop)
{
    //You probably don't need this first query, but I'm just following instructions. :P
    $query = "UPDATE subscriber SET drop_num = 0 WHERE sid = $sid";
    mysql_query($query);

//This replaces your loop.
$query = "UPDATE subscriber SET drop_num = drop_num + 1 WHERE drop_num > $ndrop AND rid = $rid";
mysql_query($query);

//This is pretty obvious
$query = "UPDATE subscriber SET drop_num = $ndrop WHERE sid = $sid";
mysql_query($query);
}

ttnyc3

Sorry, I should have been a little more descriptive.

I have a subscribers table that looks something like:

|1 | 2 | 1 |Homer|Simpson|

|2 | 2 | 4 |Mickey|Mouse |

|3 |2 |3 |Minnie|Mouse |

|4 |2 |2 |Wile |Coyote |

I need to be able to change drop positions via moves, deletes or inserts. I have been focusing on moving them as inserting and deleting would be easy after that is figured out. Using the query prescribed or my original method duplicate drop_nums are created in the new position and the original drop position is not compensated for. rid is a foreign key so could be any number of values. I really appreciate any help on this as it is driving me batty.

dnast

Hmm...I think I see what's wrong.

For the second query, try this instead.

$query = "UPDATE subscriber SET drop_num = drop_num + 1 WHERE drop_num > $ndrop AND drop_num < $cdrop AND rid = $rid";

This assumes that $cdrop is the previous drop_num and the new drop_num is less than the previous.

If the previous drop_num is less than the new one then it should be

$query = "UPDATE subscriber SET drop_num = drop_num - 1 WHERE drop_num < $ndrop AND drop_num > $cdrop AND rid = $rid";

It was kind of hard to picture in my head, but I think this should work. Good luck!

ttnyc3

Sorry for taking so long to respond, I decided to move on to some other things and give this a rest. The image attached shows kind of what I am trying to accomplish. The drop_num is selected in the form, the user needs to be able to move them up or down. If you would like, I could send you a user name and password to the staging site so you could see it in action but I would prefer to do that directly rather than post it here in an open forum. I really, really appreciate your help with this, it is driving me batty as it should work, just doesn't.