Update command not working properly in PHP

Question

$query = "UPDATE members SET userrole ='$userrole' WHERE member_id = '$member_id'";

Sometimes it updates and sometimes it does not... i don't know whether the syntax is correct.

userrole and member_id has array of values. The update query is performed for multiple items being checked.

There is no error in mysql connection or error.... for the first time the update does not happen and after that it happens

Answer 1

It's all about debugging. First, I would check if there is a mysql error to start you off.

$query = "UPDATE members SET userrole ='$userrole' WHERE member_id = '$member_id'";
echo mysql_errno($yourmysqlconnection) . ": " . mysql_error($yourmysqlconnection) . "\n"

Then doublecheck your $userrole and $memberid Variable. Post your findings here, and we might to help you further

Good Luck, rAyt

Answer 2

1. Unless you've skipped a bit you're not escaping your inputs - not good!

   $userrole = mysql_real_escape_string($userrole);

2. If $member_id is an array, you can't just put it into an update like that. I suspect you want something like this (after escaping of course!)

   $query = "UPDATE members SET userrole ='$userrole' WHERE member_id IN (" . implode(",", $member_id) . ")";

Answer 3

This cannot work, if both variables contain arrays. You need to update the items one by one (or use a clumsy MySql CASE statement, which I won't explain here):

foreach ($userrole as $key => $role) {
    $query = "UPDATE members SET userrole ='$role'
              WHERE member_id = '$member_id[$key]'";
    // perform query ...
}

Or if only $member_id is an array:

$query = "UPDATE members SET userrole ='$userrole'
          WHERE member_id IN ('" . implode(',', $member_id) . "')";
// perform query ...

You should further rename your variables to reflect that they are arrays. Just use the plural:

$userrole => $userroles
$member_id => $member_ids

On more thing: Are these values coming from a HTML form? If yes, you should really sanitize them using mysql_escape_string(). This can be done for multiple values at once using array_map():

$userroles = array_map('mysql_escape_string', $userroles);
$member_ids = array_map('mysql_escape_string', $member_ids);