PHP - Problems with JSON encoding

Question

PHP - problems with JSON encoding

I'm currently developing an android app as a part of my school project. I need to setup an API which will retrieve all the lectures from the database and outputs them as JSON. Here's an example of how would I like them to be outputted:

{
    "count: 4,
    "msg": "",
    "user_id": 1,
    "name": "John Doe"

    "lectures": [{
        "id": 1,
            "starting_at": "2015-11-30 13:00",
            "ending_at": "2015-11-30 15:00",
            "course_name": "Name of Course #1",
    }, {
        "id": 2,
            "starting_at": "2015-11-30 13:00",
            "ending_at": "2015-11-30 15:00",
            "course_name": "Name of Course #2",
    }, {
        "id": 3,
            "starting_at": "2015-11-30 13:00",
            "ending_at": "2015-11-30 15:00",
            "course_name": "Name of Course #3",
    }]
}

But instead, it duplicates all the objects I retrieve and my output looks like this:

{
    "count":4,
    "msg":"",

    "lectures":[{
        "id":"1",
        "0":"1", // Duplicated object
        "starting_at": "2015-11-30 13:00:00",
        "1":"2015-11-30 13:00:00", // Duplicated object
        "ending_at":"2015-11-30 15:00:00",
        "2":"2015-11-30 15:00:00", // Duplicated object
        "user_id":"1",
        "3":"1", // Duplicate object
        "course":"Course Name #1",
        "4":"Course Name #1", // Duplicated object
        "user_name":"John Doe",
        "5":"John Doe"
    }, {
        "id":"2",
        "0":"2", // Duplicated object
        "starting_at": "2015-11-30 13:00:00",
        "1":"2015-11-30 13:00:00", // Duplicated object
        "ending_at":"2015-11-30 15:00:00",
        "2":"2015-11-30 15:00:00", // Duplicated object
        "user_id":"1",
        "3":"1", // Duplicated object
        "course": "Course Name #2",
        "4":"Course Name #2", // Duplicated object
        "user_name":"John Doe",
        "5":"John Doe" // Duplicated object
  ]}
}

I have to admit that I'm relatively new to JSON and definitely need some help with this. Here's my PHP for the API call:

<?php
require_once("dbconnect.php");
$user_id = 1;

// Prepare the statement
$stmt = $pdo->prepare("
SELECT
lectures.id,
lectures.starting_at,
lectures.ending_at,
users_lectures.user_id,
courses.name AS course,
users.name AS user_name
FROM lectures
LEFT JOIN courses ON courses.id = lectures.course_id
LEFT JOIN users_lectures ON users_lectures.lecture_id = lectures.id
LEFT JOIN users ON users.id = users_lectures.user_id
WHERE users_lectures.user_id = :user_id
AND lectures.starting_at >= CURRENT_DATE()
ORDER BY lectures.starting_at DESC");

$stmt->execute(array(':user_id' => $user_id));
if($stmt->rowCount() > 0) {
    $lectures = array();

    while ($row = $stmt->fetch()) {    
        $lectures[] = $row;

    }

    echo json_encode(["count" => count($lectures), "msg" => "", "user_id" => $row['user_id'], "name" => $row['user_name'], "lectures" => [$lectures]]);
} else {
    echo json_encode(["count" => 0, "msg" => "No lectures today"]);
}

Answer 1

Those are not duplicates, they are due to PDO's FETCH_MODE. By default it fetches both numeric and associative keys.

Use

$stmt->fetch(PDO::FETCH_NUM) 

for numeric and

$stmt->fetch(PDO::FETCH_ASSOC)

for associative