array_push - problems to update database

lpa

Below is code which I want to use to delete messages. I need not only to delete messages but also update total number of messages in "pmessages_folders" table.

Let's say, I want to delete messages with ids 3,4,5. Two of these messages are in the same folder:

message 3 -> folder 1
message 4 -> folder 2
message 5 - > folder 2

So, from folder 2 will be deleted 2 messages and I need to set total_messages for this folder -2 but query (result2) updates it only once.

Is there any way how to solve this problem?

Thanks.

$result = mysql_query("SELECT sender FROM messages 
WHERE id IN(". implode(', ', $_POST['msg_id']).")") or die ('Error1');

$memberids = array();
while ($row = mysql_fetch_assoc($result)) {
array_push($memberids, $row['sender']);
}

$result2 = mysql_query("update messages_folders set total_messages=total_messages-1 
where sender IN (".implode(",", $memberids).") and recipient='$id'") or die('Error2');

$result3 = mysql_query("DELETE FROM messages 
WHERE id IN (". implode(', ', $_POST['msg_id']).")")or die('Error3');

Ashley_Sheridan

You could use the COUNT() function in SQL to dynamically pull out the number of messages, and it will mean you don't have to keep updating a total_messages value (which is a race condition issue waiting to happen really) and a count is fairly quick.

However, if you don't/can't/won't then what's wrong with the method you've got already?

lpa

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

The result:

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

No idea how to do this with IMPLODE. I need something like this:

$result9 = mysql_query("update messages_folders set total_messages=total_messages-$my_array[1] 
where sender $my_array[0]  and recipient='$id'") or die('Error');

I am not running this query. It's just to show what I want to achieve.

anoopmail

I would also suggest to calculate the total count of messages on the fly instead of incrementing and decrementing. There are chances that you may get loose the sync.
Anyways, to make you code work,
Put in a loop

foreach($my_array as $message){
$sender = $message[0];
$delete_count = $message[1];
$sql = "UPDATE messages_folders set total_messages=total_messages-{$delete_count}
where sender = {$sender} " ;
mysql_query($sql) or die('Error on update');
}

I'm not sure u need "and recipient='$id'".

anoopmail

if u r planning to refactor you code, consider doing the following,

Add a folder_id column into messages table.
Let the message_folder table have columns folder_id, member_id, folder_name

lpa

anoopmail, thank you for your messages.

I am trying to get a solution to update messages_folders table with just 1 mysql query. I know how to do it with a loop but... I have 30 messages on 1 page. If user wants to delete all 30 messages and each message has a different sender (folder) , this process will need 30 mysql queries 🙁 That doesn't look nice.

Both tables already have your suggested columns (your message #5).

Derokorian

DELETE FROM ... WHERE message_id IN ();

http://data.agaric.com/mysql-where-1-4-5-syntax

lpa

Derokorian, I know how to delete rows with IN(). My queston is not about it.

The problem for me is to update the number of deleted rows. For example, user deletes 30 messages and each message belongs to some folder. In this query can be more than 1 message with the same folder. I need to update folders table to set the correct number of messages. I need some solution how to do this with just one query, without loop.

Please see my message posted at 09:15 AM.

Derokorian

You could use the loop to calculate how much to decrement each folder, then run a query AFTER the loop. Tho I agree with anoop in that you should just use count() to determine on the fly rather than storing these values. What happens if deleting the message works and the update doesn't? Now the values will be out of sync (IE only 3 unread message, while the table shows 5 unread).

lpa

Derokorian, can you please show an example how to calculate the decrement of each folder by using a loop so that after that I can update folders with just 1 query?

Anoopmail's example will need a separate mysql_query to upate each folder. If I run a separate query to update each folder this can be done much easier:

$result = mysql_query("SELECT sender FROM messages
WHERE id IN(". implode(', ', $_POST['msg_id']).") and recipient ='$id'") or die ('Error1');

while(list($folder) = mysql_fetch_row($result)) {
$result2 = mysql_query("update messages_folders set total_messages=total_messages-1
where sender='$folder' and recipient='$id'") or die('Error2'); 
}

$result3 = mysql_query("DELETE FROM messages 
WHERE id IN (". implode(', ', $_POST['messages']).") and recipient='$id'")or die('Error');

But I need the following result:

1 query to read the folders IDs ($result1)
1 query to update folders ($result2)
1 query to delete messages ($result3)

not 30 queries inside a loop to update 30 folders.

I also don't understand why some people say that my first example is a race condition. I add condition "and recipient='$id'" to each query, so, it's not possible that several people may run the same query at the same time or something like that.

Thanks to all.

anoopmail

Whenever you want to show the message count for any folder, you should calculate it on the fly from messages table. Currently, you have 2 sources of information. What if your messages table has 20 rows for a particular folder but message folder table says 19?
Also, the messages table need unique message_id so that you could identoify and delete each message without the recipient_id.

lpa

anoopmail, but in my post #3 I already included an example where query counts messages on the fly as suggested Ashley Sheridan. Why do you think this query will count wrong? This query checks all messages submitted by $_POST['msg_id'] and if theu exist calculates the number of messages for each folder that will be deleted.

I just don't know how to update messages_folders table by using information from multidimensial array and without running a separate query for each folder.

Messages table has unique message_id and I will delete messages without and recipient='$id'. I just added it to my examples in this forum to show that everything else like will be checked.

anoopmail

Your code is correct until you update the count to message folder table. Instead of this, compute the count whenever you want to show them (probably just right to the folder name as Gmail does).

Feel free to ask for more if your brain is still flying around..

lpa

I just got a message in another forum that multiple rows with a single query can not be updated if values for each row are different.

So, I have to use a loop for updating 🙁

Ashley_Sheridan

What exactly are you trying to update in a loop? Several of us have said you should calculate the count at the time you need it. Don't store it anywhere. Instead of running a query to retrieve the count field from the DB, just run the query that produces the count. No loop, no updating hassle.

lpa

Ashley Sheridan, probably we are talking about different things. Maybe my English is not good enough. Anyway, as I wrote, in another forum I got answer that

Multiple rows can only be updated if you are applying the same value to a particular column

It's not my case because I have different values. So, I can't update multiple rows with just 1 query.

Below are my final version and it's working. Is it OK or it can be done better? If you don't like this, can you please make changes in the script so that I can see what you really mean.

Thanks.

$id = $_SESSION['userid'];

$result1 = mysql_query("SELECT id, recipient, sender FROM messages 
WHERE id IN(". implode(', ', $_POST['msg_id']).")") or die ('Error1');
while ($row = mysql_fetch_assoc($result1)) {

if ($row['recipient'] != $id) {
echo "You are not allowed to delete this message";
die;
}

$result2 = mysql_query("update messages_folders set total_number=total_number-1 
where sender='".$row['sender']."' and recipient='$id'") or die('Error2');

$deleteids[]= $row['id'];
}

$result3 = mysql_query("DELETE FROM messages 
WHERE id IN (". implode(', ', $deleteids).")")or die('Error3');

Ashley_Sheridan

Why are you trying to update your DB with a count anyway? The way you're doing it now - updating a field one query at a time as you loop through the results of another query - is crazy, and will break at some point!

In this code, you're getting a list of all the messages, looping through and updating a count by one for each message, then deleting some messages. This leaves the count wrong. What are you using the count for anyway? Why can't you just use a standard COUNT() function in MySQL?

bradgrafelman

lpa;10983914 wrote:
Ashley Sheridan, probably we are talking about different things.

Not really, no.

Ashley Sheridan (et al.) have suggested that you eliminate the 'total_number' column altogether (and thus the UPDATE query becomes pointless) since you can instead perform a COUNT() at the time you retrieve the list of folder names to calculate the number of messages "on-the-fly" rather than using a stored value (which must be kept in sync with the actual count).

Why is it that you're ignoring that suggestion?

Multiple rows can only be updated if you are applying the same value to a particular column

This is entirely false, however it's also irrelevant.

lpa;10983914 wrote:
So, I can't update multiple rows with just 1 query.

This is again false, however using COUNT() when retrieving the folders would negate the need to even address this at all.

lpa;10983914 wrote:
Is it OK or it can be done better?

IMHO, it's not OK and it could be done much, much better.

For one, there's the obvious suggestion that's been beaten to death already - using COUNT() on the fly.

For another, if you wanted to keep the stored value for whatever reason (e.g. you're using InnoDB tables and/or COUNT() isn't performing as well as you'd like), I would recommend using triggers to automatically update this value whenever a message is created or deleted.

In a nutshell, you'd most likely have two triggers - one for 'ON INSERT' and one for 'ON DELETE.' Their respective functions should be obvious; the former increments the message count while the latter decrements the count.

lpa

bradgrafelman, thank you for your message. I don't use folders on my website yet and it was not a problem to delete messages so far. I am just trying to implement folders.

I did not understand until now the Ashley Sheridan's idea about using COUNT(); Now I understand but anyway... How much server resources may take such a calculating on the fly?

I have almost 2 000 000 messages in the database. Some people have ~50 folders. Can you imagene 1000 people online and accessing their mailboxes, and MySQL has to calculate the total number of messages for all users in all folders on the fly?

Maybe I am wrong but it looks that it may take a lot of server resources. I don't think that, for example, this forum calculates the number of threads and posts on the fly.

Maybe triggers is a solution but I need information how to implement them.

bradgrafelman

lpa;10983918 wrote:
I have almost 2 000 000 messages in the database. Some people have ~50 folders. Can you imagene 1000 people online and accessing their mailboxes, and MySQL has to calculate the total number of messages for all users in all folders on the fly?

Depending upon the hardware... yes, I could imagine that scenario playing out just fine.

lpa;10983918 wrote:
I don't think that, for example, this forum calculates the number of threads and posts on the fly.

On the contrary, I would guess that it does determine this dynamically rather than storing a fixed number. I haven't looked at vBulletin in quite a while, so I could be way off base.

lpa;10983918 wrote:
Maybe triggers is a solution but I need information how to implement them.

Once you decide which path you're going down (on-the-fly, or a static value stored in the D😎, we can probably help in figuring out what the best way to go about doing what you're describing.

If, for example, you wanted to stick with the static value stored in the DB, my opinion is that triggers would indeed be the best way to implement it. It would be more efficient and more reliable than adding a loop in the appropriate place(s) in your PHP application(s).

As far as triggers themselves go, the MySQL manual section entitled 17.3. Using Triggers will help describe what triggers are and the relevant syntax used with them. Try writing one or both of the triggers I mentioned above and post what you come up with as well as a copy of your table structure(s) (e.g. do a 'SHOW CREATE TABLE tblName' query and just paste the raw output - we can decipher it from there... hopefully 😉) if you get stuck.