Jump to content

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


tfe

Recommended Posts

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.

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.