John Davidson

Message:

I'm trying to create an API endpoint that will show two columns from two different SQL tables using a primary key. While the query works in DB browser, in the API it only returns the second column selected (in this case sessions.name). Didn't find a similar problem to other APIs created and dont know how to make them both appear.

$query = "SELECT rooms.name, sessions.name 

              FROM sessions JOIN rooms 

              WHERE sessions.roomId = rooms.roomId";

Update (code from comment):

private function json_rooms() { 

    $query = "SELECT rooms.name, sessions.name FROM sessions JOIN rooms WHERE sessions.roomId = rooms.roomId"; 

    $params = []; 

    return ($this->recordset->getJSONRecordSet($query, $params)); 

}

---

My Answer:
"SELECT users.username, sessions.name
FROM users
JOIN sessions ON users.id = sessions.user_id
WHERE users.id = :id";

$stmt = $pdo->prepare($query);
$stmt->bindParam(':id', $id);
$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo json_encode($result);