Private message bug unread count

DuckLv · April 10, 2020

Hello World ! i got this script of PM system on my website where 2 or 3 users can send PM to each other it work ok but it

<?php
header :  PM (unread count)
$msgs_count = GetUnreadMessagesCount($session_user["id"]);
and got this in the Index header :  PM <span>( <?=$msgs_count;?> )</span>

 /* Hello World ! i got a website where 2 or 3 users can create and send PM everything work good but have this bug . */
Example message thread#id1 Sender: A send the message to  B & C  ok now B & C got PM (1)
B make reply to this PM and C the same now the bug is userA will have PM(2) like he have 2unread messages 
but is different reply in the same message so need to count only (1) by Thread not reply ok if A read it and send new reply now B & C will have PM (3)
i hope someone can help me with this code thank you ! 

this is message_tbl for database


<?php
$tbl_messages_fields = array(
	"id"		=> "INTEGER PRIMARY KEY AUTO_INCREMENT", 
	"threadId"	=> "INT(11)", 
	"type"		=> "VARCHAR(4)", 
	"heldById"	=> "INT", 
	"fromId"	=> "INT", 
	"toId"		=> "VARCHAR(32)", 
	"isRead"	=> "INT(1) $d0", 
	"isStarred"	=> "INT(1) $d0", 
	"isDeleted"	=> "INT(1) $d0", 
	"subject"	=> "VARCHAR(150)", 
	"message"	=> "VARCHAR(10000)", 
	"timestamp"	=> "INT(12)");
SetupTable('tbl_messages', $tbl_messages_fields);

function UpdateMessageField($conditions, $field, $value){
	global $dbPrep;
	$add = array();
	foreach($conditions as $key=>$val){
		$add[] = "`$key`=:$key";
	}
	$add = implode(" AND ", $add);
	$sql = "UPDATE `tbl_messages` SET $field=:$field WHERE $add";
	$query = $dbPrep->prepare($sql);
	$data = array_merge($conditions, array("$field"=>$value));
	$query->execute($data);
}

function GetMessagesQuery($data = array(), $complexConditions = "", $extra = ""){
	$dbPrep = GetDatabaseConnection();
	$sql = "SELECT * FROM `tbl_messages` ";
	$add = " ";
	foreach($data as $key=>$value){
		$add.= "AND `$key`=:$key ";
	}
	$add = ($add != " ") ? "WHERE" . substr($add, 4, strlen($add)) : $add;
	$sql.= $add." ".$complexConditions." ".$extra;
	$query = $dbPrep->prepare($sql);
	$query->execute($data);
	return GetRows($query);
}

function GetThreadQuery($conditions = array(), $complexConditions = "", $extra = ""){
	global $dbPrep;
	
	$add = array();
	foreach($conditions as $key=>$val){
		$add[] = "`$key`=:$key";
	}
	$add = implode(" AND ", $add);
	
	$sql = "SELECT a.*, b.username AS fromUsername, b.type AS fromType FROM `tbl_messages` AS a ";
	$sql .= "INNER JOIN `tbl_users` AS b ON a.fromId=b.id ";
	$sql .= "WHERE $add ORDER BY timestamp ASC";
	#echo $sql;
	
	$query = $dbPrep->prepare($sql);
	$query->execute($conditions);
	return GetRows($query);
}

$p_NewMessage = $dbPrep->prepare("INSERT INTO `tbl_messages` (type, threadId, heldById, fromId, toId, subject, message, timestamp) VALUES (:type, :threadId, :heldById, :fromId, :toId, :subject, :message, :timestamp);");

$sql = "SELECT a.*, b.type as fromType FROM `tbl_messages` AS a ";
$sql .= "INNER JOIN `tbl_users` AS b ON a.fromId=b.id ";
$sql .= "WHERE threadId=:id AND heldById=:heldById AND isDeleted=0 ORDER BY timestamp ASC";
$p_GetSingleMessage = $dbPrep->prepare($sql);

