php - mysql DISTINCT GROUP QUERY 我如何列出组

Question

我有一个带有消息的 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>"; 
                            }
                    }


        }

标签： phpmysql