not able fetch results from tables

[shan2batman]

hi,

i'm trying to create a notification system the problem is i'm able to get results from rowCount results but not from fetchAll query. tried running it manually in phpmyadmin it works like a charm but it is not working when called from php script.

here is the code for the query logic:

$query="select private_message.receiver_uid,user.user_id,user.avatar from pm_notifications"
        . "inner join private_message"
        . "on private_message.pm_id=pm_notifications.message_id"
        . "inner join user"
        . "on user.user_id=pm_notifications.user_id"
        . "where private_message.receiver_uid=:sess_id"
        . "and pm_notifications.status=:status"
        . "order by pm_notifications.pm_notification_id desc";
$status='not_seen';
$stmt_q=$conn->prepare($query);
$stmt_q->bindValue(":sess_id",$_SESSION['id']);
$stmt_q->bindValue(":status",$status);
$results_q=$stmt_q->fetchAll();
$total_row=$stmt_q->rowCount();
var_dump($results_q);
foreach ($results_q as $row){
if($total_row>0)
    {
 if($row['user_id']==$_SESSION['id']) 
     {
     #there is no notifications
     $notify.='0';
     
 } else {
     #there is notifications
     $notify.='1';
     }
 
  }
    echo $notify;
}

 

[requinix]

echo out your query and see if you can spot the 7 problems with it.

[shan2batman]

i get the following error when i echo out $conn @requinix

Quote

<b>Recoverable fatal error</b>:  Object of class PDO could not be converted to string in <b>/opt/lampp/htdocs/myexpresspad/notification/ajax-pm-notification.php</b> on line <b>50</b><br />

 

Edited September 20, 2018 by shan2batman

[requinix]

Not $conn. The query.

[shan2batman]

i get the following error from ajax call

select private_message.receiver_uid,user.user_id,user.avatar from pm_notificationsinner join private_messageon private_message.pm_id=pm_notifications.message_idinner join useron user.user_id=pm_notifications.user_idwhere private_message.receiver_uid=:sess_idand pm_notifications.status=:statusorder by pm_notifications.pm_notification_id descarray(0) {
}
<br />
<b>Notice</b>:  Undefined variable: results_q in <b>/opt/lampp/htdocs/myexpresspad/notification/ajax-pm-notification.php</b> on line <b>59</b><br />
<br />
<b>Warning</b>:  Invalid argument supplied for foreach() in <b>/opt/lampp/htdocs/myexpresspad/notification/ajax-pm-notification.php</b> on line <b>59</b><br />

 

[requinix]

Okay, progress. Now actually look at that query. Can you see the problems with it?

[shan2batman]

after correcting the error from results_q to results_pm the query prints fine but i get an empty array like

array(0) {
}

 

[shan2batman]

@requinix i'm sorry i'm still learning ways with php can you enlighten me

[requinix]

There's not really a lot to enlighten you about. The problem with the query was something that took me a couple minutes to spot. This current problem took a second look as well.

You've prepared the query and bound the parameter values to it. What should the next step be? Don't look at your code. Don't even think about it. Instead think about what is supposed to happen next. In principle. When you have your answer, then you can look at your code and compare.

[shan2batman]

i guess i have to prepare the st and bind values before executing it thats what i have done before trying to fetch results.????

[shan2batman]

i even ran it manually as suggested from a previous post and everything worked fine ???

[requinix]

Let me highlight what you just said:

4 minutes ago, shan2batman said:

i guess i have to prepare the st and bind values before executing it thats what i have done before trying to fetch results.????

That's four steps. Now look at your code and compare. And in case you're not sure, each of those steps requires a separate method call.

[shan2batman]

ah i forgot to execute thanks a ton but still the ajax call returns a empty array bro where am i going wrong???

[requinix]

What array? All your script is outputting, at least according to the code in your first post, will be a bunch of 0s and 1s.

So what's your current code?

[shan2batman]

here is my current code no big changes except execution of query.

 

$query="select private_message.receiver_uid,user.user_id,user.avatar from pm_notifications"
        . " inner join private_message"
        . " on private_message.pm_id=pm_notifications.message_id"
        . " inner join user"
        . " on user.user_id=pm_notifications.user_id"
        . " where private_message.receiver_uid=:sess_id"
        . " and pm_notifications.status=:status"
        . " order by pm_notifications.pm_notification_id desc";
//var_dump($conn);
$status='not_seen';
$stmt_pm=$conn->prepare($query);
$stmt_pm->bindValue(":sess_id",$_SESSION['id']);
$stmt_pm->bindValue(":status",$status);
$stmt_pm->execute();
$results_pm=$stmt_pm->fetchAll();
$total_row=$stmt_pm->rowCount();
var_dump($results_pm);

foreach ($results_pm as $row){
if($total_row>0)
    {
 if($row['user_id']==$_SESSION['id']) 
     {
     #there is no notifications
     $notify.='0';
     
 } else {
     #there is notifications
     $notify.='1';
     }
 
  }
    echo $notify;
}

 

[requinix]

Okay, well, it's still going to be outputting 0s and 1s, so...

What array?

[shan2batman]

i think the query isn't parsing. Atleast, that's what i think and it isn't outputting 0's and 1's as the $results_pm isn't fetching the desired values from the query.

[requinix]

Could the $_SESSION[id] value be something other than what you expect it to be? Are you sure when you ran the query manually you had the right values?

var_dump($_SESSION['id']);

