具有线程/回复的私人消息系统

I'm currently working on creating a private messaging system, (PHP/MySQL) in which users can send message to multiple recipients at one time, and those users can then decide to reply.

我目前正在创建一个私人消息系统(PHP / MySQL),用户可以在其中一次向多个收件人发送消息,然后这些用户可以决定回复。

Here's what I'm currently working with:

以下是我目前正在使用的内容:

tbl_pm tbl:
id
date_sent
title
content
status ENUM ('unread', 'read') DEFAULT 'unread'

tblpm_info tbl:
id
message_id
sender_id
receiver_id

However, I need some help determining the logic on two things:

但是,我需要一些帮助来确定两件事的逻辑:

1) When a new message is created, should the "id" be auto-increment? If the 'id' column is set to auto-increment in both tables, how would I set the "message_id" column in the 'relation table'?

1)创建新消息时,“id”是否应自动递增?如果'id'列在两个表中都设置为自动递增,我如何在'关系表'中设置“message_id”列?

For example, when a new message is created, my MySQL statement is as follows:

例如,当创建新消息时,我的MySQL语句如下:

<?php
mysql_query("INSERT INTO `tblpm` (title, content, sender_id, date_sent) VALUES ('$subject', '$message', '$sender', NOW())" );

In the same statement, how would I enter the 'auto-incremented' value of tblpm into the tblpm_info "message_id" field?

在同一个语句中,如何将tblpm的“自动递增”值输入到tblpm_info“message_id”字段中?

2) What should my MySQL statement look like when users reply to messages?

2)当用户回复消息时,我的MySQL语句应该是什么样子?

Perhaps I am making this more complicated than I need to. Any help is greatly appreciated!

也许我让这比我需要的更复杂。任何帮助是极大的赞赏!

6 个解决方案

#1

1) Definetely yes, id's should be auto-autoincremented unless you provide a different means of a primary key which is unique. You get the id of the insert either with mysql_insert_id() or LAST_INSERT_ID() from mysql directly, so to post some connected info you can do either

1)定义是,id应该是自动自动增量的,除非你提供一种独特的主键的不同方法。您可以直接从mysql获取mysql_insert_id()或LAST_INSERT_ID()的插入ID,因此要发布一些连接的信息,您可以执行

   mysql_query("INSERT INTO table1 ...")
   $foreign_key=mysql_insert_id(); //this gives you the last auto-increment for YOUR connection

or, but only if you're absolutely sure no one else writes to the table in the mean time or have control over the transaction, after insert do:

或者,但是只有当你绝对确定没有其他人同时写入表格或控制交易时,才能执行以下操作:

$foreign_key=mysql_query("SELECT LAST_INSERT_ID()")
INSERT INTO table2 message_id=$foreign_key

or, without pulling the FK to php, all in one transaction (I also advice to wrap the SQL as a transaction too) with something like:

或者,没有将FK拉到php,所有在一个事务中(我也建议将SQL包装为事务),例如:

"INSERT INTO table1...; INSERT INTO table2 (message_id,...) VALUES(LAST_INSERT_ID(),...)"

Depending on your language and mysql libraries, you might not be able to issue the multi-query approach, so you're better off with using the first approach.

根据您的语言和mysql库,您可能无法发出多查询方法,因此您最好使用第一种方法。

2) This can have so many approaches, depending on if you need to reply to all the recepients too (e.g. conference), reply in a thread/forum-like manner, whether the client-side can store the last retrieved message/id (e.g. in a cookie; also affecting whether you really need the "read" field).

2)这可以有很多方法,具体取决于你是否需要回复所有接收者(例如会议),以线程/论坛的方式回复,客户端是否可以存储最后检索到的消息/ id(例如在一个cookie中;也影响你是否真的需要“阅读”字段)。

The "private chat" approach is the easiest one, you then are probably better off either storing the message in one table and the from-to relationships into an other (and use JOINs on them), or simply re-populate the message in one table (since storage is cheap nowadays). So, the simplistic model would be one table:

“私人聊天”方法是最简单的方法,然后你最好将消息存储在一个表中,将from-to关系存储到另一个表中(并在其上使用JOIN),或者简单地将消息重新填充到一个表中表(因为现在存储很便宜)。因此,简化模型将是一个表:

table: message_body,from,to
$recepients=array(1,2,3..);
foreach($recepients as $recepient)
 mysql_query("INSERT INTO table (...,message_body,from,to) VALUES(...,$from,$recepient)");

(duplicate the message etc, only the recepient changes)

(复制消息等,只有接收者更改)

message_table: id,when,message_body
to-from-table: id,msg_id,from,to

