php mass update database field value question

[hopper]

here is the php script i am working on

<?php
$mysqli = new mysqli("localhost", "root", **** "htf");

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
else {
    /*$sql = "DELETE FROM ".$_GET['type']." WHERE id=".$_GET["id"];
    mysqli_query($mysqli, $sql);
    echo    "File Removed<br>"; */
for ($x = $_GET['id']; $x <= $_GET['highest']-1; $x++) {
$y=$x+1;
$sql = "UPDATE ".$_GET['type']." SET id=".$x." WHERE id = ".$y;
echo $sql."<br>";
    mysqli_query($mysqli, $sql) or die('failed');
}
mysqli_close($mysqli);
}
//echo "<meta http-equiv=\"refresh\" content=\"0; URL=\"./manage.php\">";

the idea is to delete a record from a database and decrement the remaining id numbers by 1 to fill the gap, but the sql query keeps dieng, any suggestions?
(yes the delete part is commented out for the time being until i can get the update part running)

 

[woiwky]

If that's your real password there, please edit that out asap.

But anyway, the update query should be fine like this:

$sql = "UPDATE {$_GET['type']} SET id= id - 1 WHERE id > {$_GET['id']}";

However, assuming the id field auto-increments, you should probably decrease the auto-increment value by 1 after doing that update.

 

[hopper]

hmm
when given values it echoes out to
UPDATE Other SET id= id - 1 WHERE id > 2
but the query still dies
p.s. it was and wasn a real password, im working on a testing server that isnt internet accessable

 

[woiwky]

Can you make it die with mysqli_error() instead of 'failed' and post the error?

 

[hopper]

die(mysqli_error());

returns nothing

 

[woiwky]

I should have noticed this sooner. You're creating a mysqli object with the connection statement and setting $mysqli to that object. But then you use the functions for the queries instead of the object's methods, and use the object as the connection link. You need to either change the function calls to method calls of the object $mysqli (such as $mysqli->query()), or change the connection line to:

$mysqli = mysqli_connect("localhost", "root", **** "htf");

I think that'll fix it.

 

[hopper]

updated program

<?php
$mysqli = mysqli_connect("localhost", "root", "***", "htf");

if (mysqli_connect_errno()) {
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
}
else {
	$sql = "DELETE FROM ".$_GET['type']." WHERE id=".$_GET["id"];
	echo $sql;
	//mysqli_query($mysqli, $sql);
	echo	"File Removed<br>";
//for ($x = $_GET['id']; $x <= $_GET['highest']-1; $x++) {
//$y=$x+1;
$sql = "UPDATE {$_GET['type']} SET id= id - 1 WHERE id > {$_GET['id']}";
echo $sql."<br>";
	mysqli_query($mysqli, $sql) or die(mysqli_error());
//}
mysqli_close($mysqli);
}
//echo "<meta http-equiv=\"refresh\" content=\"0; URL=\"./manage.php\">";
?>

still nothing
just outputs

DELETE FROM Other WHERE id=3File Removed
UPDATE Other SET id= id - 1 WHERE id > 3

and does nothing

 

[woiwky]

Well you are missing the Q in mysqli_error(). However, if you didn't get a fatal error because of that, then that means the die() was not executing. In that case, are you sure there are records in Other with an id greater than 3? I would check the db since there doesn't appear to be any error in the code.

 

[hopper]

hmm
well this is the menu that is being updated
http://localhost/flashtheater/
if the query was executing the numbers for menu 'other' should be
1
2
3
3
4
5
the goal eventually will be to delete row 3 so
1
2
3
4
5
6
7

becomes

1
2
3
4
5
6

instead of
1
2
4
5
6
7
Edit:
silly me
http://hoppernet.exofire.net/flashtheater/
linking to localhost will do no goo

 

[woiwky]

Well, of course I can't access your localhost, so I can't see that. However, let me just confirm that the table name is 'Other' not 'other', correct? But even still, mysqli_error() should only return an empty string if there was no error. So I don't believe the query itself is wrong anyway. Just to make sure, though, try putting 'failed' back in place of mysqli_error(). If it's returning false but no error is being made, then this is very strange indeed.

***

Nevermind that thing about your localhost, I just read your edit.

 

[hopper]

put 'failed' back in

DELETE FROM Other WHERE id=2File Removed
UPDATE Other SET id= id - 1 WHERE id > 2
failed
[code]

 

[woiwky]

Alright, can you run that query in phpmyadmin and see what it says?

 

[hopper]

Error

SQL query:

UPDATE Other SET id = id -1 WHERE id >2

MySQL said: Documentation
#1062 - Duplicate entry '2' for key 1
hmm maybe i should try with the delete in place
Edit:
ok that seems to work..
Edit:
grr i spoke too soon
now its deleting everything past that point
Edit:
nvm my refresh code was broken

 

[woiwky]

So it's working well now? I should suggested to test it in phpmyadmin sooner. I was completely overlooking the fact that id had to be unique. It's very strange that mysqli_error() didn't come up with anything, though.