CODEWITHSUNDEEP
phpmysql
xxx
xxx

Reputation: 197

Single php query to select and update

I am working on a messaging app that allows users to delete their messages, below is a query that is to do just that, I want to select a message based on its ID then if its the recipient deleting the messaging who in this case is the (to_id), the column user1delete will be updated with a '1', if it is the sender the column user2delete will be updated with a 1, below are three queries I want to integrate into one or better how can I select a message based on its ID and update the selected row?

            $query = "Select subject ,id, to_id, from_id, user_from, message, time_sent FROM pm
   WHERE id = $id"; 

$result = $db->query($query);

    $query = "UPDATE pm SET user1delete = '1'
    WHERE 
    CASE
    WHEN to_id = $to_id
    THEN from_id != $to_id
    END";

     $result = $db->query($query);

    $query = "UPDATE pm SET user2delete = '1'
    WHERE 
    CASE
    WHEN from_id = $to_id
    THEN to_id != $to_id
    END";
$result = $db->query($query);

Help will be greatly appreciated.

Upvotes: 2

Views: 78

Answers (1)

xxx
xxx

Reputation: 197

Thanks to @Pala, instead of selecting using the first query all I had to do include the id to the where clause below is my solution:

$query = "UPDATE pm SET user1delete = '1'
WHERE 
CASE
WHEN to_id = $to_id
THEN from_id != $to_id
AND id = $id
END";
$result = $db->query($query);


//$result = $db->query($query);

$query = "UPDATE pm SET user2delete = '1'
WHERE 
CASE
WHEN from_id = $to_id
  THEN to_id != $to_id
  AND id = $id
  END";
$result = $db->query($query);

Upvotes: 1

Related Questions