Updating Database With Inifite Menu Lists

[RobinScott]

Odd title, I know, but I'm struggling to explain it!

Basically I'm coding my own mini-CMS for a personal project. Nothing for public release or anything, just something to get me more in the know about PHP coding.

I'm trying to create a system that will manage "blocks" for my left hand navigation menu. Basically each block has a title and some content, and I want to be able to reorder the blocks. The higher the position number of the block, the higher up it is on the page.

In the admin area I want the user to be able to reorder the position number of the blocks, similar to how in Inivision Board you can reorder the position number of the forums using menu lists.

The problem I'm encountering is that there could be any number of blocks, and each menu list needs it's own unique ID. So take this code as an example...

	$query = "SELECT * FROM leftnav ORDER BY pos ASC";
	$result = mysql_query($query);
	$num = mysql_num_rows($result);

// Total number of positions
$nopos = $num + 1;

$inc = "0";
while ($inc < $num)
{
$row = mysql_fetch_array($result);
$id = $row['id'];
$pos = $row['pos'];
$title = $row['title'];
?>
      <tr>
        <td>
	<select name="form_pos_<?=$inc ?>" id="form_pos_<?=$inc ?>">
		<?
		$no = "1";
		while ($no < $nopos)
		{
			if ($no == $pos)
			{
		?>
          	<option value="<?=$no ?>" selected><?=$no ?></option>
		<?
			}
			else
			{
		?>
			<option value="<?=$no ?>"><?=$no ?></option>
		<?
			}
			++$no;
		}
		?>
        </select>

This creates a menu list with an incrementing amount of numbers depending on how many blocks are in the database. The code will highlight the position that is currently listed in the database.

The problem now comes when I try to update the database with this content -- since I don't know how many blocks are going to be present in the database I can't UPDATE the table by simply doing a while loop -- there's no way I could use..

UPDATE `leftnav` SET `pos` = '$form_pos_$inc';

I need someway of incrementing that variable number, but how?

So what do people recommend?

I find if I think about it too much my head just goes blank.

Any help would be much appreciated...I'm just confused!

Regards.

[dnast]

I'm not exactly sure if I understood what you're trying to do, but I'll give it a try. I assume that the select box allows you insert a specific item to a certain position. If so, I'd do something like this...

<?php

//Shifting the other blocks around
if ($old_pos < $new_pos) {
    $query = "UPDATE `leftnav` SET `pos` = (`pos` - 1)
WHERE `pos` <= $new_pos AND `pos` > $old_pos";
}
else {
    $query = "UPDATE `leftnav` SET `pos` = (`pos` + 1)
WHERE `pos` >= $new_pos AND `pos` < $old_pos";
}

$result = mysql_query($query);

//Then put the selected block where it's supposed to go
$query2 = "UPDATE `leftnav` SET `pos` = $new_pos WHERE `id` = '$id'";
$result2 = mysql_query($query);
?>

Untested and I'm not exactly sure if that's what you were going for. Basically it sees if the new position for the item is higher or lower and changes the affected items accordingly. This shouldn't affect anything beyond the range between the old position and new. If this isn't what you were going for, hopefully you can use something from it.

If not, I'll try to help if you clarify more.