Need help to update table with a single query and info from multidim. array

[tfe]

This my code.

 

$result = mysql_query("SELECT id, owner, folder, COUNT(folder) FROM messages WHERE id IN(". implode(', ', $_POST['msg_id']).") GROUP BY folder") or die ('Error');
while ($row = mysql_fetch_assoc($result)) {

if ($row['owner'] != $_SESSION['userid']) {
echo "You are not allowed to delete this message";
die;
}
$folderid = $row['folder'];
$number_to_delete = $row['COUNT(folder)'];
$my_array[] = array($folderid,$number_to_delete);

$messages_to_delete[] = $row['id'];

} 

$result1 = mysql_query("DELETE FROM messages WHERE id IN (". implode(', ', $messages_to_delete).")")or die('Error1');

 

Everything OK so far. Now I need to update "folders" table.

 

In this example I want to delete 4 messages and $my_array[] is the following:

 

Array
(
[0] => Array
(
[0] => 893
[1] => 1
)
[1] => Array
(
[0] => 557
[1] => 3
)
) 

 

So, $my_array[0] contains folder ID and $my_array[1] contains number of deleted messages.

 

Folder 893 should be updated: total_messages= total_messages-1

and

folder 557 should be updated: total_messages=total_messages-3

 

Now my question? How can I delete with a single query?

 

I need something like this. It's not working, I just want to make my idea more clear:

 

$result9 = mysql_query("update messages_folders set total_messages=total_messages-$my_array[1]
where sender IN (". implode(', ', $myarray[0]).") and owner='" . $_SESSION['userid'] . "'") or die('Error');

 

I know how to do it with a loop, to run a separate query for each folder. I need to know if it's possible with a single query. Please show me an example if it's possible.

 

This is my messages_folders table:

 

CREATE TABLE IF NOT EXISTS `messages_folders` (
  `owner` int(11) NOT NULL,
  `folder` int(11) NOT NULL,
  `total_messages` mediumint(5) NOT NULL default '0',
  `updated` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  UNIQUE KEY `folder` (`folder`,`owner`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ;

 

In this situation I can't use unique folder_id because folders are being created and updated with "ON duplicate KEY UPDATE" statement and "folder" actually is the ID of the sender.

[craygo]

You will not be able to do it with a single query. Multiple rows can only be updated if you are applying the same value to a particular column. You will have to use a loop to update rows independently.

 

Ray

[tfe]

Thank you. OK, I will use a loop.