我有来自mysql的数据并且错误地转换为JSON

Question

I have the following table:

-> +-----+---------+-----+-------+--------------+
-> | id1 | fname   | id2 | fname | relationship |
-> +-----+---------+-----+-------+--------------+
-> |   4 | Albaraa |   5 | Sadi  | Father       |
-> +-----+---------+-----+-------+--------------+

I assign the following to the variable $relations:

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

Finally I print out the JSON encode of this variable using this:

echo json_encode($relations);

...and I get the following output:

[{"id1":"4","fname":"Sadi","id2":"5","relationship":"Father"}]

So here is a few questions about the output that I get:

1) You can see that my output does not seem right as it should be something like this:

[{"id1":"4","fname":"Albaraa","id2":"5","fname":"Sadi","relationship":"Father"}]

Why is it not printing correctly, does it have something to do with the fact that the table, has two columns with the same name "fname"? If there is any input on how to fix this that would be amazing!

2) Another thing I was curious to know is that I actually want to change the JSON to where it becomes something like this:

[{"id":"4","name":"Albaraa","id":"5","name":"Sadi","relationship":"Father"}]

Is there an easy way to change "id1" and "id2" to become "id" and both "fname" to become just "name"?

Again any input on this will be greatly appreciated!

Answer 1

1) fname was overwritten. Try use different aliases in SQL query for matching fields.

2) You should not pass exact name matching properties. They might be overwitten by JSON-decoder. Better to pass them as a different JSON-parts.

[{"id":"4","name":"Albaraa","id":"5","name":"Sadi","relationship":"Father"}]

Should be like:

{
    "person1":{"id":4,"name":"Albaraa"},
    "person2":{"id":5,"name":"Sadi"},
    "relationship":"Father"
}

---

UPDv1:

For result:

-> +-----+---------+-----+--------+--------------+
-> | id1 | fname1  | id2 | fname2 | relationship |
-> +-----+---------+-----+--------+--------------+
-> |   4 | Albaraa |   5 | Sadi   | Father       |
-> +-----+---------+-----+--------+--------------+

Try to convert like this:

<?php
// $row is sql fetched row.

$person1  = array('id' => $row['id1'], 'name' => $row['fname1']);
$person2  = array('id' => $row['id2'], 'name' => $row['fname2']);
$response = array(
     'person1' => $person1,
     'person2' => $person2,
     'relationship' => $row['relationship']
);

echo json_encode($response);

?>