function UnDeleteThread($threadId, $heldById){
	global $dbPrep;
         $p_UnDeleteThread = $dbPrep->prepare("UPDATE `tbl_messages` SET isDeleted=0,isRead=0 WHERE threadId=:threadId AND heldById=:heldById");
	$p_UnDeleteThread->execute(array("threadId"=>$threadId, "heldById"=>$heldById));
}

function GetRecipients($sessId, $fromId, $toId){
	$allParties = $sessId . "," . $fromId . "," . $toId;
	$arr = array_unique(explode(",", $allParties));
	if (($key = array_search($sessId, $arr)) !== false) {
		unset($arr[$key]);
	}
	return implode (",", $arr);
}

function GetRecipientName($id){
	global $dbPrep;
	$sql = "SELECT username FROM `tbl_users` WHERE id=:id";
	$query = $dbPrep->prepare($sql);
	$query->execute(array("id"=>$id));
	$row = $query->fetch( PDO::FETCH_ASSOC );
	return $row["username"];
}

function GetRecipientNames($recipients){
	$all = explode(",", $recipients);
	$val = array();
	foreach ($all as $a){
		$val[] = GetRecipientName($a);
	}
	return $val;
}

$p_GetUnreadMessagesCount = $dbPrep->prepare("SELECT COUNT(*) FROM `tbl_messages` WHERE isRead=0 AND heldById=:heldById AND type='recv'");
$unreadMsgsCount = -1;
function GetUnreadMessagesCount($uid){
	global $unreadMsgsCount;
	global $p_GetUnreadMessagesCount;
	if($unreadMsgsCount == -1){
		$p_GetUnreadMessagesCount->execute(array("heldById"=>$uid));
		$unreadMsgsCount = $p_GetUnreadMessagesCount->fetch( PDO::FETCH_ASSOC );
		$unreadMsgsCount = $unreadMsgsCount["COUNT(*)"];
	}
	return $unreadMsgsCount;
}

