[SOLVED] Updating multiple rows with one query

[gray8110]

I'm processing a form with a bunch of radio buttons. I'm getting the form to work and I've tested that everything works, but when I get to the point of updating the appropriate rows in the MySQL table, only one update is getting performed.

 

Here's the relevant code:

<?php
$result = mysql_query("SELECT * FROM obra ORDER BY ID ASC", $connection);
$userID = 1 . ',';
$numRows = mysql_num_rows($result);
$position = 1;

while ($position <= $numRows) {
$name = $_POST["$position"];

if ($name == 'definite') {

	$qry = "UPDATE obra SET
			definite = '$userID'
			WHERE $position = ID";
}

elseif ($name == 'maybe') {

	$qry = "UPDATE obra SET
			maybe = '$userID'
			WHERE $position = ID";
}


$position++; // increment the position
}
?>

 

The query is succesful in making an update but it only updates one record when there are multiple records that fit the criteria. More to the point, it is only updating the row with the highest ID value which confuses me even more.  I tested the code without the update queries by echoing the variables as they would update the table... This worked flawlessly - it returned all of the selected values. For some reason, the update queries aren't looping.

 

What am I missing? Thanks

[cwarn23]

$position++; // increment the position
}

Try replacing the above with the below code with the below code

if (!empty($qry))
    {
    mysql_query($qry);
    }
unset($qry);
$position++; // increment the position
}
if (!empty(mysql_error()))
    {
    die(mysql_error());
    }

That should do the trick as the variable $qry kept on being replaced by a new query.

[gray8110]

That should do the trick as the variable $qry kept on being replaced by a new query.

 

That makes sense, but I get the following error when I run the script:

Fatal error: Can't use function return value in write context

 

The error occurs on the line immediately after the while loop is closed if (!empty(mysql_error()))

 

$result = mysql_query("SELECT * FROM obra ORDER BY ID ASC", $connection);
$userID = 1 . ',';
$numRows = mysql_num_rows($result);
$position = 1;

while ($position <= $numRows) {
$name = $_POST["$position"];

if ($name == 'definite') {

	$qry = "UPDATE obra SET
			definite = '$userID'
			WHERE $position = ID";
}

elseif ($name == 'maybe') {

	$qry = "UPDATE obra SET
			maybe = '$userID'
			WHERE $position = ID";
}

if (!empty($qry))
    {
    mysql_query($qry);
    }
unset($qry);
$position++; // increment the position
}
if (!empty(mysql_error()))
    {
    die(mysql_error());
    }

$result = @mysql_query($qry);
//Check whether the query was successful or not
if($result) {
	header("location: ../test.php");
	exit();
}else {
	die("Query failed");
}

?>

 

 

[gray8110]

 

That makes sense, but I get the following error when I run the script:

Fatal error: Can't use function return value in write context

 

The error occurs on the line immediately after the while loop is closed if (!empty(mysql_error()))

 

 

The query is actually working... all updates are now being completed... the failure is occuring on the if/die portion...

[cwarn23]

I just did a test and look like its not possible to use the empty() function on mysql_error(). So try the following:

$result = mysql_query("SELECT * FROM obra ORDER BY ID ASC", $connection);
$userID = 1 . ',';
$numRows = mysql_num_rows($result);
$position = 1;

while ($position <= $numRows) {
$name = $_POST["$position"];

if ($name == 'definite') {

	$qry = "UPDATE obra SET
			definite = '$userID'
			WHERE $position = ID";
}

elseif ($name == 'maybe') {

	$qry = "UPDATE obra SET
			maybe = '$userID'
			WHERE $position = ID";
}

if (!empty($qry))
    {
    mysql_query($qry);
    }
unset($qry);
$position++; // increment the position
}
if (strlen(mysql_error())>5)
    {
    die(mysql_error());
    }

$result = @mysql_query($qry);
//Check whether the query was successful or not
if($result) {
	header("location: ../test.php");
	exit();
}else {
	die("Query failed");
}

?>