0

I'm trying to find the number of comments made on each post, and it's working fine when the user is logged out, but if they're logged in it's doing something strange.

If they're logged in I check whether they've upvoted or downvoted something, but I've placed that within an if statement. Maybe it's conflicting?

<?php
if (isset($_SESSION['userid'])){
  $stmt = $db->prepare("SELECT pv_type,COUNT(*) FROM post_votes WHERE pv_postid = ? AND pv_userid = ?");
}

$stmt2 = $db->prepare("SELECT COUNT(*) FROM comments WHERE c_postid=?");

foreach($posts as $post):
      // [variables set here, removed for this post]

      if (isset($_SESSION['userid'])){
        $stmt->bind_param("ii",$p_id,$session_id);
        $stmt->execute();
        $stmt->bind_result($pv_type,$voted);
        $stmt->fetch();
      }

      $stmt2->bind_param("i",$p_id);
      $stmt2->execute();
      $stmt2->bind_result($numComments);
      $stmt2->fetch();

      if ($numComments == 1){
        $numComments = $numComments . " comment";
      } else {
        $numComments = $numComments . " comments";
      }
?>

        ** HTML to be rendered **

        <div class="comment-info"><?php echo $numComments; ?></div>

<?php
endforeach;

if (isset($_SESSION['userid'])){
  $stmt->close();
}
$stmt2->close();

When the user is logged in, it doesn't get the number of comments and on each post it adds another comments, e.g.

post 1 is comments, post 2 is comments comments, rather than 1 comment, 3 comments, etc.

I must be missing something here

Christian Esperar
  • 528
  • 5
  • 19
frosty
  • 2,779
  • 6
  • 34
  • 63
  • It looks to me like you just need to initialize `$numComments` inside the `foreach`. Otherwise you just keep adding onto it. Something like `$numComments = "";` at the start of each loop iteration, before calling the `execute()` that populates it. You aren't checking for errors, and a problem preparing or executing the second statement would cause `$numComments` to hold onto its previous value. – Michael Berkowski Feb 07 '16 at 14:11
  • Add some error checking like `if (!$stmt2->execute()) echo $stmt2->error;` You will need to check on each of the `$stmt2` methods. This could be a situation where MySQL is responding with a "commands out of sync" error due to incomplete fetching from the first statement. – Michael Berkowski Feb 07 '16 at 14:11
  • I get this: `Commands out of sync; you can't run this command now` – frosty Feb 07 '16 at 14:13
  • Yes, that's as I suspected. Your first statement probably returns more than one row, but you only `fetch()` once. Check the output from that query in a MySQL client. Do you expect only one row in your code? Do you expect multiple rows? You either need to free the result set or fetch all rows from it (or maybe add `LIMIT 1`) before MySQL will allow you to run another statement. – Michael Berkowski Feb 07 '16 at 14:16
  • See also http://stackoverflow.com/questions/3632075/mysqli-giving-commands-out-of-sync-error-why – Michael Berkowski Feb 07 '16 at 14:16
  • Just searched and found that adding `$stmt->store_result()` after the execute fixes it. It works now! – frosty Feb 07 '16 at 14:17
  • Ok, I'll link this to the other question I mentioned, since it includes that solution. – Michael Berkowski Feb 07 '16 at 14:18

0 Answers0