updating multiple rows in php/mysql

Question

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. Itlooks that array_push ignores identical values.

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');

PART 2

Here are my changes:

$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.

Bob Vale · Accepted Answer · 2011-07-05 09:40:48Z

1

The way you are currently doing it leads to a race condition if two people simultanously try and delete the same messages, the count could go off.

It is better to delete the messages, and then update the total to be equal to the count of the messages. I would need to see the structure of your tables in order to offer an example query

answered Jul 5, 2011 at 9:40

Bob Vale

18.5k1 gold badge44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

evan · Accepted Answer · 2011-07-05 09:42:26Z

0

Instead of array_push(), use $memberids []= $row['sender'];

This is actually recommended by php.net: http://php.net/manual/en/function.array-push.php as it is equivalent and doesn't have the function overhead. Although array_push() shouldn't care about previous values.

answered Jul 5, 2011 at 9:42

evan

12.6k7 gold badges41 silver badges52 bronze badges

Collectives™ on Stack Overflow

updating multiple rows in php/mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related