Freddy Freddy - 7 months ago 8
PHP Question

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:


  • Logged in as freddy.

  • Looking through conversation with Alice (at which point, the url reads:
    http://localhost/messages.php?u=AliceP
    .
    -
    delete_conversation.php
    currently has the following code in it (nothing else, besides the variable defining code):
    echo $username." ". $user;

  • The following results are echo'd -
    freddy
    - clearly
    $user
    . has not been found.


Answer

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