Need to re-sequence records

[ClevaTreva]

Hi

I have a mySQL db table with records that are, effectively, blocks of text, like paragraphs in a story (for illustration).

Via an on-line form, I need to display these in the order the user requires. No problem. I add a field called 'sequence' and the user can specify an integer representing the order in which the records display, lowest first, as they enter each new record.

Next, they view the finished result listing all the records in that sequence. They want to move a paragraph. Now, they COULD be required to simply edit each record to re-order them, but the longer this table is, the more that becomes untenable.

I can put field on the form to allow the user to specify where to move the record (e.g. above record 10, below record 15, at the start, at the end, etc). I can post that off to a page to process it.

Does anyone have a snippet of code that would alter the 'sequence' field accordingly?

Let's assume the table looked like this:

contentid int(4) NOT NULL auto_increment (Primary & Unique)
contentsequence int(2) default NULL
bodypara text default NULL

Any help much appreciated, as I have been pondering this one for weeks. There must be an easy way to do this.

Trevor

[peppeto]

Make another column in db for example contentNr

then
if (isset($swh) && isset($swt)) {

	$query = "SELECT * FROM table ORDER BY contentNr";

	$result = @mysql_query ($query) or die ("<p><b>ERROR!!!</b></p><BR>".$query."<BR>");

	$j=0;

	$swhp=$swh;

	$swtp=$swt;

	while ($rows = mysql_fetch_object($result)) {

		$j+=1;

		if ($j==$swt) {$swtp = $rows->contentNr;}

		if ($j==$swp) {$swhp = $rows->contentNr;}

	}



	$query = "UPDATE table SET contentNr =0 WHERE contentNr =".$swhp;

	$result = @mysql_query ($query) or die ("<p><b>ERROR!!!</b></p><BR>".$query."<BR>");

	$query = "UPDATE table SET contentNr=".$swhp." WHERE contentNr=".$swtp;

	$result = @mysql_query ($query) or die ("<p><b>ERROR!!!</b></p><BR>".$query."<BR>");

	$query = "UPDATE table SET contentNr =".$swtp." WHERE contentNr=0";

	$result = @mysql_query ($query) or die ("<p><b>ERROR!!!</b></p><BR>".$query."<BR>");



}

<script language=javascript>

<!--

function swapList(lst,form) {

	var ind=form.selectedIndex;

	location.href="page.php?swt="+form.options[ind].value+"&swh="+lst;

} // -->

</script>

$query = "SELECT * FROM table ORDER BY contentNr";
$result = @ ($query) or die ("<p><b>ERROR!!!</b></p><BR>".$query."<BR>");
$cnt= mysql_num_rows($result);
$j=0;
while ($rows = mysql_fetch_object($result)) {
$j+=1;
$aw_id = $rows->id;
$aw_head = StripSlashes($rows->content);

<form>
<select name=sel<?=$aw_id?> onchange=javascript:swapList('<?=$j?>',this.form.sel<?=$aw_id?>);>
<?
for ($i=1; $i<=$cnt; $i++) {
if ($i==$j) {
?>
<option value=<?=$i?> selected><?=$i?></option>
<?
} else {

?>
<option value=<?=$i?>><?=$i?></option>

<?

		}
	}

?>

		</select>

</form>

Hoope this helps

[ClevaTreva]

Hi pepetto

I'll try this and get back to you, thanks 🙂

Trevor

[ClevaTreva]

Hi pepetto

Well, I hacked the code around a bit to get what I wanted (see below). HOWEVER, your idea to use a second sequence field did it. I put the revised location for the record in that field, (which by default has a large number, like 9999) and sort on both fields to get the records in the new order, then re-set the real sequence field, re-setting the dummy field to 9999 as it goes. Works like a charm.

Thanks for getting me over the block.

The extra bits of html code are just to make it easier to see using my db.

Edited to add:

I got to thinking if it could be done without the additional field, ie just using php to shuffle the records around. My original posted solution was 68 lines of code. This revised version is now 89 lines long. BUT, it doesn't need an extra field (that cost 13 lines of code), AND it formats the drop down select list in an easier to understand way (8 extra lines of code).

The database structure is still per my first post.

Here's the final code:

<?php
if (isset($contentresequencer))
{
$sequenceOKlessthanid=$contentresequencer-1;
$sequenceOKgreaterthanid=$contentsequence+1;
$resequencedid=$contentresequencer;
$sequenceSORTBYorder="DESC";

if ($contentresequencer>$contentsequence)
{
	$sequenceOKlessthanid=$contentsequence-1;
	$sequenceOKgreaterthanid=$contentresequencer;
	$resequencedid-=1;
	$sequenceSORTBYorder="ASC";
}

$query = "SELECT * FROM `tablename` 
WHERE `contentsequence`>$sequenceOKlessthanid AND `contentsequence`<$sequenceOKgreaterthanid 
ORDER BY `contentsequence` $sequenceSORTBYorder";
$result = @mysql_query ($query) or die ("<p><b>ERROR!!!</b></p><BR>".$query."<BR>"); 
$j=0;
while ($rows = mysql_fetch_object($result))
{
	$contentid = $rows->contentid;
	$updateset = $resequencedid;
	if ($j!=0)
	{
		$contentsequence = ($rows->contentsequence)+1;
		if ($sequenceSORTBYorder=="ASC") $contentsequence-=2;
		$updateset = $contentsequence;
	}
	$updatequery = "UPDATE `tablename` SET `contentsequence`='$updateset' WHERE `contentid`='$contentid'";
	$updateresult = @mysql_query ($updatequery) or die ("<p><b>ERROR!!!</b></p><BR>".$updatequery."<BR>");
 	$j+=1;
}

}

$query = "SELECT * FROM tablename ORDER BY contentsequence";
$result = @ ($query) or die ("<p><b>ERROR!!!</b></p><BR>".$query."<BR>");
$cnt= mysql_num_rows($result);
$j=0;
?>
<table border="1"><tr><td colspan="2" valign="top"><b>To move a paragraph to a new sequence position, select from the drop down list next to it the position you want the moved paragraph to:</b></td></tr>
<?
while ($rows = mysql_fetch_object($result))
{
$j+=1;
$contentsequence = $rows->contentsequence;
$contentid = $rows->contentid;
?>
<tr><td width="600"><?=stripslashes($rows->bodypara)?></td><td valign="top"><form name="hiddenresequenceform<?=$contentsequence?>" method="post">
<INPUT NAME="contentsequence" TYPE="hidden" VALUE="<?=$contentsequence?>">
<INPUT NAME="contentid" TYPE="hidden" VALUE="<?=$contentid?>">
<select name="contentresequencer" onchange="document.hiddenresequenceform<?=$contentsequence?>.submit()">
<?
$selected = "";
$beginning = "At the Beginning";
if ($j==1)
{
$selected = " selected";
$beginning = "1 (Current position)";
}
?>
<option value="1"<?=$selected.'>'.$beginning?></option>
<?
for ($i=2; $i<=$cnt; $i++)
{
$selected = "";
$currentposition = "";
if ($i==$j)
{
$selected = " selected";
$currentposition = " (Current position)";
}
if ($i!=($j+1))
{
$above = "";
if ($i>($j+1)) $above = "Above ";
?>
<option value=<?=$i.$selected.'>'.$above.$i.$currentposition?></option>
<?
}
}
?>
<option value=<?=$i?>>At the End</option></select></form></td></tr>
<?
}
?>
</table>

Trevor