i have a music sharing script and i am trying to create JSON from my mysql database, I'm trying to create a specific structure and can't seem to figure it out
heres my code
<?php
//Create Database connection
$db = mysql_connect("localhost","UN","PW");
if (!$db) {
die('Could not connect to db: ' . mysql_error());
}
//Select the Database
mysql_select_db("DB",$db);
{ // <--WHY
$result = mysql_query("SELECT * FROM users ORDER BY username ASC");
$json_response = array(); //Create an array
while ($row = mysql_fetch_array($result))
{
$row_array = array();
$row_array['idu'] = $row['idu'];
$row_array['username'] = $row['username'];
$row_array['first_name'] = $row['first_name'];
$row_array['last_name'] = $row['last_name'];
$row_array['country'] = $row['country'];
$row_array['image'] = $row['image'];
$row_array['cover'] = $row['cover'];
$row_array['description'] = $row['description'];
$row_array['playlists'] = array();
$row_array['tracks'] = array();
$plid = $row['idu'];
$option_qry = mysql_query("SELECT * FROM playlists where playlists.by=$plid");
while ($opt_fet = mysql_fetch_array($option_qry))
{
$row_array['playlists'][] = array(
'id' => $opt_fet['id'],
'name' => $opt_fet['name'],
'by' => $opt_fet['by'],
);
}
array_push($json_response, $row_array); //push the values in the array
//tracks
$option_qry = mysql_query("SELECT * FROM tracks where tracks.uid=$plid");
while ($opt_fet = mysql_fetch_array($option_qry))
{
$row_array['tracks'][] = array(
'id' => $opt_fet['id'],
'title' => $opt_fet['title'],
'name' => $opt_fet['name'],
'art' => $opt_fet['art'],
'likes' => $opt_fet['likes'],
'downloads' => $opt_fet['downloads'],
'plays' => $opt_fet['views'],
'uid' => $opt_fet['uid'],
);
}
array_push($json_response, $row_array); //push the values in the array
}
echo json_encode($json_response,JSON_PRETTY_PRINT);
} // <-- WHY
?>
users Table:
id username last_name first_name country etc...
1 username1 Mynce George USA etc...
2 username2 Jenkins Fred USA etc...
3 username3 Walberg Mark USA etc...
4 username4 Smith Will USA etc...
playlists Table:
id by name
1 4 My playlists
2 3 Popular Songs
playlist entries Table:
id playlists track
12 1 13
13 1 23
14 1 3
15 1 17
16 1 9
12 2 14
13 2 24
14 2 4
15 2 18
16 2 10
tracks Table:
id title name art likes downloads views
1 song_title song1.mp3 art1.png 13 8 55
2 song_title song2.mp3 art2.png 12 10 31
3 song_title song3.mp3 art3.png 7 32 50
4 song_title song4.mp3 art4.png 22 11 1
5 song_title song5.mp3 art5.png 1 28 2
With my current code i am getting this JSON result:
{
"idu": "2",
"username": "chicagochurch",
"first_name": "Chicago Christian Center",
"last_name": "",
"country": "United States",
"image": "839674815_146358938_1746691162.png",
"cover": "173157219_1187606488_302826016.jpg",
"description": "This is the official Chicago Church Music page.",
"playlists": [
{
"id": "4",
"name": "We Give You Praise",
"by": "2"
}
],
"tracks": []
},
{
"idu": "2",
"username": "chicagochurch",
"first_name": "Chicago Christian Center",
"last_name": "",
"country": "United States",
"image": "839674815_146358938_1746691162.png",
"cover": "173157219_1187606488_302826016.jpg",
"description": "This is the official Chicago Church Music page.",
"playlists": [
{
"id": "4",
"name": "We Give You Praise",
"by": "2"
}
],
"tracks": [
{
"id": "29",
"title": "01 We Give You Praise",
"name": "1667450919_35711384_1898671201.mp3",
"art": "97020012_1272964020_1490042512.png",
"likes": "1",
"downloads": "1",
"plays": "5",
"uid": "2"
},
{
"id": "30",
"title": "02 Luvudees",
"name": "1361859314_884859216_209326964.mp3",
"art": "1591964284_1156840179_1721104535.png",
"likes": "1",
"downloads": "1",
"plays": "5",
"uid": "2"
},
{
"id": "31",
"title": "03 Mugo Del",
"name": "1366849477_130736941_1367659635.mp3",
"art": "1181156184_556035815_1698596436.png",
"likes": "0",
"downloads": "1",
"plays": "0",
"uid": "2"
},
{
"id": "32",
"title": "04 San Amensa",
"name": "40925819_971317614_1732715256.mp3",
"art": "837149755_251638008_1945445596.png",
"likes": "0",
"downloads": "0",
"plays": "1",
"uid": "2"
},
{
"id": "33",
"title": "05 One True God",
"name": "1201163785_1107025307_1077346045.mp3",
"art": "713651840_1636034922_1247445482.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "34",
"title": "06 Mugo Anav",
"name": "173569477_686122962_117960391.mp3",
"art": "1432749408_578109445_1094716795.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "35",
"title": "07 Hallelujah",
"name": "133051077_70993845_552471086.mp3",
"art": "2040610215_1811023913_383444282.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "36",
"title": "08 Sheppard",
"name": "984429058_1532916377_1375134853.mp3",
"art": "1153269141_143559426_997684622.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "37",
"title": "09 I Love you Lord",
"name": "794115968_1411878888_673035094.mp3",
"art": "1692460167_1433248811_682631716.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "38",
"title": "10 Jesus In The Center",
"name": "202390322_461558278_1271927584.mp3",
"art": "1048499380_362527600_4599069.png",
"likes": "0",
"downloads": "1",
"plays": "0",
"uid": "2"
},
{
"id": "39",
"title": "11 Mugo Luvudeemos",
"name": "274506373_302381815_356651583.mp3",
"art": "1229728795_739090349_686501748.png",
"likes": "0",
"downloads": "1",
"plays": "0",
"uid": "2"
},
{
"id": "40",
"title": "12 Onday Chedo Kud",
"name": "378498031_1470949688_1199351944.mp3",
"art": "308978665_1074723934_419931699.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "41",
"title": "13 The Word",
"name": "956461611_287666310_43661088.mp3",
"art": "1584112229_240532531_977129308.png",
"likes": "1",
"downloads": "4",
"plays": "0",
"uid": "2"
}
]
}
The JSON below is the structure i am trying to get, i want each playlist a user creates to nest the tracks associated with that playlist in a nested array under that playlists User>Playlists>Tracks
I typed out two playlists here "We Give You Praise" and "We Give You Praise 2" for example
{
"idu": "2",
"username": "chicagochurch",
"first_name": "Chicago Christian Center",
"last_name": "",
"country": "United States",
"image": "839674815_146358938_1746691162.png",
"cover": "173157219_1187606488_302826016.jpg",
"description": "This is the official Chicago Church Music page.",
"playlists": [
{
"id": "4",
"name": "We Give You Praise",
"by": "2",
"tracks": [
{
"id": "29",
"title": "01 We Give You Praise",
"name": "1667450919_35711384_1898671201.mp3",
"art": "97020012_1272964020_1490042512.png",
"likes": "1",
"downloads": "1",
"plays": "4",
"uid": "2"
},
{
"id": "30",
"title": "02 Luvudees",
"name": "1361859314_884859216_209326964.mp3",
"art": "1591964284_1156840179_1721104535.png",
"likes": "0",
"downloads": "1",
"plays": "3",
"uid": "2"
},
{
"id": "31",
"title": "03 Mugo Del",
"name": "1366849477_130736941_1367659635.mp3",
"art": "1181156184_556035815_1698596436.png",
"likes": "0",
"downloads": "1",
"plays": "0",
"uid": "2"
},
{
"id": "32",
"title": "04 San Amensa",
"name": "40925819_971317614_1732715256.mp3",
"art": "837149755_251638008_1945445596.png",
"likes": "0",
"downloads": "0",
"plays": "1",
"uid": "2"
},
{
"id": "33",
"title": "05 One True God",
"name": "1201163785_1107025307_1077346045.mp3",
"art": "713651840_1636034922_1247445482.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "34",
"title": "06 Mugo Anav",
"name": "173569477_686122962_117960391.mp3",
"art": "1432749408_578109445_1094716795.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "35",
"title": "07 Hallelujah",
"name": "133051077_70993845_552471086.mp3",
"art": "2040610215_1811023913_383444282.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "36",
"title": "08 Sheppard",
"name": "984429058_1532916377_1375134853.mp3",
"art": "1153269141_143559426_997684622.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "37",
"title": "09 I Love you Lord",
"name": "794115968_1411878888_673035094.mp3",
"art": "1692460167_1433248811_682631716.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "38",
"title": "10 Jesus In The Center",
"name": "202390322_461558278_1271927584.mp3",
"art": "1048499380_362527600_4599069.png",
"likes": "0",
"downloads": "1",
"plays": "0",
"uid": "2"
},
{
"id": "39",
"title": "11 Mugo Luvudeemos",
"name": "274506373_302381815_356651583.mp3",
"art": "1229728795_739090349_686501748.png",
"likes": "0",
"downloads": "1",
"plays": "0",
"uid": "2"
},
{
"id": "40",
"title": "12 Onday Chedo Kud",
"name": "378498031_1470949688_1199351944.mp3",
"art": "308978665_1074723934_419931699.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "41",
"title": "13 The Word",
"name": "956461611_287666310_43661088.mp3",
"art": "1584112229_240532531_977129308.png",
"likes": "1",
"downloads": "4",
"plays": "0",
"uid": "2"
}
]
},
{
"id": "5",
"name": "We Give You Praise 2",
"by": "2",
"tracks": [
{
"id": "29",
"title": "01 We Give You Praise 2",
"name": "1667450919_35711384_1898671201.mp3",
"art": "97020012_1272964020_1490042512.png",
"likes": "1",
"downloads": "1",
"plays": "4",
"uid": "2"
},
{
"id": "30",
"title": "02 Luvudees 2",
"name": "1361859314_884859216_209326964.mp3",
"art": "1591964284_1156840179_1721104535.png",
"likes": "0",
"downloads": "1",
"plays": "3",
"uid": "2"
},
{
"id": "31",
"title": "03 Mugo Del 2",
"name": "1366849477_130736941_1367659635.mp3",
"art": "1181156184_556035815_1698596436.png",
"likes": "0",
"downloads": "1",
"plays": "0",
"uid": "2"
},
{
"id": "32",
"title": "04 San Amensa 2",
"name": "40925819_971317614_1732715256.mp3",
"art": "837149755_251638008_1945445596.png",
"likes": "0",
"downloads": "0",
"plays": "1",
"uid": "2"
},
{
"id": "33",
"title": "05 One True God",
"name": "1201163785_1107025307_1077346045.mp3",
"art": "713651840_1636034922_1247445482.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "34",
"title": "06 Mugo Anav 2",
"name": "173569477_686122962_117960391.mp3",
"art": "1432749408_578109445_1094716795.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "35",
"title": "07 Hallelujah 2",
"name": "133051077_70993845_552471086.mp3",
"art": "2040610215_1811023913_383444282.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "36",
"title": "08 Sheppard 2",
"name": "984429058_1532916377_1375134853.mp3",
"art": "1153269141_143559426_997684622.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "37",
"title": "09 I Love you Lord 2",
"name": "794115968_1411878888_673035094.mp3",
"art": "1692460167_1433248811_682631716.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "38",
"title": "10 Jesus In The Center 2",
"name": "202390322_461558278_1271927584.mp3",
"art": "1048499380_362527600_4599069.png",
"likes": "0",
"downloads": "1",
"plays": "0",
"uid": "2"
},
{
"id": "39",
"title": "11 Mugo Luvudeemos 2",
"name": "274506373_302381815_356651583.mp3",
"art": "1229728795_739090349_686501748.png",
"likes": "0",
"downloads": "1",
"plays": "0",
"uid": "2"
},
{
"id": "40",
"title": "12 Onday Chedo Kud 2",
"name": "378498031_1470949688_1199351944.mp3",
"art": "308978665_1074723934_419931699.png",
"likes": "0",
"downloads": "0",
"plays": "0",
"uid": "2"
},
{
"id": "41",
"title": "13 The Word 2",
"name": "956461611_287666310_43661088.mp3",
"art": "1584112229_240532531_977129308.png",
"likes": "1",
"downloads": "4",
"plays": "0",
"uid": "2"
}
]
}
]
}
as you can see in the above structure, the playlists is listing the tracks associated with it in its own nested array, I'm sure the playlistentries table will need to be used for this but I dont know how to implement it, Sorry for all the code... but thanks for your help!
{for no aparent reason? You do appear to match it with a closing}but there is not reason for either. – RiggsFolly Aug 29 '15 at 21:38