function GetNewThreadId(){
	global $dbPrep;
	$threadId = 1;
	$p_GetLatestThreadId = $dbPrep->prepare("SELECT MAX(threadId) FROM `tbl_messages`");
	$p_GetLatestThreadId->execute();
	$latestThreadId = $p_GetLatestThreadId->fetch( PDO::FETCH_ASSOC );
	if($latestThreadId){
		$threadId = $latestThreadId["MAX(threadId)"]+1;
	}
	return $threadId;
}			
?>




		if(isset($_REQUEST["mark_read"])){
			if(isset($_REQUEST["message_all_toggle"]))
				foreach($_REQUEST["message_all"] as $m)
					SetNotificationRead($m, $session_user["id"], 1);
			elseif(isset($_REQUEST["message"]))
				foreach($_REQUEST["message"] as $m)
					SetNotificationRead($m, $session_user["id"], 1);
			else
				foreach($_REQUEST["message_all"] as $m)
					SetNotificationRead($m, $session_user["id"], 1);
		}
		if(isset($_REQUEST["mark_unread"])){
			if(isset($_REQUEST["message_all_toggle"]))
				foreach($_REQUEST["message_all"] as $m)
					SetNotificationRead($m, $session_user["id"], 0);
			elseif(isset($_REQUEST["message"]))
				foreach($_REQUEST["message"] as $m)
					SetNotificationRead($m, $session_user["id"], 0);
			else
				foreach($_REQUEST["message_all"] as $m)
					SetNotificationRead($m, $session_user["id"], 0);
		}
		if(isset($_REQUEST["delete"])){
			if(isset($_REQUEST["message_all_toggle"]))
				foreach($_REQUEST["message_all"] as $m)
					RemoveNotificationById($m, $session_user["id"]);
			elseif(isset($_REQUEST["message"]))
				foreach($_REQUEST["message"] as $m)
					RemoveNotificationById($m, $session_user["id"]);
		}
	} else {
		if(isset($_REQUEST["delete"])){
			if(is_numeric($_REQUEST["delete"])){
				UpdateMessageField(array("threadId"=>$_REQUEST["delete"], "heldById"=>$session_user["id"]), "isStarred", 0);
				UpdateMessageField(array("threadId"=>$_REQUEST["delete"], "heldById"=>$session_user["id"]), "isDeleted", 1);
				UpdateMessageField(array("threadId"=>$_REQUEST["delete"], "heldById"=>$session_user["id"]), "isRead", 1);
			}elseif(isset($_REQUEST["message_all_toggle"])){
				$messages = $_REQUEST["message_all"];
				foreach($messages as $m){
					UpdateMessageField(array("threadId"=>$m, "heldById"=>$session_user["id"]), "isStarred", 0);
					UpdateMessageField(array("threadId"=>$m, "heldById"=>$session_user["id"]), "isDeleted", 1);
					UpdateMessageField(array("threadId"=>$m, "heldById"=>$session_user["id"]), "isRead", 1);
				}
			}elseif(isset($_REQUEST["message"])){
				$messages = $_REQUEST["message"];
				foreach($messages as $m){
					UpdateMessageField(array("threadId"=>$m, "heldById"=>$session_user["id"]), "isStarred", 0);
					UpdateMessageField(array("threadId"=>$m, "heldById"=>$session_user["id"]), "isDeleted", 1);
					UpdateMessageField(array("threadId"=>$m, "heldById"=>$session_user["id"]), "isRead", 1);
				}
			}
		}
		if(isset($_REQUEST["messagestar"])){
			if(isset($_REQUEST["message_all_toggle"])){
				$messages = $_REQUEST["message_all"];
				foreach($messages as $m){
					UpdateMessageField(array("threadId"=>$m, "heldById"=>$session_user["id"]), "isStarred", 1);
				}
			}elseif(isset($_REQUEST["message"])){
				$messages = $_REQUEST["message"];
				foreach($messages as $m){
					UpdateMessageField(array("threadId"=>$m, "heldById"=>$session_user["id"]), "isStarred", 1);
				}
			}
		}
		if(isset($_REQUEST["deletestarred"])){
			if(isset($_REQUEST["message_all_toggle"])){
				$messages = $_REQUEST["message_all"];
				foreach($messages as $m){
					UpdateMessageField(array("threadId"=>$m, "heldById"=>$session_user["id"]), "isStarred", 0);
				}
			}elseif(isset($_REQUEST["message"])){
				$messages = $_REQUEST["message"];
				foreach($messages as $m){
					UpdateMessageField(array("threadId"=>$m, "heldById"=>$session_user["id"]), "isStarred", 0);
				}
			}
		}
		if(isset($_REQUEST["mark_unread"])){
			if(isset($_REQUEST["message_all_toggle"])){
				$messages = $_REQUEST["message_all"];
				foreach($messages as $m){
					UpdateMessageField(array("threadId"=>$m, "heldById"=>$session_user["id"]), "isRead", 0);
				}
			}elseif(isset($_REQUEST["message"])){
				$messages = $_REQUEST["message"];
				foreach($messages as $m){
					UpdateMessageField(array("threadId"=>$m, "heldById"=>$session_user["id"]), "isRead", 0);
				}
			} 
		}
		if(isset($_REQUEST["mark_read"])){
			if(isset($_REQUEST["message_all_toggle"])){
				$messages = $_REQUEST["message_all"];
				foreach($messages as $m){
					UpdateMessageField(array("threadId"=>$m, "heldById"=>$session_user["id"]), "isRead", 1);
				}
			}elseif(isset($_REQUEST["message"])){
				$messages = $_REQUEST["message"];
				foreach($messages as $m){
					UpdateMessageField(array("threadId"=>$m, "heldById"=>$session_user["id"]), "isRead", 1);
				}
			}
		}
	}

	$details = isset($_REQUEST["details"])?$_REQUEST["details"]:0;
	$session_user = GetUserById($session_user["id"]);

