Click here to Skip to main content
15,066,193 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have one problem I have added unread count filed to my script After display like this message

Fatal error: Call to a member function get_result() on boolean in C:\xampp\htdocs\demo\npm\cons.php on line 110

I have added line

  (SELECT from_id, COUNT(*) unread FROM pm WHERE to_id=? AND read='0' GROUP BY from_id)


my problem here SELECT from_id, I want to add it counting one by one users unread messages showing i want to know to add it to my script.

my database pm table

    id  from_id     to_id    msg               sent_date              read    
    1   2           3        hi how are you?   2019-12-05 04:14:20    1                
    2   3           2        fine              2019-12-05 05:15:58    0               
    3   2           3        hi                2019-12-05 03:20:34    1                  
    4   5           2        hi                2019-12-05 08:30:40    0   
users table

userid username
2      previn
3      rajesh
5      raj
Here is my source code

    <?php
     if (isset($_SESSION['userid'])) {
        $session_id = $_SESSION['userid'];
     }


    $sql = "SELECT *,

    (SELECT username FROM users WHERE userid=from_id) AS from_username,
    (SELECT username FROM users WHERE userid=to_id) AS to_username,
    (SELECT username FROM users WHERE userid=?) AS my_username,
(SELECT from_id, COUNT(*) unread FROM pm WHERE to_id=? AND read='0' GROUP BY from_id)


    FROM pm WHERE from_id = ? or to_id=?   ORDER BY id DESC";


    if ($stmt->prepare($sql)) {
       $stmt->bind_param('iiii', $session_id, $session_id,$session_id,$session_id);
       $stmt->execute();
       $result = $stmt->get_result();
       while ($row = $result->fetch_assoc()) {
          echo $row['from_username'] . " -> " . $row['to_username'] . "<br>";
          echo 'User ' . $row['from_id'] . ' - ' . $row['unread'];
       }
      }
    ?>


What I have tried:

<
I have added line
<code><pre> (SELECT from_id, COUNT(*) unread FROM pm WHERE to_id=? AND read='0' GROUP BY from_id)
Posted
Updated 13-Jan-20 10:27am
Comments
Wendelius 13-Jan-20 14:31pm
   
Could elaborate more, what would be the expected result from the query based on the example data?
Wendelius 13-Jan-20 14:43pm
   
Yes, that's the error message of the statement, but what the SQL statement should return?

Shouldn't:
PHP
while ($row = $result->fetch_assoc()) {
    echo $row['from_username'] . " -> " . $row['to_username'] . "<br>";
    echo 'User ' . $row['from_id'] . ' - ' . $row['unread'];
}

be enclosed if an if-statement? I believe your error is saying $result has the potential to be false, which might add some strangeness/error to your while loop.

e.g.
PHP
while($row=false->fetch_assoc())

You'd want to check the $result has values before doing the fetch_assoc().
   
v3
Most probably, $stmt->execute() returned false, thus the statement couldn't be prepared properly, and get_result() method cannot be called against it.
Try
PHP
if ($stmt->execute()) {
   $result = $stmt->get_result();
   while ($row = $result->fetch_assoc()) {
      echo $row['from_username'] . " -> " . $row['to_username'] . "<br>";
      echo 'User ' . $row['from_id'] . ' - ' . $row['unread'];
   }
}

Another detail which could be an issue: if the read column of the pm table is a bit type, you should not enclose the value between single quotes. Single quotes are for string types only. Try
SQL
SELECT from_id, COUNT(*) unread FROM pm WHERE to_id=? AND read=0 GROUP BY from_id
instead.
   
Comments
previn md 14-Jan-20 4:55am
   
it not working
previn md 14-Jan-20 4:56am
   
please can you help me i have lot of tried
phil.o 14-Jan-20 5:17am
   
I could try to help, but you will have to be much more specific as to what "not working" means. I'm not a magician, nor can I read your mind or see your screen.
previn md 14-Jan-20 6:02am
   
display like this message Fatal error: Call to a member function get_result() on boolean in C:\xampp\htdocs\demo\npm\cons.php on line 120
previn md 14-Jan-20 6:06am
   
problem SELECT from_id, and GROUP BY from_id when i removed this working but all users unread messages same like this user1 unread message 2, user2 unread message 2, user3 unread message 2, display same
previn md 14-Jan-20 6:07am
   
I want to get like this user1 unread message 4, user2 unead message 7, user3 unread message 1
phil.o 14-Jan-20 9:40am
   
After re-reading your original post and your extra explanation for the third time, I still have a hard time understanding what result exactly you are expecting from your query. You seem to select all columns of each message (the SELECT *), but then want a count on each message line as to the number of unread messages for the target of current message. You then add the names of source and target users using separate queries, when you could just join both tables to get the information; doing this would make much more sense than issuing sub-queries yourself.
Please, use the green Improve question widget which appears on hovering your question, and qualify clearly which columns you want in your final result.
previn md 15-Jan-20 13:25pm
   
please help me bro
previn md 14-Jan-20 10:32am
   
I want to count one by one users unread messages when i joined this script it not working SELECT from_id, COUNT(*) unread FROM pm WHERE to_id=? AND read=0 GROUP BY from_id but this script working without another script both script working but after joined this script not working fettal error message display please help me i have lot of tried

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900