duplicate entry problem

[beesman]

Hi all,

I'm coding a photo portfolio site & have a simple database of images, the primary key of which is the field 'img_id' (which is auto increment). My client has asked whether it will be possible to re-arrange the order in which the images are displayed on the site, e.g. if he wanted to 'move up' an image in priority from 10th to 1st.

I know that I could ORDER BY 'img_id', or by any other field for that matter. However, as I understand it, it wouldn't be possible to change the primary key of a row, so I created a separate 'sequence' field, 'sort_id'. However, if for example I want to change the sort_id of an image from 5 to 2, to avoid duplication I need to 'move up' all images with a 'sort_id' >=2. I've tried using the code below which I found in thse forums, but it's not working 😕 :

$query1 = "UPDATE images SET sort_id = (sort_id + 1) WHERE sort_id >= '$sort_id';";
$result1 = mysql_query($query1) or die ("Error in query: $query1. " . mysql_error());

$query2 = "UPDATE images SET sort_id = '$sort_id' WHERE img_id = '$img_id'";
$result2 = mysql_query($query2) or die ("Error in query: $query2. " . mysql_error());

Any help really appreciated 🙂

[jhunrivera]

Why don't u add an additional column like "position" instead of using the img_id.
Can u post the complete code?

[beesman]

Yeah, I have added an additional column - 'sort_id'. The strange thing is that if I perform a test query within phpmyadmin, eg UPDATE images SET sort_id = (sort_id + 1) WHERE sort_id >= 3 , it works without any problem, but if I get the sort_id from an html form it doesn't...

The relevant code is below (apologies if it's too long)

<FORM  method="post" action="edit2.php?img_id=<? echo $img_info["img_id"] ?>">
<div class="form">
<span class="label">Category of photo:</span>
<span class="formw"><SELECT name="category" class="input"><? echo "$catchoices"; ?></SELECT></span>
</div>
<div class="form">
<span class="label">Sub-Category of photo:</span>
<span class="formw"><SELECT name="subcategory" class="input"><? echo "$subcatchoice"; ?></SELECT></span>
</div>
<div class="form">
<span class="label">Photo title:</span>
<span class="formw"><input size="50" maxlength="250" type="text" name="img_title" class="input" value="<? echo $row->img_title; ?>"></span>
</div>
<div class="form">
<span class="label">Long description:</span>
<span class="formw"><textarea name="img_descr" cols="40" rows="10" class="input"><? echo $row->img_descr; ?></textarea></span>
</div>
<div class="form">
<span class="label">Alt text:</span>
<span class="formw"><input size="50" maxlength="250" type="text"  class="input" name="img_alt"  value="<? echo $row->img_alt; ?>"></span>
</div>
<div class="form">
<span class="label">Display order number:</span>
<span class="formw"><input size="5" maxlength="5" type="text"  class="input" name="sort_id"  value="<? echo $row->sort_id; ?>"></span>
</div>
<div class="form">
<span class="formw"><input type="Submit" name="submit" value="Update" class="input"></span>
</div>
</form>
<?
	}
	else
	{
		echo "<font size=-1>Sorry, that photo could not be located in our database.</font>";
	}
}
else
{
	$errorList = array();
	$count = 0;
	if (!$img_title) { $errorList[$count] = "Invalid entry: Title"; $count++; }
	if (!$img_descr) { $errorList[$count] = "Invalid entry: Description"; $count++; }
	if (!$img_alt) { $img_alt = $def_alt; }
	if (sizeof($errorList) == 0)
	{
		$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
		mysql_select_db($db) or die ("Unable to select database!");

//category & subcategory sub-routine to allow insertion of image category & subcategory values below
$catchoice=$HTTP_POST_VARS["category"];
$sql = "SELECT cat_id FROM cats WHERE cat_name = \"".$catchoice."\"";
$catval = mysql_query($sql,$connection) or die("Couldn't execute query.");
while ($row = mysql_fetch_array($catval)) {
$catresult = $row["cat_id"];
}

$subcatchoice=$HTTP_POST_VARS["subcategory"];
$sql2 = "SELECT subcat_id FROM subcats WHERE subcat_name = \"".$subcatchoice."\"";
$subcatval = mysql_query($sql2,$connection) or die("Couldn't execute query.");
while ($row = mysql_fetch_array($subcatval)) {
$subcatresult = $row["subcat_id"];
}

$query1 = "UPDATE images SET sort_id = (sort_id + 1) WHERE sort_id >= $sort_id;";
$result1 = mysql_query($query1) or die ("Error in query: $query1. " . mysql_error());

$query2 = "UPDATE images SET img_title = '$img_title', img_descr = '$img_descr', img_alt = '$img_alt', img_cat = '$catresult', img_subcat = '$subcatresult', sort_id = '$sort_id' WHERE img_id = '$img_id'";
$result2 = mysql_query($query2) or die ("Error in query: $query2. " . mysql_error());

echo "<br/><br/>Update successful!";
mysql_close($connection);
	}
	else
	{
		echo "<font size=-1>The following errors were encountered: <br>";
		echo "<ul>";
		for ($x=0; $x<sizeof($errorList); $x++)
		{
			echo "<li>$errorList[$x]";
		}
		echo "</ul></font>";
	}
}
?>
</body>
</html>