如何设置 phpmyadmin 和 SQL 以通过 REST-api 进行消息传递? [英] How to setup phpmyadmin and SQL for messaging via REST-api?

问题描述

在我的数据库中，我有一个名为Chat"的表.在这里，我打算在我的应用程序中存储用户之间发送的每条消息.首先，我必须将它们存储在一个列表中(只需简单地执行一个 SELECT * FROM ColumnText WHERE OneID = '$OneID' 并从我有消息的其他用户那里收集所有 ID)但我现在努力完成最后一部分，即在我单击一行后查看这两个用户之间的消息.

我的计划是通过一个看起来像这样的电话来收集信息:

http://myURL.com/Chat.php?UserOneID=1548&UserTwoID=1724

其中一个用户的 ID 1548 和另一个 1724.

我看到的第一个问题是，我会知道谁发送了消息.然后我所做的并且可能有效的是做这样的事情:

将 ID 重命名为更清晰的名称，例如第一个 SenderID 和 RecieverID 而数据可能看起来像这样:

SenderID = 1548 RecieverID = 1724

和

SenderID = 1724 RecieverID = 1548

我为它编写了如下代码，其中我使用 UNION 尝试将两个 ID 的数据收集在一起，而不仅仅是一个 ID，调用看起来像这样:

http://myURL.com/Chat.php?SenderID=1548&RecieverID=1724http://myURL.com/Chat.php?RecieverID=1548&SenderID=1724<?php类连接信息{公共 $conn;公共函数 GetConnection() {$this->conn = mysqli_connect("serv", "user","pass", "db") or die(mysqli_error($mysql_pekare));}}$connectionInfo = new ConnectionInfo();$connectionInfo->GetConnection();if (!$connectionInfo->conn){echo '无连接';}别的{$SenderID = $_GET['SenderID'];$RecieverID = $_GET['RecieverID'];$query = "(SELECT * FROM ColumnText WHERE SenderID = '$SenderID') UNION (SELECT * FROM ColumnText WHERE RecieverID = '$RecieverID')";$stmt = mysqli_query($connectionInfo->conn, $query);如果 (!$stmt){echo '查询失败';}别的{$contacts = array();而 ($row = mysqli_fetch_array($stmt)){$contact = array("Messages" => $row['Messages']);array_push($contacts, $contact);}echo json_encode(array('results' => $contacts), JSON_PRETTY_PRINT);}}?>

这里的问题是UNION"似乎不起作用.我似乎只从 SenderID 行获取所有数据，即使 RecieverID 与我发送给脚本的数据不匹配.

我目前的方法是否正确?如果是，我需要如何调整我的 phpscript 才能使其正常工作?

解决方案

为什么不直接使用 OR?那么结果有正确的接收者和正确的发送者吗?你得到了从 X 到 Y 的所有消息以及从 Y 到 X 的所有消息

您的查询行将如下所示:

 $query = "SELECT * FROM ColumnText WHERE (SenderID = '$SenderID' AND RecieverID = '$RecieverID') OR (SenderID = '$RecieverID' AND RecieverID = '$SenderID');

此外，我强烈建议您使用准备好的语句

In my database I have a table called "Chat". Here i intend to store every message that has been sent between users in my application. First i have to store them in a list (by just simply doing a SELECT * FROM ColumnText WHERE OneID = '$OneID' and gather all the ID's from other users i have had messages with) but i now struggle to finish the last part which is to see the messages between these two users once i click on a row.

My plan is to gather the info with a call that would look something like this:

http://myURL.com/Chat.php?UserOneID=1548&UserTwoID=1724

Where one user has ID 1548 and another 1724.

The first issue I am seeing is that who will I know which one sent the message. What i then did and that could potentially work is to do something like this instead:

Rename the ID's to something more clear like this first SenderID and RecieverID instead where the data could look something like this:

SenderID = 1548 RecieverID = 1724

and

SenderID = 1724 RecieverID = 1548

I wrote code for it that looks like this where i use a UNION to try to gather the data for the two ID's together, not just one ID where the call would look something like this:

http://myURL.com/Chat.php?SenderID=1548&RecieverID=1724
http://myURL.com/Chat.php?RecieverID=1548&SenderID=1724


<?php

class ConnectionInfo 
{   
public $conn; 
public function GetConnection() {
  $this->conn = mysqli_connect("serv", "user","pass", "db") or die(mysqli_error($mysql_pekare));

 }
}

$connectionInfo = new ConnectionInfo();
$connectionInfo->GetConnection();

if (!$connectionInfo->conn)
{
echo 'No Connection';
}

else
{
    $SenderID = $_GET['SenderID'];
    $RecieverID = $_GET['RecieverID'];

    $query = "(SELECT * FROM ColumnText WHERE SenderID = '$SenderID') UNION (SELECT * FROM ColumnText WHERE RecieverID = '$RecieverID')";

    $stmt = mysqli_query($connectionInfo->conn, $query);

    if (!$stmt)
    {
        echo 'Query failed';
    }

    else
    {
        $contacts = array(); 
        while ($row = mysqli_fetch_array($stmt)) 
        {

        $contact = array("Messages" => $row['Messages']);

        array_push($contacts, $contact);         
        }
        echo json_encode(array('results' => $contacts), JSON_PRETTY_PRINT);
    }

}

?>

The problem with this is that "UNION" does not seem to do the trick. I only seem to get all the data from the SenderID row even though the RecieverID is not matching with the one i send to the script.

Is my current approach the correct way and if so, how would i need to adjust my phpscript in order for this to work?

解决方案

Why don‘t you just use an OR? So the results have the correct receiver and the correct sender? And you got all messages from X to Y and all messages from Y to X

Your query line would look like this:

 $query = "SELECT * FROM ColumnText WHERE (SenderID = '$SenderID' AND RecieverID = '$RecieverID') OR (SenderID = '$RecieverID' AND RecieverID = '$SenderID')";

Furthermore i strongly recommend you to use prepared statements

这篇关于如何设置 phpmyadmin 和 SQL 以通过 REST-api 进行消息传递?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！