php mass update database field value

frieza · 04-01-2008, 01:30 PM

here is the php script i am working on

Code:

<?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)

chrism01 · 04-01-2008, 05:42 PM

Well, I'd check that your vars are what you think e.g. echo out the SQL just before using it. Also, check the exact error from PHP eg
printf("Error: %s\n", mysqli_error($link));
as per
http://au.php.net/manual/en/function.mysqli-query.php

prad77 · 04-02-2008, 03:18 PM

You could find some more info from the error msgs.

// Perform Query
$result = mysql_query($query);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}

Gentoo

AdaHacker · 04-02-2008, 05:50 PM

The problem isn't immediately obvious to me. You should post the actual SQL you're executing and crank up error_reporting and show us what messages you're getting.

Also, you really don't need that for loop. You can do the whole update in one query:

Code:

UPDATE yourtable SET id = id - 1 WHERE id BETWEEN :deleted_id + 1 AND :highest - 1

The placeholders :deleted_id and :highest are your $_GET['id'] and $_GET['highest'] respectively. Also, since you're already using mysqli, you might want to look into prepared statements rather than using string concatenation to put data into your queries.