mysql distinct group query如何列出组

w6lpcovy  于 2021-06-18  发布在  Mysql
关注(0)|答案(0)|浏览(193)

我有一个mysql数据库,里面有消息,我试图在一个网页上显示用户和用户发送的消息数,但是我只能从查询中得到用户数,但是经过仔细的调查,我发现我的查询是正确的,但是输出的方式是不正确的,但是我不知道如何同时显示用户名和信息量。。
有谁能帮我理解如何像这样正确地输出结果吗。。

messages

用户1(3)用户2(6)
这是我目前的查询和输出。
我真的很感谢你们的帮助,因为这已经折磨了我一整天。
谢谢。

<div id='containercontact' class='containercontact'>

<?php

    include_once('contactselect.php');
?>

</div>

-----contactselect.php----(variables set by another page with an ajax send)

if (isset($_GET['listID'])){$listID=$_GET['listID'];}

if (isset($_GET['itemaccountname'])){$itemaccountname=$_GET['itemaccountname'];}

if (isset($_GET['useraccountname'])){$useraccountname=$_GET['useraccountname'];}

if (isset($_GET['selecteduser'])){$selecteduser=$_GET['selecteduser'];} else {$selecteduser="nil";}

                if ($useraccountname===$itemaccountname) {

                echo"

<div class='userbox'>
<header class='ubheader'>Messages From</header>
<section class='ubmain'>
";

    $db = new PDO("mysql:host=localhost;dbname=messages", 'root', ''); // 1. set database with this instead of conect - or change conect to this

    $query="SELECT DISTINCT messageaccountname,count(*) FROM `messagedatabase` WHERE `listID`='$listID' GROUP BY messageaccountname";

    $stat=$db->prepare($query);

    $stat->execute();
        $Ccount = $stat->rowCount();
        $contactcount=$Ccount;

        if ($contactcount <1) {
        echo"<div id='namecon' class='ubnames' >NO MESSAGES YET</div>"; 
        }
        else {

                    while($row = $stat->fetch()){
                         $messageaccountname=$row['messageaccountname'];

                            if ($messageaccountname != $useraccountname){
                                        echo"<div id='namecon' class='ubnames' onclick='selectmessage(\"{$messageaccountname}\")'>{$messageaccountname}</div>"; 
                            }
                    }

        }
echo "
</section>

</div>
";              

            } 

?>

<script>

function selectmessage(selectauser){

    var selected=selectauser;
    var varLISTID = document.getElementById('datacatch').getAttribute("data-variable-LISTID");
    var varUSERACCOUNTNAME = document.getElementById('datacatch').getAttribute("data-variable-USERACCOUNTNAME");
    var varITEMACCOUNTNAME = document.getElementById('datacatch').getAttribute("data-variable-ITEMCCOUNTNAME");

    var mylink = "loadmessages.php?listID=" + varLISTID + "&useraccountname=" + varUSERACCOUNTNAME + "&itemaccountname=" + varITEMACCOUNTNAME + "&selecteduser="+selected;
    $('#infobox1').load(mylink);

}

</script>

我想我做到了

$db = new PDO("mysql:host=localhost;dbname=messages", 'root', ''); // 1. set database with this instead of conect - or change conect to this

    $query="SELECT DISTINCT messageaccountname,count(*) FROM `messagedatabase` WHERE `listID`='$listID' GROUP BY messageaccountname";

    $stat=$db->prepare($query);

    $stat->execute();
        $Ccount = $stat->rowCount();
        $contactcount=$Ccount;

        if ($contactcount <1) {
        echo"<div id='namecon' class='ubnames' >NO MESSAGES YET</div>"; 
        }
        else {

                    while($row = $stat->fetch()){

                        $messagenumber = $row['count(*)'];
                         $messageaccountname=$row['messageaccountname'];

                            if ($messageaccountname != $useraccountname){
                                        echo"<div id='namecon' class='ubnames' onclick='selectmessage(\"{$messageaccountname}\")'>{$messageaccountname}{$messagenumber}</div>"; 
                            }
                    }

        }

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题