Need Help

What I am trying to do, is say I have 1 piece of data with id 1, one with id 2, and one with 3. Using this code, I can successfully delete the selected data by id, but also what I want to do is for every id above the one deleted, I want to make them go down by 1, like the one with id of 2 would go to 1, and the one with id of 3 would go to 2, the code below is what I have tried, and been unsuccessful

This is my code so far:

<?php

include("global.php");

connect();

$sql = "SELECT * FROM `banners` WHERE admin='1' AND username='".$session_username."'";
$res = mysql_query($sql) or die(mysql_error());

if(mysql_num_rows($res) == 1)
{

$sql2 = "SELECT * FROM `banners`";
$res2 = mysql_query($sql2) or die(mysql_error());

	if(isset($_GET['delete']))
	{


	$deleteid = $_GET['delete'];
		if(!isset($_GET['sure']))
		{
			echo "<center>Are you sure you want to delete this data?<br><br>";
			echo "<a href='admin.php?delete=".$deleteid."&sure=1'>Yes, I am sure</a<br>";
			echo "<a href='admin.php'>No, I am not.</a></center>";
		}else
		{
		
		
		$sqldelete = "DELETE from `banners` WHERE id='".$deleteid."'";
		$resdelete = mysql_query($sqldelete) or die(mysql_error());
		
		$upsql = "SELECT * FROM `banners` WHERE id > '".$deleteid."'";
		$upres = mysql_query($upsql) or die(mysql_error());
		
		$array[] = $upres;
		
		foreach($array AS $idnum)
		{
		
		$idstart = $deleteid + $idnum;
		$idupdate = $idstart - 1;
		
		$sqlupdate = "UPDATE `banners` SET id='".$idupdate."' WHERE id='".$idstart."'";
		$sqlres = mysql_query($sqlupdate);
		
		
		}
		
		echo "You have successfully deleted the item with an ID of ".$deleteid."!";
		
		}
	}
else
{
while($assoc = mysql_fetch_assoc($res2))
{

$id = $assoc['id'];
$username = $assoc['username'];
$sitename = $assoc['sitename'];
$bannerurl = $assoc['bannerurl'];
$url = $assoc['url'];

echo "<center><table border='1'>";
echo "<tr><td>".$username."</td><td>".$sitename."</td><td>".$bannerurl."</td><td>".$url."</td><td><a href='admin.php?delete=".$id."'>Delete</a></td></tr>";
echo "</table></center>";
}
}


}else
{
	die("You do not have permission to access this area");
}


?>

Code (markup):

 

id is autogenerated? and i am unable to understand purpose for this id shift?

please put more light on your purpose, so that we can suggest better solutions if possible.

 

ID is set using the GET method, I have a list of Delete links on another page, which then displays admin.php?delete=1 , and that code is admin.php, and the id is 1 because delete=1

 

then you need not modify id in database. admin.php creates link for each id to delete them which need not start from 1 only.

 

But when an id is deleted, I want every one after that to reduce by one, that is my aim as well.

 

ok, following query will reduce id greater than specified id by 1.

update `banners` set id = id - 1 where id > 2

however, how many records do you feel banners table will have? is id an autogenerated column in banners table?
this query might give error if id 2 or whatever is given in query is already in table.

 

That would only work if there was 1 above.

 

nope. that query will reduce all id > 2 by 1.

 

So with the SET thing, how it sais id=id-1, would I do it like this:

"UPDATE `banners` SET id='id-1' WHERE id > '".$deleteid."'";

Would that be the query?

 

It needs modification, but that will be the query, however you please try it on a duplicate table first to make sure it gives you desired result.

Use following:

"UPDATE `banners` SET id = id - 1 WHERE id > $deleteid";

 

It works, thank you very much

 

seems like a private chat... :S