Mysql DELETE problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
arukomp
Forum Contributor
Posts: 113
Joined: Sun Sep 24, 2006 4:22 am

Mysql DELETE problem

Post by arukomp »

Hello,

I have a code which should delete user and all it's data. Here's the code:

Code: Select all

if ($action == 'delete') {
	$id = $_GET['id'];
	$sql = "DELETE * FROM `users` WHERE id = $id";
	$result = mysql_query($sql);
	$sql = "SELECT id FROM users ORDER BY id DESC LIMIT 1"; // this is to get the highest id
	$result = mysql_query($sql);
	$re = mysql_fetch_array($result);
	$max = $re['id']; // highest id
	$id += 1;
	while($id <= $max){  // this updates all user's ids bellow the deleted user
		$sql = "UPDATE users SET id = id - 1 WHERE id = $id";
		$result = mysql_query($sql);
		$id += 1;
	} // while
}
if() sentence executes successfully, but user is not deleted and ids are not updated. while() sentence doesn't even execute. I have put echo "hello"; in while() to test if it has been executed and echo didn't execute.

Maybe someone sees a problem here?

Any help would be appreciated.

Thanks
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

remove the * from the delete statement.

should be like this:

Code: Select all

delete from myTable where ...
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

And don't change the id. Make it an auto_increment field and leave it alone.
Post Reply