select private_message.receiver_uid,user.user_id,user.avatar from pm_notifications
inner join private_message on private_message.pm_id=pm_notifications.message_id
inner join user on user.user_id=pm_notifications.user_id
where private_message.receiver_uid=(ID FROM SESSION) and pm_notifications.status='not_seen'
order by pm_notifications.pm_notification_id desc

 

[shan2batman]

yes

[requinix]

Just in case, make sure your database code is using exceptions instead of errors.

$conn = new PDO(???, ???, ???, array(
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));

// or

$conn = new PDO(...);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Then if there are any errors your script will die a horrible death and you'll see immediately that there is a problem.

Otherwise I don't see why you aren't getting results when there should be. If you're expecting multiple rows then I can tell you the 0/1 output will be wrong, but I was figuring we'd tackle that problem when the time came for it.

[shan2batman]

@requinixfor some reason firefox with user1 shows results and chrome with user 2 doesn't show results while both users values are in the mysql table. And you are right the output comes with multiple rows any advice here will be valuable.

Edited September 20, 2018 by shan2batman

[ginerjm]

I have no idea what is going on here but this puzzles me.

You check the row count and then you begin a loop.  In that loop you examine the row count and do something based on its value.  That makes no sense since if the row count is actually zero you will never enter the loop to do your code that checks that count.  Here is what I'm seeing:

foreach ($results_pm as $row)
{
 if($total_row>0)
 {
  if($row['user_id']==$_SESSION['id'])
  {
   #there is no notifications
   $notify.='0';
  }
  else
  {
   #there is notifications
   $notify.='1';
  }
 }
 echo $notify;
}

 

(ps - I really hate how this forum just doesn't respect the formatting of pasted in code)

Edited September 20, 2018 by ginerjm

[requinix]

4 hours ago, ginerjm said:

(ps - I really hate how this forum just doesn't respect the formatting of pasted in code)

What are you doing for adding code? You should be able to hit the <> button, paste code into the popup, change the language (I should find a way to switch the default), and insert without any problems.

[ginerjm]

I have always done my code insertion manually using the bracketed 'php' or 'code' indicators and then pasting in my editor's code.  That loses all formatting that I now see is maintained when using the <> icon.  Thanks for the heads-up!

Suggestion?  The tab-size could be reduced here, don't you think?  My editor uses 4 spaces for a tab; the forum uses I don't-know-how-many spaces but it's rather large.  Example using code having a tab size of 4 spaces:

while ($row=$qrslts->fetch())
{
	echo "<tr>
		<td>{$row['city']}</td>
		<td>{$row['team']}</td>
		<td>{$row['city_abbr']}</td>
        </tr>";
	if ($xyz)
	{
		echo "<tr><td>asdf</td></tr>";
	}
}
echo "</table>";

As you see the tabs here are rather wide...

Anyway - I am happy to have learned something new today!

[shan2batman]

@requinix i changed the code to some extent for fetching results still it doesn't fetch them can you once again help mr????

 

here is my code:

 if($friend_username !=$_SESSION['uname']){         
$query="select private_message.receiver,pm_notifications.message_id,pm_notifications.user_id_pm,user.user_id,user.avatar from pm_notifications"
        . " inner join private_message"
        . " on private_message.os_id=pm_notifications.os_id_pm"
        . " inner join user"
        . " on user.user_id=pm_notifications.user_id_pm"
        . " where private_message.receiver=:sess_id"
        . " and private_message.sender=:f_uname and pm_notifications.status=:status and pm_notifications.user_id_pm=:sess"
       . " order by pm_notifications.pm_notification_id  desc" ;
//var_dump($query);
$status='not_seen';
var_dump($friend_username);

$stmt_pm_f=$conn->prepare($query);
$stmt_pm_f->bindValue(":f_uname", $friend_username);
$stmt_pm_f->bindValue(":sess",$_SESSION['id']);
$stmt_pm_f->bindValue(":sess_id",$_SESSION['uname']);
$stmt_pm_f->bindValue(":status",$status);
$stmt_pm_f->execute();
$results_pm_f=$stmt_pm_f->fetchAll();
$total_row_f=$stmt_pm_f->rowCount();
var_dump($results_pm_f);
var_dump($stmt_pm_f->rowCount());
foreach ($results_pm_f as $row1) {
   

if($total_row>0)
    {
 if($row1['user_id_pm']==$_SESSION['id']) 
     {
     #there is no notifications
   echo '<a href="private_message.php?u='.$friend_username.'">'
          . '<img class="friendpics" src="'.$friend_pic.'" height="20" width="20" alt="'.$friend_username.'" title="'.$friend_username.'">'.$fname.' '.$lname.'<br>('
          .$friend_username.')</a><br><hr>';
     
 } else {
     #there is notifications
      echo '<span class="label label-pill label-success" style="color: white; float:left;">New</span><div style="background: #cce0cc; height:125px; width:100%;"><a style="text-align:center;" href="private_message.php?u='.$friend_username.'">'
          . '<img class="friendpics" src="'.$friend_pic.'" height="20" width="20" alt="'.$friend_username.'" title="'.$friend_username.'">'.$fname.' '.$lname.'<br>('
          .$friend_username.')</a></div><br><hr>';

     }
 
  } else {
    echo '<a href="private_message.php?u='.$friend_username.'">'
          . '<img class="friendpics" src="'.$friend_pic.'" height="20" width="20" alt="'.$friend_username.'" title="'.$friend_username.'">'.$fname.' '.$lname.'<br>('
          .$friend_username.')</a><br><hr>';
 
}
 }}
    
    }
 }
 echo '</div></div>';
 
      }