Reordering positions

[Fleur]

I'm building a CMS website where 5 images are displayed per page and the user can upload new images and select the position of the image / change the position after uploaded. I got help to be able to shift positions down if there is a duplicate - if you select a position (eg 2) that is already in use then the record that you are updating/adding will keep the position you have given it (ie 2), but the record that had that position before is shifted down by 1 position (to 3) and the record that was 3 becomes 4 etc.

Now my problem is that if I move an image to a lower position then I need the other images to move positions up automatically so that you always have positions 1-5 and no blanks.

I'm using PHP 5.3.0 and MySql 5.1.36. Please let me know if you have any suggestions.

[trq]

Have you made any attempt at this yourself? A little logical thinking will get you a long way in programming.

[Fleur]

I have made some attempts, but thought that someone might have a simple solution and that my code could just confuse things. Seeing as that hasn't helped yet, here's the latest code that I've tried for updating pics (will be able to adjust to saving new images).

At the moment if I move an image position down then all of the other will move down 1 position. If I move an image up then it works correctly and only moves images up 1 position if they are below that image.

 

I'm using objects for the pics called Photograph and have declared variables for each parameter (id, filename, page_id, position).

 

Form code (refs to Page/Photograph/Session/database objects irrelevant):

<?php if (isset($_POST['submit']))
	{
		$id					= $_POST['id'];
		$photo->id			= $_POST['id'];
		$page_id				= $_POST['page_id'];
		$photo->page_id		= $page_id;
		$photo->position 		= $_POST['position'];
		$photo->filename		= $_POST['filename'];
		$position				= $_POST['position'];
		$orig_position			= $_POST['orig_position'];

		if($photo->update())
		{	//Success
			(irrelevant)
		} else
		{	// Failure
			(irrelevant)
		}

	}
?>

<form action="edit_photo.php?id=<?php echo $photo->id; ?>" method="post">
<input type="hidden" name="id" value="<?php echo $photo->id; ?>"/>
<input type="hidden" name="filename" value="<?php echo $photo->filename; ?>"/>
<input type="hidden" name="orig_position" value="<?php echo $photo->position; ?>"/>
<label>Position:</label>
<select name="position">
	<?php 

		$page_photos=Photograph::get_photos_for_page($page_id);

		foreach($page_photos as $page_photo)
		{$photo_count++;}

		for($count=1; $count <= $photo_count; $count++)
		{
			echo "<option value=\"{$count}\"";
			if ($count == $photo->position)
			{
				echo " selected=\"selected\"";
			}
			echo ">{$count}</option>";
		}
	?>	
</select>	

<label>Page:</label>
<select name="page_id">
	code to select page (irrelevant)
</select>
<input type="submit" name="submit" value="Update Photo" />
<input type="reset" value="Reset">							

</form>

Photograph object code:
<?php 

class Photograph
{
protected static $table_name="images";
protected static $db_fields = array('id', 'page_id', 'filename', 'position');
public $id;
public $page_id;
public $filename;
public $position;	

private $temp_path;
protected $upload_dir="xxx";


public function update()
{
	global $database; // database connection
	global $orig_position;

	if ($orig_position < $this->position)
	{
		$this->fill_gaps();
	} elseif ($orig_position > $this->position)
	{
		$this->check_double();
	}

	// code to update current record (irrelevant)
}

public function check_double()
{				
	global $database;

	$check_dbl = "SELECT * FROM ".self::$table_name." WHERE page_id={$this->page_id} AND position={$this->position}";
	mysql_query($check_dbl);
	if ($database->affected_rows() >0)
	{
		$update = "UPDATE ".self::$table_name." SET position=position+1 WHERE page_id={$this->page_id} AND position>={$this->position}";
		$database->query($update);
	}
	return true;
}

public function fill_gaps()
{				
	global $database;

	$fill = "UPDATE ".self::$table_name." SET position=position-1 WHERE page_id='{$this->page_id}' AND position>='{$gap}' AND position<='{$this->position}'";
	mysql_query($fill);

	return true;
}

}

?>

Please anyone help if you can!

[fenway]

Just use the "new" order, and you're done -- ignore the existing order.