如何将特定列从sql结果保存到JSON数组？

Question

I am executing an SQL query, and I get number of rows as a result:

$sql = "SELECT id, name, age FROM users";
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

But for certain purpose, I need to save only the id column as a JSON array (containing all the ids of the result), so that in the end I should have a $idJsonArray variable that will contain all the ids in this form: {11, 15, 74, 100} for example (assuming that the query returned 4 rows containing the ids 11, 15, 74 and 100)

Answer 1

You can use array_column() to extract the id column and then json_encode() just the result...

$jsonIDs = json_encode(array_column($result, "id"));

will give...

[11, 15, 74, 100]

(As it is an array, it will be in [] instead of {})

If you need it as an object output, then add JSON_FORCE_OBJECT to the encode...

$jsonIDs = json_encode(array_column($result, "id"), JSON_FORCE_OBJECT);