Handle multiple-rows through join mysql tables via PHP -


i have id of album , i'd show tracks of album in json file. album has many tracks , track has many producers (artists).

database structure

database structure

mysql query

select * tracks           left join produced on produced.produced_track = tracks.track_id           left join artists on produced.produced_artist = artists.artist_id  tracks.track_album = 1

result

mysql result

what i'd show via php

php result

i'd create json object different tracks , related artists. problem how handle sql result in order create object.

here sql code per final output use.

create table `tracks` (   `track_id` int(10) not null,   `track_name` varchar(255) not null,   `produced_track` int(10) not null,   `produced_artist` int(10) not null,   `artist_id` int(10) not null,   `artist_name` varchar(255) not null ) engine=innodb default charset=latin1;  insert  `tracks`(`track_id`,`track_name`,`produced_track`,`produced_artist`,`artist_id`,`artist_name`) values (1,'get luckey original  ',1,3,3,'daft prunk'),(1,'get luckey original  ',1,4,4,'pharell'),(2,'touch',1,5,3,'daft prunk'),(2,'touch',1,5,5,'paul');

and php code.

$arr = array(); $rs = mysql_query("select track_id,track_name,group_concat(artist_id) artist_id ,group_concat(artist_name separator 0x1d ) artist_name tracks group track_id"); $i = 0; while($v = mysql_fetch_assoc($rs)){         $arr [$i]['track_id'] = $v['track_id'];         $arr [$i]['name'] = $v['track_name'];         $artist_name_array = explode( chr(29) , $v['artist_name'] );         foreach($artist_id_array = explode(',',$v['artist_id']) $k1 => $v1 ){             $arr [$i]['artists'][] = array ( 'id' => $artist_id_array[$k1] , 'name' => $artist_name_array[$k1] ) ;         }         $i++;  } echo json_encode($arr);exit;

here final output.

[     {         "track_id": "1",         "name": "get luckey original  ",         "artists": [             {                 "id": "3",                 "name": "daft prunk"             },             {                 "id": "4",                 "name": "pharell"             }         ]     },     {         "track_id": "2",         "name": "touch",         "artists": [             {                 "id": "3",                 "name": "daft prunk"             },             {                 "id": "5",                 "name": "paul"             }         ]     } ]

as per join query use 

select track_id,track_name,group_concat(artist_id) artist_id ,group_concat(artist_name separator 0x1d ) artist_name tracks           left join produced on produced.produced_track = tracks.track_id           left join artists on produced.produced_artist = artists.artist_id

Comments

Post a Comment

Popular posts from this blog

Change php variable from jquery value using ajax (same page) -

what i'm trying this: i've php global variable $currentid , simple jquery script check if there "id" (page) in hash of url , if yes wanted give value $currentid . i've been reading lot , notice shall use ajax i'm not managing it. my code is: <script type="text/javascript"> $(document).ready(function(){ if (parent.location.hash != '' ) { var thecurrentid = parent.location.hash.split('#')[1]; $.ajax({ url: 'my.php', //current page type: 'post', data: {theid: thecurrentid }, datatype: 'json', success: function () { } }); } // code here }); </script> could me please? thank in advance. php server side language, meaning once has sent data browser, php variables , data cannot changed... however, using ajax, can send request php page , change data in dom based on result. consider th...
Read more

Pull out data related to my apps from Android Play Store and iOS App Store -

i want pull out data related apps (ratings, reviews, etc) google play store , apple app store ? how can accomplish ? i have found open source apis google mentioned not reliable. also there paid apis (e.g. appfigures) available. has idea how able pull data these play stores ?? i looking direction on how can accomplish this. can suggest ? we (appfigures) have been working on developing infrastructure give ability reviews , bunch of other data points major app stores several years :] we make available via our api though may want check out.
Read more

How can I fetch data from a web server in an android application? -

i want retrieve data web server in android application, , don't know begin. should use web services? i recommend these tutorials: connect android php , mysql , json in android , php , mysqli i used these tutorials , managed trying working without difficulty. between them describe each step in how attempting @ each stage, android application, database , web server side , has information included can process , use received information the thing add connect android php , mysql tutorial makes use of mysql_ in php deprecated. better use mysqli why included third link. the basic outline of want this: 1) in android app make request server php script using class this: import java.io.bufferedreader; import java.io.ioexception; import java.io.inputstream; import java.io.inputstreamreader; import java.io.unsupportedencodingexception; import java.util.list; import org.apache.http.httpentity; import org.apache.http.httpresponse; import org.apache.http.namevaluepair...
Read more