Wsrtjtyk

i have a database for messages and i am currently able to pull the amount of messages an individual user has sent and the user names with the DISTINCT count(*) query, But i am un able to now pull the messages that have a notread column. my question is from the query below can anybody see a quik fix to display the number of unread messages . my database has a READ coloumn with either yes or no input at message creation..

The below query pulls the user names and the amount of messages from each user and displays the username and messages amount number by the side..

I wish to however replace the amount of messages with amount of messages unread.

here is the code

$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();       



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>"; 

                            }

   }

im thinking i need to change the messageaccountname,count(*) WHERE listID=$listID GROUP BY messageaccountname

but i am unsure as to how i should then display the number of results,

if anyone can see what i need to do please let me know, as i have been looking all day for the answer and have not found it yet.

Thank you all.

Lez

i accept your READ column type is varchar or enum with values YES and NO. so if you change your query like below you can get the unreaded messages count

SELECT DISTINCT messageaccountname,count(*), sum(if(read = "NO", 1, 0)) unread_count FROM `messagedatabase` WHERE `listID`='$listID' GROUP BY messageaccountname

hope will help you