在json响应中显示2个id

Question

subjects.id is overriding users.id in the JSON response whenever i add subjects.idto the select in the query.

How can i show my both users.id and subject.id in the response

    $sql = "SELECT users.id,users.name,users.date,subjects.id FROM tb_user AS users INNER JOIN 
   tb_subjects AS subjects ON users.id = subjects.userid WHERE users.id = '$userid'";

    try {
        $db = new db();
        $db = $db->connect();
        $stmt = $db->prepare($sql);

        $stmt->execute();

        $user = $stmt->fetch(PDO::FETCH_OBJ);

        $db = null;

        if(empty($user)) {
            $response->getBody()->write
            ('
            {
                "error":
                {
                    "message":"Invalid"
                }
            }');
        } else {
            $response->getBody()->write(json_encode($user));
        }
    } catch(PDOException $e) {}

current output

{
"id": "1",
"name": "joe",
"date": "2017-07-22 18:37:37"
}

expected output

{
"id": "1",
"name": "joe",
"subjectid": "4",
"date": "2017-07-22 18:37:37"
}

Answer 1

To get around the problem of a result set with two id columns, give the subject id column an alias of subjectid:

SELECT
    users.id,
    users.name,
    users.date,
    subjects.id AS subjectid
FROM tb_user AS users
INNER JOIN tb_subjects AS subjects
    ON users.id = subjects.userid
WHERE users.id = '$userid'

Most databases seem to tolerate a result set which has two columns by the same name. But this would fail if it were to happen in a subquery in which you tried to also select that duplicate column. It looks like PHP is just deciding to choose one of the id columns though the best thing to do here is to fix the duplicate name problem in the query.

Edit:

If you wanted to get the latest subject, as indicated by its time column, you could slightly modify your query to use another join:

SELECT
    users.id,
    users.name,
    users.date,
    s1.id AS subjectid
FROM tb_user AS users
INNER JOIN tb_subjects s1
    ON users.id = s1.userid
INNER JOIN
(
    SELECT userid, MAX(time) AS max_time
    FROM tb_subjects
    GROUP BY userid
) s2
    ON s1.userid = s2.userid AND
       s1.time   = s2.max_time     
WHERE users.id = '$userid'

The subquery in the new join finds the latest time for each user. But this still does not give us the subject id which we actually want. To get this, we can access the first tb_subjects table, which however has been reduced after this new join to only records having the most recent message time. One caveat here: if you had a tie for most recent message my query would return all such ties. We could workaround this, but it would be more work.