CODEWITHSUNDEEP
phpmysqljsonpdo
Cordell
Cordell

Reputation: 77

JSON returning wrong value from PDO query

I'm attempting to return the value of a column called "followers_count". On my site I have several users, each with their own count. The update works, in that when you click follow it updates in the database but I wanted to use JSON to display the change without the need to refresh the page. So far the code works, but it only returns the value of followers_count for the last user registered. Anyone know why?

In changes.php:

<?php

require_once 'class.channel.php';

$user_change = new USER();

$stmt = $user_change->runQuery("SELECT followers_count FROM tbl_users");
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);


$currFollows = $row['followers_count'];

$seqFollows = $user_change->runQuery( "SELECT currval('followers_count')" );

if ($seqFollows == $currFollows){
    exit(0);
}

$query = $user_change->runQuery($seqFollows);

while($row = $stmt->fetch($query))
{
$follows = $row['followers_count'];
}

header('Content-type: application/json');
$array = array('followers_count'=>$follows);
echo json_encode($array);

?>

And in index.php:

<div>
  Channel Adds: <div id="follow_count"></div>
</div>

<script type="text/javascript">

  $(document).ready(function(){
        $.getJSON('changes.php', function(data) {
          $('#follow_count').html(data.followers_count);
        });
      });

</script>

Upvotes: 1

Views: 198

Answers (1)

FluffyKitten
FluffyKitten

Reputation: 14312

As you loop through the database results, you are replacing the value of $follows each time, so you are only ever storing with the last value.

To add each count to an array you need to change this:

while($row = $stmt->fetch($query)) { 
    $follows = $row['followers_count']; 
}

to this:

while($row = $stmt->fetch($query)) {
    $follows[] = $row['followers_count'];
}

UPDATE:

There is something wrong with your queries:

$seqFollows = $user_change->runQuery( "SELECT currval('followers_count')" );
[...]
$query = $user_change->runQuery($seqFollows);

You are trying run $seqFollows which is a value not a query, so that won't get get the result you are looking for.

You say you are doing $query = $user_change->runQuery($seqFollows); to get the value of $seqFollows, but you already have it. So I suggest you try changing your change.php to the following to pass $seqFollows back as followers_count

<?php
require_once 'class.channel.php';

$user_change = new USER();

$stmt = $user_change->runQuery("SELECT followers_count FROM tbl_users");
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);

$currFollows = $row['followers_count'];

$seqFollows = $user_change->runQuery( "SELECT currval('followers_count')" );
$seqFollows->execute();
$row = $seqFollows->fetch(PDO::FETCH_ROW);
$follow_count = $row[0];

if ($follow_count == $currFollows){
    exit(0);
}

header('Content-type: application/json');
$array = array('followers_count'=>$follow_count);
echo json_encode($array);
?>

Upvotes: 2

Related Questions