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

问题描述：

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  | hopewe@gmail.com
 2         | Hillary Mutanda         | 2000eea4c56cb341002a4  | hillary@gmail.com
 3         | Fortunate Mutanda       | ca2964e72000eea4c56cb  | fortu@gmail.com
 4         | Nyasha Mupanguri        | a3aca2964e72000eea4c5  | n.k.mupanguri@gmail.com

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();
?>

答

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

答

I don't know what you are trying to achieve, but I think that the case shouldn't be there.

It would be helpful if you explain what is the result you are expecting from the first query.

EDIT

Here is what you can do with your query.

select *
from conversation as c
, (
    select cr1.*
    from conversation_reply as cr1
    , (
        select c_id_fk as conversation_id
        , max(cr_id) as reply_id
        from conversation_reply
        group by c_id_fk
    ) as cr2
    where cr1.cr_id = cr2.reply_id
) as cr
where c.c_id = cr.c_id_fk
and (c.user_one = 1 or c.user_two = 1)

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

相关推荐