使用php和mysql数据库返回对话列表和每条消息的最后一条消息

Question

I have the following database design for a conversation database and its php code to return a conversation list and the last message of each and every conversation.

users table

 user_id   | username                | password               | email
 ---------------------------------------------------------------------------
 1         | Hopewell Mutanda        | a3aca2964e72000eea4c5  | [e-mail]
 2         | Hillary Mutanda         | 2000eea4c56cb341002a4  | [e-mail]
 3         | Fortunate Mutanda       | ca2964e72000eea4c56cb  | [e-mail]
 4         | Nyasha Mupanguri        | a3aca2964e72000eea4c5  | [e-mail]

Conversation table

 c_id     | user_one   | user_two   | ip                 | time
 ----------------------------------------------------------------------------------
 1        | 1          | 2          | 3478348924893      | 82738478234
 2        | 1          | 3          | 2487348439340      | 74347738439
 3        | 1          | 4          | 3473487438494      | 74387438479
 4        | 2          | 1          | 3434830430543      | 38483489934
 5        | 2          | 3          | 3985398594589      | 43875438758
 6        | 3          | 4          | 3878438439954      | 87457485748

conversation_reply table

cr_id     | reply                   | user_id_fk      | ip           | time           | c_id_fk
-----------------------------------------------------------------------------------------------------
1         | Hello how are you       | 1               | 274782347843 | 877428742387   | 1
2         | Im fine and you?        | 2               | 873784387438 | 287483473847   | 3
3         | How has been your day   | 3               | 727728743387 | 342898328938   | 6

********fields with a _fk means they have a foreign key constraint********************

This is my php code to retrieve the conversation list and the last message of each conversation. Unfortunately it is returning an empty array and i dont know where i am going wrong. The second sql query should be working fine, it is the first one that i am mainly concerned about. Thats what at least i found out when i tried debugging it

<?php
include 'database.php';
$user_one = "2";
$pdo = Database::connect();
$sql = "SELECT u.user_id,c.c_id,u.username,u.email
         FROM conversation c, users u
         WHERE CASE 
         WHEN c.user_one = '$user_one'
         THEN c.user_two = u.user_id
         WHEN u.user_two = '$user_one'
         THEN c.user_one= u.user_id
         END 
         AND (
         c.user_one ='$user_one'
         OR c.user_two ='$user_one'
         )
         Order by c.c_id DESC Limit 20";

$q = $pdo->prepare($sql);
$q->execute(array($sql));
$array = array();
$array["details"] = array();
 while ($row = $q->fetch(PDO::FETCH_ASSOC)){
    $c_id=$row['c_id'];
    $user_id=$row['user_id'];
    $username=$row['username'];
    $email=$row['email'];
    $cquery = "SELECT R.cr_id,R.time,R.reply FROM conversation_reply R WHERE R.c_id_fk='$c_id' ORDER BY R.cr_id DESC LIMIT 1";
    $result2 = $pdo->prepare($cquery);
    $result2->execute(array($cquery));
    while ($crow = $result2->fetch(PDO::FETCH_ASSOC)){
         $cr_id=$crow['cr_id'];
         $reply=$crow['reply'];
         $time=$crow['time'];
         $details["cr_id"] = $crow["cr_id"];
         $details["reply"] = $crow["reply"];
         $details["time"] = $crow["time"];
         $details["c_id"] = $row["c_id"];
         $details["user_id"] = $row["user_id"];
         $details["username"] = $row["username"];
         $details["email"] = $row["email"];
         array_push($array["details"], $details);
        }

   }
$json = json_encode($array);
echo $json;
Database::disconnect();
?>

Answer 1

Is it possible that what should be there is a Case Expression and not a Case Statement ?

Replace the first where condition, i.e.,the following :

CASE WHEN c.user_one = '$user_one' THEN c.user_two = u.user_id WHEN u.user_two = '$user_one' THEN c.user_one= u.user_id END

with this :

(Case When c.user_one = '$user_one' And c.user_one = u.user_id Then 1
Else Case When  c.user_two = '$user_one' And c.user_two = u.user_id Then 1 
Else 0 End End ) = 1