gw1500se · April 10, 2020

but it ??????

requinix · April 10, 2020

Look in the database to see what rows there are. Look at who is the receiver, who is the sender, and whether a message is read or unread. You should be able to spot something wrong with the data.

1 hour ago, gw1500se said:

but it ??????

The question ended up in the code. Problem is something to do with unread counts.

DuckLv · April 10, 2020

ok thanks i llok in db now i sent new Message and look like this
Message from A to B 

id    threadId   type   heldById   fromId  toId  isRead  isStarred isDeleted  subject  mesage  timpestamp
1      1          sent     5         5      10     0        0          0        Test   Test     158642221
2      1          recv     10        5      10     0        0          0        Test    Test     158642221 

ok now i go to account: B and read the message and in the database to the user B (10) isRead=1
i sent reply back to the user  A

id   threadId   type     heldById   fromId  toId  isRead  isStarred  isDeleted   subject   message   timestamp
1      1          sent     5         5      10    0        0          0        Test      Test      158642221
2      1          recv     10        5      10    1        0          0        Test      Test      158642221 
3      1          sent     10        10     5     1        0          0        Test      bug       158642221
4      1          recv     5         10     5     0        0          0        Test      bug       158642221 

when i go to click and read messages in the profile of user A row id 1 and 4 are isRead=1 
now all rows isRead=1   i reply back to user  B now 

id   threadId   type     heldById   fromId  toId  isRead  isStarred  isDeleted   subject   message   timestamp
1      1          sent     5         5      10    1        0          0        Test      Test      158642221
2      1          recv     10        5      10    0        0          0        Test      Test      158642221 
3      1          sent     10        10     5     0        0          0        Test      bug       158642221
4      1          recv     5         10     5     1        0          0        Test      bug       158642221 
5      1          sent     5         5      10    1        0          0        Test      userBreply      158642221
6      1          recv     10        5      10    0        0          0        Test      userBreply      158642221 

the problem seems is everytime new message is sent the code update back to isRead=0 all Rows of the recv or sent

ok i did some test and the result is this

probably something bad in this script ?

$p_GetUnreadMessagesCount = $dbPrep->prepare("SELECT COUNT(*) FROM `tbl_messages` WHERE isRead=0 AND heldById=:heldById AND type='recv'");
$unreadMsgsCount = -1;
function GetUnreadMessagesCount($uid){
	global $unreadMsgsCount;
	global $p_GetUnreadMessagesCount;
	if($unreadMsgsCount == -1){
		$p_GetUnreadMessagesCount->execute(array("heldById"=>$uid));
		$unreadMsgsCount = $p_GetUnreadMessagesCount->fetch( PDO::FETCH_ASSOC );
		$unreadMsgsCount = $unreadMsgsCount["COUNT(*)"];
	}
	return $unreadMsgsCount;
}

requinix · April 10, 2020

Well, the first problem I see is that you're storing two rows for each message. That's a lot of duplicate data. You only need the one: isRead only applies to the receiver, and isStarred and isDeleted could be handled by using two columns.

DuckLv · April 12, 2020

ok thanks maybe the code are not very good but for now i use it . i am not a coder so i don't know how to fix it if someone can look and understand where is the problem of unread count and fix it iwill be very happy

requinix · April 12, 2020

At the moment the only thing I can guess is that you're updating all messages in a thread when you should be updating individual messages.

Sign In

Private message bug unread count

Recommended Posts

DuckLv

Link to comment

Share on other sites

gw1500se

Link to comment

Share on other sites

requinix

Link to comment

Share on other sites

DuckLv

Link to comment

Share on other sites

requinix

Link to comment

Share on other sites

DuckLv

Link to comment

Share on other sites

requinix

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information