Thomas Degroot Thomas Degroot - 4 months ago 48
SQL Question

Join mySQL tables and output JSON for Ionic 2 App

I am trying to join two tables example below;

demo_one
----------------------------------------
id | name | position | phone
----------------------------------------
1 | Moby Dick | Whale | 1-403-651-4000
2 | Jaws | Shark | 1-403-651-4000


demo_two
---------------------------------------------------
id | name | type | cell
---------------------------------------------------
1 | Son of Moby Dick | Whale | 1-403-651-4000
2 | Son of Jaws | Shark | 1-403-651-4000


and this is the json result I am looking for

{
"demo_one":[
{"name":"Moby Dick", "position":"Whale", "phone":"1-403-651-4000"},
{"name":"Jaws", "position":"Shark", "phone":"1-403-651-4000"}
],

"demo_two":[
{"name":"Son of Moby Dick", "type":"Whale", "cell":"1-403-651-4000"},
{"name":"Son of Jaws", "type":"Shark", "cell":"1-403-651-4000"}
]
}


I have seen some examples on stack overflow, but I require this json output for my Ionic 2 App.

Answer

This doesn't require a join, just two separate queries. Their results get put into different properties of the JSON result.

$result = array();
$query1 = $pdoconn->query("SELECT * FROM demo_one");
$result['demo_one'] = $query1->fetchAll(PDO::FETCH_ASSOC);
$query2 = $pdoconn->query("SELECT * FROM demo_two");
$result['demo_two'] = $query2->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($result);