CODEWITHSUNDEEP
phpmysql
Freddy
Freddy

Reputation: 867

Trying to delete a whole conversation but struggling with what to parse in the WHERE clause

I have a table called private_messages, with the following structure:

id     message_from     message_to

I have a button which once pressed, will delete all the messages concerning the user logged in ($username) and the opposing person in the conversation ($user).

<a href='/inc/delete_conversation.php> Delete Conversation</a>

Consider that my private_messages table has the following rows:

id   message_from    message_to
--   ------------    ----------
1    Alice           conor
2    Alice           conor
3    conor           Alice
4    Anderson        conor
5    Cooper          Alice

If I am logged in as Conor, I will see 3 messages in the conversation with Alice (2 from her, 1 sent by conor). When I click Delete Conversation I want all three of these rows DELETED.

I have a PHP script called delete_conversation.php. In this script, I have tried to run the following query: 

$delete_query = mysqli_query($connect, "DELETE FROM private_messages WHERE 
                                        message_from='$username' 
                                        OR message_to='$username' 
                                        AND 
                                        message_from ='$user' 
                                        OR message_to='$user'
                            ");

With the above query, only the logged in users posts are deleted ($username). I tried to echo the value of $user, the echo returned nothing.

$user is the variable which stores what is at the end of the URL. For example, if my URL reads http://localhost/messages.php?u=Alice, then the value of $user is equal to Alice.

The Delete conversation link is found on messages.php, where the value is appended to the URL. But when the delete_conversation.php script is called, it doesn't know what $user is.

I thought about using $_GET. But I cannot append anything to my anchor link, for example, consider the below:

$get_name = mysqli_query($connect, "SELECT * FROM private_messages WHERE message_from='$username' OR message_to='$username' AND message_from ='$user' OR message_to='$user'");

$get_all = mysqli_fetch_assoc($get_name);
    $mess_from  = $get_all['message_from'];
    $mess_to    = $get_all['message_to'];
    $message_id = $get_all['id'];

    echo "<a href='/inc/delete_conversation.php?id=$mess_from'> Delete Conversation</a>

I cannot have an identifier of $mess_from or $mess_to because that is dependant on who has the latest row in db. For example, if im logged in as conor, I send a message to anderson, $mess_from would equal conor.

I am fine with deleting the logged in users posts in the conversation, but struggling to delete the opposite persons messages due to the script not being able to find $user.

Edit

State's walk through:

Upvotes: 3

Views: 64

Answers (3)

spencer7593
spencer7593

Reputation: 108450

Seems like there are a couple of issues.

As far as passing two values in a URI, it's possible to do that. For example:

<a href='/inc/delete_conversation.php?mess_from=ann&mess_to=dee'>mytext</a>

The values of mess_from and mess_to would both be available to your script.

The SELECT statement shown in the code...

 SELECT *
   FROM private_messages
  WHERE message_from = :user_one
     OR message_to   = :user_one
    AND message_from = :user_two
     OR message_to   = :user_two

returns an odd result. There are two usernames supplied, but the query is (potentially) returning rows that are related to users other than :user_one and :user_two.

There's an order of precedence between AND and OR. Adding parentheses around expressions specifies the order that the terms are to be evaluated.

As a simple demonstration, consider the the following query, and the results returned for the three boolean expressions.

(Note d.f represents message_from, d.t represents message_to)

SELECT m.f
     , m.t
     ,   m.f='ann' OR   m.t='ann'   AND   m.f='dee'   OR m.t='dee' AS `_or_and_or_`
     ,   m.f='ann' OR ( m.t='ann'   AND   m.f='dee' ) OR m.t='dee' AS `_or(_and_)or`
     , ( m.f='ann' OR   m.t='ann' ) AND ( m.f='dee'   OR m.t='dee' ) AS `(_or_)and(_or_)`
  FROM ( -- message_from and message_to
         SELECT 'ann' AS f, 'dee' AS t 
         UNION ALL SELECT 'dee', 'ann'
         UNION ALL SELECT 'ann', 'tye'
         UNION ALL SELECT 'tye', 'ann'
         UNION ALL SELECT 'dee', 'tye'
         UNION ALL SELECT 'tye', 'dee'
       ) m
   ORDER BY LEAST(m.f,m.t),GREATEST(m.f,m.t), m.f, m.t

The boolean expressions are matching to 'ann' and 'dee'. All of those are identical except for the addition of parens.

A return value of 1 means that the boolean expression evaluates to TRUE, a value of 0 means FALSE.

Compare the results from the boolean expression without parens (the third column) with the return from the expressions with parens (the fourth and fifth columns).

f     t     _or_and_or_  _or(_and_)or  (_or_)and(_or_)
----  ----  -----------  ------------  ---------------
ann   dee             1             1                1
dee   ann             1             1                1
ann   tye             1             1                0
tye   ann             0             0                0
dee   tye             0             0                0
tye   dee             1             1                0

There's nothing necessarily wrong with rows with 'tye' in the f or t column evaluating to TRUE. But I suspect that you are only looking to return rows that are related to both 'ann' and 'dee', and not any rows that are part of a conversation with 'tye'.

Upvotes: 2

Professor Abronsius
Professor Abronsius

Reputation: 33813

perhaps something like

$delete_query = mysqli_query($connect, "DELETE FROM `private_messages` WHERE 
        ( `message_from`='$username' AND `message_to`='$user' )
            OR
        ( `message_from`='$user' AND `message_to`='$username' )");

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Just use parentheses in your first query:

DELETE FROM private_messages
WHERE (message_from = '$username' OR message_to = '$username') AND
      (message_from ='$user' OR message_to = '$user');

Or switch to using IN:

DELETE FROM private_messages
WHERE '$username' IN (message_from, message_to) AND
      '$user' IN (message_from, message_to) ;

Big caution: I am following your original logic, but this will also delete messages to oneself -- if that is allowed.

Also, you should be using parameterized queries rather than plugging values directly in the query string.

Upvotes: 1

Related Questions