CODEWITHSUNDEEP
phphtmlmysql
lezz
lezz

Reputation: 45

Mysql database for messages Query for pulling number of unread messages (with query)

I have a database for messages and I am currently able to pull the number of messages an individual user has sent and the user names with the DISTINCT COUNT(*) query, but I am unable to pull the messages that have a notread column.

My question is from the query below can anybody see a quick fix to display the number of unread messages. My database has a READ column 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>"; 
                            }
   }

I'm 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

Upvotes: 0

Views: 145

Answers (1)

ilazmi
ilazmi

Reputation: 11

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

Upvotes: 0

Related Questions