Display if user has message

[dean7]

Hey guys, I've not done any coding in a long time although I'm starting to get back into it, using PDO rather than MySQL aswell.

 

On the code below I'm simply trying to pull information from the database to tell the user if he/she has a message, but the problem I've got is its saying I haven't got a message even when I have? 

 

Am I missing something completely simple here or have I got to approach this a complete different way than I have done? 

 

<?php$Unread = 0;$UsersInbox = $db->prepare("SELECT * FROM `inbox`  WHERE `to` = ':username' AND `read` = ':unread'");$UsersInbox->bindParam(":username", $Username);$UsersInbox->bindParam(":unread", $Unread);$UsersInbox->execute();$UsersInbox1 = $UsersInbox->fetchObject();$NumberOfMessage = $UsersInbox->rowCount();   ?><div class="FooterText"><a href="Inbox.php">Inbox</a>(<?php if ($NumberOfMessage != "0"){ echo "New"; }else{ echo "0"; } ?>)

 

Thanks for any help!

 

Dean

[ginerjm]

1 - turn on error checking so you can see messages

2 - remove the quotes from the parms in the query statement.

[Psycho]

First, a few pointers:

 

1. Don't use "SELECT *". In this case you only want to know if the user has any unread messages. So, you shouldn't be selecting records and instead just query the count. But, even when you do need data, using * is a bad practice for several reasons which I won't go into right now.

 

2. As stated above, you just need to do a "SELECT COUNT(*)". That will be much more efficient.

 

3. Add comments! Even when working on small pieces of code it will save you time in the long run.

 

In regards to your problem. the rowCount() method is supposed to be used on the database result (e.g. "$UsersInbox"). The code above is trying to use it on the the first row from the result set. Additionally, that will be a Boolean FALSE if there were no rows returned. I'm also curious why you are using the hard-coded unread variable since the intent of the query is to get the unread messages. I can't think of a reason why you would want to get only the read messages.

 

Try this (untested)

<?php
//Create an run the query to get unread message count
$queryStr = "SELECT COUNT(*) as unreadCount FROM `inbox`  WHERE `to` = :username AND `read` = 0";
$inboxQry = $db->prepare($queryStr);
$inboxQry->bindParam(":username", $Username);
$inboxQry->execute();
 
//Get the results
$inbox = $inboxQry->fetchObject();
$inbox->unreadCount;

//Prepare the output based on results
$inboxCountHtml = ($inbox->unreadCount) ? "{$userInbox->unreadCount} New" : "0";
 
?>
<div class="FooterText"><a href="Inbox.php">Inbox</a> (<?php echo $inboxCountHtml; ?>)

Edited July 20, 2016 by Psycho

[dean7]

 

First, a few pointers:

 

1. Don't use "SELECT *". In this case you only want to know if the user has any unread messages. So, you shouldn't be selecting records and instead just query the count. But, even when you do need data, using * is a bad practice for several reasons which I won't go into right now.

 

2. As stated above, you just need to do a "SELECT COUNT(*)". That will be much more efficient.

 

3. Add comments! Even when working on small pieces of code it will save you time in the long run.

 

In regards to your problem. the rowCount() method is supposed to be used on the database result (e.g. "$UsersInbox"). The code above is trying to use it on the the first row from the result set. Additionally, that will be a Boolean FALSE if there were no rows returned. I'm also curious why you are using the hard-coded unread variable since the intent of the query is to get the unread messages. I can't think of a reason why you would want to get only the read messages.

 

Try this (untested)

<?php
//Create an run the query to get unread message count
$queryStr = "SELECT COUNT(*) as unreadCount FROM `inbox`  WHERE `to` = :username AND `read` = 0";
$inboxQry = $db->prepare($queryStr);
$inboxQry->bindParam(":username", $Username);
$inboxQry->execute();
 
//Get the results
$inbox = $inboxQry->fetchObject();
$inbox->unreadCount;

//Prepare the output based on results
$inboxCountHtml = ($inbox->unreadCount) ? "{$userInbox->unreadCount} New" : "0";
 
?>
<div class="FooterText"><a href="Inbox.php">Inbox</a> (<?php echo $inboxCountHtml; ?>)

 

Thanks for your help! I have tried and tested this bit of code but it is still saying that I have no messages yet I know I do.

 

This bit:

 

$inboxCountHtml = ($inbox->unreadCount) ? "{$userInbox->unreadCount} New" : "0";

 

Has slightly confused me though? Should {$userInbox->unreadCount} be {$inbox->unreadCount} as where did the $userInbox variable come from

 

Thanks for your help

Edited July 20, 2016 by dean7

[ginerjm]

Why are you fetching an object when the result is just a simple value? Try:

//Get the results
$inbox = $inboxQry->fetch(PDO::FETCH_ASSOC);
$cnt = $inbox['unreadCount'];

//Prepare the output based on results
$html = "You have $cnt new messages";
echo "<div class='FooterText'><a href='Inbox.php">$html</a>";

[Psycho]

This bit

$inboxCountHtml = ($inbox->unreadCount) ? "{$userInbox->unreadCount} New" : "0";

 

Has slightly confused me though? Should {$userInbox->unreadCount} be {$inbox->unreadCount} as where did the $userInbox variable come from

 

 Yes, I changed some names half way through. As I said - not tested. Add some debugging to validate what IS returned from the query.

 

Updated code:

 

<?php
//Create an run the query to get unread message count
$queryStr = "SELECT COUNT(*) as unreadCount FROM `inbox`  WHERE `to` = :username AND `read` = 0";
$inboxQry = $db->prepare($queryStr);
$inboxQry->bindParam(":username", $Username);
$inboxQry->execute();
 
//Get the results & create the output
$inbox = $inboxQry->fetch(PDO::FETCH_ASSOC);
$inboxCountHtml = ($inbox['unreadCount']) ? "{$inbox['unreadCount']} New" : "0";
 
//Debug lines
echo "Query result: "; var_dump($inboxQry);
echo "Fetched Row: "; var_dump($inbox);
 
?>
<div class="FooterText"><a href="Inbox.php">Inbox</a> (<?php echo $inboxCountHtml; ?>)