我已经创建了消息传递系统,在messages. PHP上我想显示消息对话明智和最后消息应该显示,但不知道查询,因为我是PHP新手,这里是数据库信息
table:conversation_chat table2:chat conversation_id: primary unique id: primary unique from: conversation_id: to: from_user: time: to_user: message: subject:
如果有人能帮助我在这个
<?php
$req3 = mysql_query("SELECT * FROM `conversation_chat` WHERE
`user_to`='$userid1'");
$hy= mysql_fetch_array($req3);
$convo = $hy['conversation_id'];
if(mysql_num_rows($req3)!=0){
$req4 = mysql_query("SELECT * FROM `chat` WHERE `to`='$userid1' AND
`conversation_id`='$convo'");
while($dn1 = mysql_fetch_array($req4))
{
?>
<td><img alt="example image" src="avatar.png"> <a
href="read?id=<?php echo $dn1['id']; ?>"><?php echo
htmlentities($dn1['from'], ENT_QUOTES, 'UTF-8'); ?></a></td>
<td><span class="label vd_bg-green append-icon">
<?php
echo $dn1['subject']; ?></span></td>
<td style="width:80px" class="text-right">
<strong><?php echo timeAgo($dn1['time']); ?></strong></td><td
style="width:80px" class="text-right">
<?php echo '<a
href="mdelete.php?id='. $dn1['id'] .'">Delete</a>'; ?></td>
</tr>
<?php
}}else{ echo "No new messages";}
?>
好了,您刚刚编辑了数据库信息。所以现在我明白你的数据库是怎样的了。
你必须在chat2中添加一个DATETIME类型的"time"列
SELECT *
FROM `chat`
WHERE `to`='$userid1'
AND
`conversation_id`='$convo'
ORDER BY time DESC //Change the order from last to older
如果你只想要最后一条消息,如果是MySQL数据库,你可以添加
LIMIT 1
如果我正确理解了您的问题,您希望显示且仅显示一条消息。也就是最新的?
如果这是真的,改变你的查询
SELECT * FROM `chat` ...
SELECT *, max(date) FROM `chat` ...
选择<?php
$req3 = "SELECT DISTINCT conversation_id,column_name FROM `conversation_chat` WHERE
`user_to`='$userid1' ORDER BY conversation_id desc";
$query=mysql_query($req3);
$num=mysql_num_rows($req3);
$hy= mysql_fetch_array($req3);
$convo = $hy['conversation_id'];
if($num!=0){
$req4 = mysql_query("SELECT DISTINCT id,column_name FROM `chat` WHERE `to`='$userid1' AND
`conversation_id`='$convo' ORDER BY id desc ");
while($dn1 = mysql_fetch_array($req4))
{
?>
<td><img alt="example image" src="avatar.png"> <a
href="read?id=<?php echo $dn1['id']; ?>"><?php echo
htmlentities($dn1['from'], ENT_QUOTES, 'UTF-8'); ?></a></td>
<td><span class="label vd_bg-green append-icon">
<?php
echo $dn1['subject']; ?></span></td>
<td style="width:80px" class="text-right">
<strong><?php echo timeAgo($dn1['time']); ?></strong></td><td
style="width:80px" class="text-right">
<?php echo '<a
href="mdelete.php?id='. $dn1['id'] .'">Delete</a>'; ?></td>
</tr>
<?php
}}else{ echo "No new messages";}
?>