How do I update a table from an array?

Question

$query = "SELECT users FROM tabl ORDER BY RAND()";
$result = mysql_query ($query)
    or die ("Query '$query' failed with error message: \"" . mysql_error () . '"');

while ($row = mysql_fetch_array($result)) {
    $users[] = $row[0];
}

$current = end($users);
$partners = array();
foreach ($users as $user)
{
        $partners[$user] = $current;
        $current = $user;
}
print_r($partners);

$query2 = "UPDATE tabl SET partner = {$partners[0]} WHERE users = '$users'";
mysql_query ($query2)
    or die ("<br>Query '$query2' failed with error message: \"" . mysql_error () . '"');

That's the code I'm working with. Everything is good until query2. I've tried every variation I can think of, but nothing works.

The table on has two fields: users and partners. The code pulls the users in random order, then assigns them to eachother in a circle. I need to populate the partners field with the assignments.

Answer 1

Place the update query inside the foreach loop, then you have the partner and user variables to hand without diving into the array later on:

foreach ($users as $user)
{
        $partners[$user] = $current;
        $current = $user;
        $query2 = "UPDATE tabl SET partner = '{$partners[$user]}' WHERE users = '{$user}'";
        mysql_query ($query2)
          or die ("<br>Query '$query2' failed with error message: \"" . mysql_error ()
}

Answer 2

Use WHERE users IN ('.implode(',',$users).')'

Answer 3

I would change the code to:

$current = end($users);
$partners = array();
foreach ($users as $user)
{
    $partners[$user] = $current;
    $current = $user;
    $query2 = "UPDATE tabl SET partner = {$partners[$user]} WHERE users = '$user'";
    mysql_query ($query2)
        or die ("<br>Query '$query2' failed with error message: \"" . mysql_error () . '"');
}
print_r($partners);

But you could also do the following depending on the outcome you desire:

$userList = join(',', $users);
$query2 = "UPDATE tabl SET partner = {$partners[0]} WHERE users IN ($userList)";
mysql_query ($query2)
    or die ("<br>Query '$query2' failed with error message: \"" . mysql_error () . '"');