$recepients=array(1,2,3,...);
mysql_insert("INSERT INTO message_table (when,message_body) VALUES(NOW(),$body)");
$msg_id=mysql_insert_id();
foreach($recepients as $recepient)
 mysql_query("INSERT INTO to-from-table (msg_id,from,to) VALUES($msg_id,$from,$recepient)");

(message inserted once, store the relations and FK for all recepients)

(插入一次消息,存储所有接收者的关系和FK)

Each client then stores the last message_id he/she received (default to 0), and assume all previous messages already read):

然后,每个客户端存储他/她收到的最后一个message_id(默认为0),并假设所有先前的消息已经读取:

"SELECT * FROM message WHERE from=$user_id OR to=$user_id WHERE $msg_id>$last_msg_id"

or we just take note of the last input time from the user and query any new messages from then on:

或者我们只记下用户的最后输入时间并查询从那时起的任何新消息:

"SELECT * FROM message WHERE from=$user_id OR to=$user_id WHERE when>='".date('Y-m-d H:i:s',$last_input_time)."' "

If you need a more conference- or forum-tread-like approach, and need to keep track of who read the message or not, you may need to keep track of all the users involved.

如果您需要更多类似于会议或论坛的方法,并且需要跟踪谁阅读了该消息,您可能需要跟踪所涉及的所有用户。

Assuming there won't be hundred-something people in one "multi-user conference" I'd go with one table for messages and the "comma-separated and wrapped list" trick I use a lot for storing tags.

假设在一个“多用户会议”中不会有一百多个人,我会使用一个表来获取消息,并使用“逗号分隔和包装列表”技巧来存储标签。

id autoincrement (again, no need for a separate message id)
your usual: sent_at, title (if you need one), content
sender (int)
recepients (I'd go with varchar or shorter versions of TEXT; whereas TEXT or BLOB gives you unlimited number of users but may have impact on performance)
readers (same as above)

The secret for recepients/readers field is to populate them comma-separated id list and wrap it in commas again (I'll dulge into why later).

recepients / readers字段的秘诀是填充它们以逗号分隔的id列表并再次用逗号括起来(稍后我会深入了解原因)。

So you'd have to collect ids of recepients into an array again, e.g. $recepients=array(2,3,5) and modify your insert:

所以你必须再次将接收者的id收集到一个数组中,例如$ recepients = array(2,3,5)并修改你的插入内容:

"INSERT INTO table (sent_at,title,content,sender,recepients) VALUES(NOW(),'$title','$content',$sender_id,',".implode(',', $recepients).",')"

you get table rows like
... sender | recepients
... 1 | ,2, //single user message
... 1 | ,3,5, //multi user message

你得到的表行如... sender |收件人... 1 | ,2,//单个用户消息... 1 | ,3,5,//多用户消息

to select all messages for a user with the id of $user_id=2 you go with

为您选择ID为$ user_id = 2的用户选择所有消息

SELECT * FROM table WHERE sender=$user_id OR INSTR(recepients, ',$user_id,')

Previously we wrapped the imploded list of recepients, e.g. '5,2,3' becomes ',5,2,3,' and INSTR here tells if ',2,' is contained somewhere as a substring - since seeking for just '2',',2' or '2,' could give you false positives on e.g. '234,56','1**,234','9,452,**89' accordingly - that's why we had to wrap the list in the first place.

之前我们包含了内容的内容列表,例如'5,2,3'成为',5,2,3',INSTR在这里告诉',2,'是否作为子串包含在某处 - 因为只搜索'2',',2'或'2, '可能会给你误报,例如'234,56','1 **,234','9,452,** 89'因此 - 这就是为什么我们必须首先包装清单。

When the user reads/receives his/her message, you append their id to the readers list like:

当用户阅读/接收他/她的消息时,您将他们的ID附加到读者列表,如:

UPDATE table SET readers=CONCAT(',',TRIM(TRAILING ',' FROM readers),',$user_id,') WHERE id=${initial message_id here}

which results in:

这导致:

... sender | recepients | readers
... 1 | ,2, | ,2,
... 1 | ,3,5, | ,3,5,2,

...发件人|收件人|读者... 1 | ,2,| ,2,... 1 | ,3,5,| ,3,5,2,

Or we now can modify the initial query adding a column "is_read" to state whether the user previously read the message or not:

或者我们现在可以修改初始查询,添加一列“is_read”来说明用户以前是否读过该消息:

SELECT * FROM table WHERE INSTR(recepients, ',$user_id,'),INSTR(readers, ',$user_id,') AS is_read

collect the message-ids from the result and update the "recepients" fields with one go

从结果中收集消息ID并一次更新“recepients”字段

"UPDATE table SET readers=CONCAT(',',TRIM(TRAILING ',' FROM readers),',$user_id,') WHERE id IN (".implode(',' ,$received_msg_ids).")"

6 个解决方案

#1

更多相关文章

随机推荐