php - query data to json response

Question

I'm trying to fetch data from php via PDO to then receive a JSON object (json_encode) to have something to work with on frontend. Try as I might, the only data I receive from MySQL query are the usernames of users who added the messages (tweets), see below:

Tweet class:

class Tweet extends User {

private $id;
private $userId;
private $text;
private $creationDate;

public function __construct() {
    $this->id = -1;
    $this->userId = null;
    $this->text = null;
    $this->creationDate = null;
}

function getId() {
    return $this->id;
}

function getUserId() {
    return $this->userId;
}

function getText() {
    return $this->text;
}

function getCreationDate() {
    return $this->creationDate;
}

function setUserId($userId) {
    $this->userId = $userId;
}

function setText($text) {
    $this->text = $text;
}

function setCreationDate($creationDate) {
    $this->creationDate = $creationDate;
}

static public function loadTweetById(PDO $pdo, $id) {
    $stmt = $pdo->prepare("SELECT * FROM Messages WHERE message_id=:id");
    $result = $stmt->execute([
        'id' => $id
    ]);

    if ($result === true && $stmt->rowCount() > 0) {
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        $loadedTweet = new Tweet();
        $loadedTweet->id = $row['message_id'];
        $loadedTweet->userId = $row['user_id'];
        $loadedTweet->text = $row['message_text'];
        $loadedTweet->creationDate = $row['message_datetime'];

        return $loadedTweet;
    }

    return null;
}

static public function loadAllTweetsByUserId(PDO $pdo, $id) {
    //$stmt = $pdo->prepare("SELECT * FROM Messages WHERE user_id=:id");
    $stmt = $pdo->prepare("SELECT "
            . "Users.username, "
            . "Messages.message_text, "
            . "Messages.message_datetime "
            . "FROM "
            . "Messages "
            . "JOIN "
            . "Users "
            . "ON Users.id=Messages.user_id "
            . "WHERE `user_id`=:id "
            . "ORDER BY Messages.message_datetime DESC");
    $result = $stmt->execute([
        'id' => $id
    ]);

    //var_dump($stmt->rowCount());


    if ($result === true && $stmt->rowCount() > 0) {
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

            $loadedTweet = new Tweet();
            //echo $loadedTweet->id = $row['message_id'] . '<br>';
            echo $loadedTweet->userId = $row['username'] . '<br>';
            echo $loadedTweet->text = $row['message_text'] . '<br>';
            echo $loadedTweet->creationDate = $row['message_datetime'] . '<br>';
            echo "<br>";


            //return $loadedTweet;
        }

        return null;
    }
}

static public function loadAllTweets(PDO $pdo) {

    $sql = "SELECT * FROM Messages JOIN Users ON Users.id=Messages.user_id ORDER BY Messages.message_datetime DESC";

    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    $tweets = $stmt->fetchAll(PDO::FETCH_OBJ);
    $tweetsList = [];

    if ($stmt !== false && $stmt->rowCount() > 0) {
        foreach ($tweets as $dbTweet) {
            $tweet = new Tweet($pdo);
            $tweet->id = $dbTweet->message_id;
            $tweet->userId = $dbTweet->user_id;
            $tweet->text = $dbTweet->message_text;
            $tweet->creationDate = $dbTweet->message_datetime;
            $tweet->getUsername = $dbTweet->username;


            $tweetsList[] = $tweet;
        }
        return $tweetsList;
    } else {
        return null;
    }

}

public function saveToDB(PDO $pdo) {
    //sprawdza czy robimy insert czy update
    if ($this->id == -1) { // if -1, robimy insert
        //przygotowanie zapytania
        //$userId = $_SESSION['userId'];
        $sql = "INSERT INTO `Messages`(`user_id`, `message_text`) VALUES (:user_id, :message_text)";
        $prepare = $pdo->prepare($sql);

        //wyslanie zapytania do bazy z kluczami i wartosciami do podmienienia
        $result = $prepare->execute([
            //uzywa userId zapisanego w sesji
            'user_id' => $this->userId,
            'message_text' => $this->text
        ]);

        // pobranie ostatniego ID dodanego rekordu i przypisanie do ID w tabeli Users
        //$this->id = $pdo->lastInsertId();


        return (bool) $result;
    }
}

function loadAllTweets from Tweet class (returns an array of Tweet objects):

    static public function loadAllTweets(PDO $pdo) {

    $sql = "SELECT * FROM Messages JOIN Users ON Users.id=Messages.user_id ORDER BY Messages.message_datetime DESC";

    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    $tweets = $stmt->fetchAll(PDO::FETCH_OBJ);
    $tweetsList = [];

    if ($stmt !== false && $stmt->rowCount() > 0) {
        foreach ($tweets as $dbTweet) {
            $tweet = new Tweet();
            $tweet->id = $dbTweet->message_id;
            $tweet->userId = $dbTweet->user_id;
            $tweet->text = $dbTweet->message_text;
            $tweet->creationDate = $dbTweet->message_datetime;
            $tweet->getUsername = $dbTweet->username;


            $tweetsList[] = $tweet;
        }
        return $tweetsList;
    } else {
        return null;
    }

}

mainpage.php

<?php
include_once '../../bootstrap.php';
header('Content-Type: application/json');//return json header

    $username = $_SESSION['username'];
    $tweets = Tweet::loadAllTweets($connection);
    $jsonTweets = [];


foreach ($tweets as $tweet) {
    $jsonTweets[] = json_decode(json_encode($tweet), true);
}

$response = ["tweets" => $jsonTweets,
            "success" => $username];

echo json_encode($response);

ajax

$.ajax({
        url: "../admin/ajax/mainpage.php",
        dataType: 'json'
    })
        .done(function (response) {
            console.log(response.tweets);
        })
        .fail(function (response) {
            console.log(response);
        });

Any insight on what I'm doing wrong is greatly appreciated.

Answer 1

The private properties of the Tweet class are not visible to json_encode and therefore not included in the json. Make them public or implement \JsonSerializable with a custom jsonSerialize implementation, e.g.

class Tweet implements \JsonSerializable
{
  // ...

        public function jsonSerialize()
        {
            return [
                'foo' => $this->foo,
                'bar' => $this->bar
            ];
        }
}

More on visibility: http://docs.php.net/language.oop5.visibility The not so obvious thing is that your static method loadTweetData can indeed access the private props because the objects are of the same class.

Note off topic: Having your Tweet model extend User is a little awkward because the Tweet is not a User (violates Liskov substitution principle).