tfe Posted July 7, 2011 Share Posted July 7, 2011 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. Link to comment https://forums.phpfreaks.com/topic/241318-need-help-to-update-table-with-a-single-query-and-info-from-multidim-array/ Share on other sites More sharing options...
craygo Posted July 7, 2011 Share Posted July 7, 2011 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 Link to comment https://forums.phpfreaks.com/topic/241318-need-help-to-update-table-with-a-single-query-and-info-from-multidim-array/#findComment-1239586 Share on other sites More sharing options...
tfe Posted July 7, 2011 Author Share Posted July 7, 2011 Thank you. OK, I will use a loop. Link to comment https://forums.phpfreaks.com/topic/241318-need-help-to-update-table-with-a-single-query-and-info-from-multidim-array/#findComment-1239596 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.