dean7 Posted July 20, 2016 Share Posted July 20, 2016 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 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted July 20, 2016 Share Posted July 20, 2016 1 - turn on error checking so you can see messages 2 - remove the quotes from the parms in the query statement. Quote Link to comment Share on other sites More sharing options...
Psycho Posted July 20, 2016 Share Posted July 20, 2016 (edited) 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 Quote Link to comment Share on other sites More sharing options...
dean7 Posted July 20, 2016 Author Share Posted July 20, 2016 (edited) 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 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted July 20, 2016 Share Posted July 20, 2016 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>"; Quote Link to comment Share on other sites More sharing options...
Solution Psycho Posted July 20, 2016 Solution Share Posted July 20, 2016 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; ?>) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.