CODEWITHSUNDEEP
phpmysql
Bios90
Bios90

Reputation: 811

Right way to JOIN tables in mysql for creating JSON

Im trying to use MySQL JOIN in php query in the right way. I already have read a lot of guides and answers but i cant find a real life example how to connect one "parent" table with all its "children".

For example. I have 3 tables. One of them is main called "Bands"

+---------+-----------+-------------+
| band_id | band_name | band_rating |
+---------+-----------+-------------+
| 777     | Beatles   | 100         |
+---------+-----------+-------------+

Second table called Musicians. It has column "band_id" that joined to "band_id" in Bands

+-------------+---------+----------------+---------------------+
| musician_id | band_id | musician_name  | musician_instrument |
+-------------+---------+----------------+---------------------+
| 1           | 777     | John Lennon    | Voice               |
+-------------+---------+----------------+---------------------+
| 2           | 777     | Paul McCartney | Guitar              |
+-------------+---------+----------------+---------------------+
| 3           | 777     | Ringo Starr    | Drums               |
+-------------+---------+----------------+---------------------+

The last table called Songs. It also has column "band_id" that joined to "band_id" in Bands.

+---------+---------+-----------+-----------+
| song_id | band_id | song_name | song_year |
+---------+---------+-----------+-----------+
| 1       | 777     | Hey Jude  | 1968      |
+---------+---------+-----------+-----------+
| 2       | 777     | Let it be | 1970      |
+---------+---------+-----------+-----------+
| 3       | 777     | Yesterday | 1965      |
+---------+---------+-----------+-----------+

So, how should i make MySLQ query with join to get band with all its songs and musicians. For example i want to make final echo as JSON:

{
"id": 777,
"name": "Beatles",
"rating": 100,
"musicians":
[
    {
    "id": 1,
    "name": "John Lennon",
    "instrument": "Voice"
    },    

    {
    "id": 2,
    "name": "Paul McCartney",
    "instrument": "Guitar"
    },

    {
    "id": 3,
    "name": "Ringo Starr",
    "instrument": "Drums"
    } 
],
"songs":
[
    {
    "id":1,
    "name": "Hey Jude",
    "year": "1968"
    },

    {
    "id":2,
    "name": "Let it be",
    "year": "1970"
    },

    {
    "id":3,
    "name": "Yesterday",
    "year": "1965"
    }
]
}

What is the right way to make such queries?

Upvotes: 0

Views: 72

Answers (1)

Ayush Jain
Ayush Jain

Reputation: 349

You can run two queries and then parse the results of those queries to get your results. 

$musicians = DB::select("SELECT bands.id, bands.band_id, bands.band_name, bands.band_rating, musicians.musician_id as musician_id, 
                              musicians.musician_name as musician_name, musicians.musician_instrument as musician_instrument
                            FROM bands
                            LEFT JOIN musicians
                            ON bands.band_id = musicians.band_id
                            Where bands.band_id=777;");

            $songs = DB::select("SELECT bands.id, bands.band_id, bands.band_name, bands.band_rating, Songs.song_id as song_id, Songs.song_name as song_name, 
                            Songs.song_year as song_year
                        FROM bands
                        LEFT JOIN Songs
                        ON bands.band_id = Songs.band_id
                        Where bands.band_id=777;");


            $output = array();
            $output['id'] = $musicians[0]->band_id;
            $output['name'] = $musicians[0]->band_name;
            $output['rating'] = $musicians[0]->band_rating;
            $output['musicians'] = array();
            $output['songs'] = array();

            $index = 0;
            foreach($musicians as $data) {
                $output['musicians'][$index]['id'] = $data->musician_id;
                $output['musicians'][$index]['name'] = $data->musician_name;
                $output['musicians'][$index]['instrument'] = $data->musician_instrument;
                $index++;
            }


            $index = 0;
            foreach($songs as $data) {
                $output['songs'][$index]['id'] = $data->song_id;
                $output['songs'][$index]['name'] = $data->song_name;
                $output['songs'][$index]['instrument'] = $data->song_year;
                $index++;
            }

Finally, You can use json_encode() function to get output in JSON Format.

$finalOutput = json_encode($output);

Upvotes: 1

Related Questions