Reputation: 23738
Grouping Array result set based on same key values
i have the following array after querying into the database
$soundcloud_uploads=array(
array('release_id'=>33,'song_id'=>1,'artist_name'=>'omer aslam'),
array('release_id'=>33,'song_id'=>2,'artist_name'=>'omer aslam'),
array('release_id'=>34,'song_id'=>1,'artist_name'=>'Robbie')
);
what i am trying to do is to convert the array into something like following based on the same value for release_id.
$soundcloud_uploads=array(
array(
array('release_id'=>33,'song_id'=>1,'artist_name'=>'omer aslam'),
array('release_id'=>33,'song_id'=>2,'artist_name'=>'omer aslam'),
),
array('release_id'=>34,'song_id'=>1,'artist_name'=>'Robbie')
);
currently i have the following code using the foreach loop which somehow achieves the same thing that i want
$soundcloud_uploads = array(
array('release_id' => 33, 'song_id' => 1, 'artist_name' => 'omer aslam'),
array('release_id' => 33, 'song_id' => 2, 'artist_name' => 'omer aslam'),
array('release_id' => 34, 'song_id' => 1, 'artist_name' => 'Robbie')
);
$convert = array();
foreach ($soundcloud_uploads as $row) {
$convert[$row['release_id']][] = $row;
}
which gives the following output
Array
(
[33] => Array
(
[0] => Array
(
[release_id] => 33
[song_id] => 1
[artist_name] => omer aslam
)
[1] => Array
(
[release_id] => 33
[song_id] => 2
[artist_name] => omer aslam
)
)
[34] => Array
(
[0] => Array
(
[release_id] => 34
[song_id] => 1
[artist_name] => Robbie
)
)
)
but how can i make it more efficient and optimized as the result rest is going to be big and using this approach if i have 1000 records, i will have to iterate 2000 times once for arranging them into the desired format and then sending /uploading them in form of playlists to soundcloud.
EDIT : Describing the DB schema and the whole scenario why i could not accomplish it via mysql query
i am working on a site where users can upload and distribute their music to stores in form of releases / albums. Those uploaded and approved releases could then be sent / uploaded to soundcloud from a form where they could select to upload either all tracks inside a specific release as a playlist or each song as a separate track.
When are the selected songs uploaded
- They can choose current date to upload it right away by when they hit save.
OR
- They can choose a date in future to schedule the upload.
The selected tracks details are saved inside a table named
soundcloud_upload which saves every selected track detail against their release_id, which means if i have 5 tracks inside a release and select to upload all of them, on hitting save every track detail would be saved as a separate record, schema for the table is added below.
The first part is simple and straight and implemented, the second scheduling part is where i am now and involves a cron job which will run every day and selected all those records from the soundcloud_upload table joining with other tables for the detail where upload_date matches the current date and the upload_type is scheduled.
The query and tables schemas i am using is as follows
SELECT `sc`.*, `rp`.*, `l`.`Name` AS `LabelName`,
`l`.`ContactName`, `l`.`ContactEmail`, `l`.`soundcloudUsername`,
`l`.`soundcloudPassword`, `l`.`youtubeUsername`, `l`.`youtubePassword`,
`mg`.`Name` AS `MainGenre`, `g`.`Genre`, `u`.`Username` FROM
`soundcloud_upload` AS `sc`
LEFT JOIN `ReleaseProd` AS `rp` ON sc.ReleaseId=rp.ReleaseId AND rp.Processed IS NOT NULL
LEFT JOIN `Label` AS `l` ON l.LabelId=rp.LabelId
LEFT JOIN `MainGenre` AS `mg` ON mg.MainGenreId=rp.MainGenreId
LEFT JOIN `Genre` AS `g` ON g.GenreId=rp.GenreId
LEFT JOIN `Users` AS `u` ON u.UserId=rp.UserId WHERE (sc.uploadDate='2016-12-10') AND
(l.soundcloudUsername IS NOT NULL) AND (l.soundcloudPassword IS NOT NULL)
AND (sc.upload_type='scheduled') AND
(sc.IsUploaded=0 OR sc.IsUploaded=2)
The schema for the tables necessary to determine if i can achieve the desired format of the result set are as below.
soundcloud_upload
+----------------+-----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-----------------------------+------+-----+---------+-------+
| ReleaseId | int(11) | NO | | NULL | |
| SongId | int(11) | NO | MUL | NULL | |
| isClip | tinyint(1) | NO | | 0 | |
| trackName | varchar(100) | NO | | NULL | |
| trackBio | varchar(3000) | YES | | NULL | |
| category | varchar(20) | YES | | NULL | |
| tags | varchar(3000) | YES | | NULL | |
| IsPlaylist | tinyint(1) | NO | | 0 | |
| isPublic | tinyint(1) | NO | | 0 | |
| isDownloadable | tinyint(1) | NO | | 0 | |
| PlaylistID | int(11) | YES | | NULL | |
| TrackID | int(11) | YES | | NULL | |
| purchaseUrl | varchar(500) | NO | | NULL | |
| uploadDate | date | NO | | NULL | |
| createDate | date | NO | | NULL | |
| IsUploaded | tinyint(1) | NO | | 0 | |
| clipStart | varchar(10) | NO | | NULL | |
| clipEnd | varchar(10) | NO | | NULL | |
| error_logs | text | YES | | NULL | |
| upload_type | enum('instant','scheduled') | NO | | instant | |
+----------------+-----------------------------+------+-----+---------+-------+
Releases
+----------------------------+-------------------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+-------------------------------+------+-----+-------------------+----------------+
| ReleaseId | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | NO | | NULL | |
| UserId | int(11) unsigned | NO | MUL | NULL | |
| rerelease | tinyint(1) | YES | | NULL | |
| ReleaseNotes | mediumtext | YES | | NULL | |
| releaseDescription | varchar(3000) | YES | | NULL | |
| Catalog | varchar(20) | NO | | NULL | |
| CoverFile | varchar(200) | YES | | NULL | |
| AddDate | timestamp | NO | | CURRENT_TIMESTAMP | |
| ReleaseDate | date | YES | | NULL | |
| Howlong | int(11) | YES | | NULL | |
| EffectiveDate | date | YES | | NULL | |
| iTunesPreOrderDate | date | YES | | NULL | |
| SoundcloudDate | date | YES | | NULL | |
| VariousArtists | char(1) | YES | | NULL | |
| ArtistId | int(11) | NO | MUL | -1 | |
| Composer | varchar(255) | YES | | NULL | |
| MainGenreId | int(11) | YES | MUL | NULL | |
| GenreId | int(11) | YES | MUL | NULL | |
| Language | varchar(3) | NO | | en | |
| SubgenreId | int(11) | YES | | NULL | |
| UPCCode | varchar(13) | YES | UNI | NULL | |
| ReleaseType | int(11) | YES | | NULL | |
| TracksQty | tinyint(4) | YES | | NULL | |
| PhysicalRelease | char(1) | YES | | NULL | |
| LabelId | int(11) | NO | MUL | NULL | |
Labels
+---------------------------+------------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+------------------+------+-----+---------------------+-----------------------------+
| LabelId | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | UNI | NULL | |
| ContactName | varchar(50) | NO | | NULL | |
| ContactEmail | varchar(100) | NO | | NULL | |
| secondaryEmail | varchar(255) | NO | | NULL | |
| dob | date | NO | | NULL | |
| ISRCCode | char(3) | YES | | NULL | |
| Biography | text | YES | | NULL | |
| CatalogBeginning | int(3) | NO | | 0 | |
| CatalogPrefix | varchar(3) | NO | | NULL | |
| AddressLine1 | varchar(50) | YES | | NULL | |
| AddressLine2 | varchar(50) | YES | | NULL | |
| City | varchar(30) | YES | | NULL | |
| State | varchar(20) | YES | | NULL | |
| Language | varchar(3) | NO | | en | |
| CountryID | int(11) | NO | MUL | NULL | |
| Juno | varchar(255) | YES | | NULL | |
| TID | varchar(255) | YES | | NULL | |
| Beatport | varchar(255) | YES | | NULL | |
| mainGenreId | int(11) | NO | | NULL | |
| subGenreId | int(11) | NO | | NULL | |
| Hearaboutus | varchar(80) | NO | | NULL | |
| HearaboutusFriendName | varchar(255) | NO | | NULL | |
| HearaboutusFriendEmail | varchar(255) | NO | | NULL | |
| HearaboutusOthers | varchar(255) | NO | | NULL | |
| PromoBanner | varchar(255) | YES | | NULL | |
| Option4 | tinyint(1) | YES | | NULL | |
| package_plan | tinyint(1) | YES | | NULL | |
| royalty_percentage | float | NO | | NULL | |
| Threshold | float | YES | | 200 | |
| Tagging | tinyint(1) | YES | | NULL | |
| LogoFile | varchar(100) | YES | | NULL | |
| PhoneNumber | varchar(20) | YES | | NULL | |
| SalesRate | decimal(10,4) | YES | | NULL | |
| CurrencyId | int(11) | YES | MUL | NULL | |
| UserId | int(11) | NO | | NULL | |
| LastChangedBy | int(11) | NO | | NULL | |
| DateAdded | date | YES | | NULL | |
| DateExpired | date | NO | | NULL | |
| paypal_time | timestamp | NO | | 0000-00-00 00:00:00 | on update CURRENT_TIMESTAMP |
| isViewed | tinyint(1) | NO | | 1 | |
| Active | tinyint(1) | NO | | 1 | |
| agreement | varchar(255) | NO | | NULL | |
| manual_agreement | enum('Y','N') | NO | | N | |
| Signatures | text | NO | | NULL | |
| ein | varchar(128) | NO | | NULL | |
| Neighbor | tinyint(4) | NO | | NULL | |
| nbr_country | int(11) | NO | | NULL | |
+---------------------------+------------------+------+-----+---------------------+-----------------------------+
Upvotes: 1
Views: 88
Answers (3)
Reputation: 1
yes you can do this by function
example
$soundcloud_uploads=array(
array('release_id'=>33,'song_id'=>1,'artist_name'=>'omer aslam'),
array('release_id'=>33,'song_id'=>2,'artist_name'=>'omer aslam'),
array('release_id'=>34,'song_id'=>1,'artist_name'=>'Robbie')
);
$soundcloud=array();
$release_id_array=array();
foreach($soundcloud_uploads as $sound)
{
if($sound->release_id=='33')
{
$bbb=array();
$bbb['release_id']=$sound->release_id;
$bbb['song_id']=$sound->song_id;
$bbb['artist_name']=$sound->artist_name;
array_push($release_id_array,$bbb);
}
}
//finally push array to main array
array_push($soundcloud,$release_id_array);
$release_id_array=array();
foreach($soundcloud_uploads as $sound)
{
if($sound->release_id=='34')
{
$bbb=array();
$bbb['release_id']=$sound->release_id;
$bbb['song_id']=$sound->song_id;
$bbb['artist_name']=$sound->artist_name;
array_push($release_id_array,$bbb);
}
}
//finally push array to main array
array_push($soundcloud,$release_id_array);
Upvotes: 0
Reputation: 620
I think you have no critical problem if you have 1000 records. A loop of 2000 times is not a big matter. But you have a problem if you manage 1 000 000 or more records because the PHP process will grow too big (and will take time, yes).
In my opinion, when you have many data rows to deal with, you have to change your logic from memory to database or file, to avoid managing too much information in memory.
Is there a way you change your query to the database, to get, either
- The list of playlists
- The content of a given playlist ?
Updated :
According to your DB schema, the table soundcloud_upload seems (a guess!) to contain both Playlists and Playlist contents, and the difference between the two is known by the attribute IsPlaylist.
Is this is true, to get the list of Playlists you will have to introduce a where clause looking like this : WHERE sc.IsPlaylist = 1
To get the content of a given playlist, you will have to know its playlist Id and to select with a where clause looking like this :
WHERE sc.IsPlaylist = 0 and sc.PlaylistId = '$playListId'
Upvotes: 1
Reputation: 153
I think to assign it to assosiative array is already good approach for what you want to do, because its does not matter how many you go through loop if the process or the code logic become complex it will be the same.
Example : your code using 2000 loop to complete the job that you handling
somehow you managed to recode it and just using 1000 loop to complete the job with validation handling or assign new variable
The two kind or process will take time with no much different because of the complexity is different
Sometimes its better to do more loop rather than focusing in how to make the code do less loop.
I do facing this problem before, then do benchmark between two code, the result is not much different
Hope this help you
Good luck
Upvotes: 1
Related Questions
- Grouping data while looping by one value and push another value into the group as a subarray value
- Group rows of a 2d arrays by a column value and push whole rows into subarrays
- Group values of a flat array by the corresponding values of another flat array
- Transpose a SimpleXMLElement object containing arrays
- Group an array based on common value
- how to group an array by multiple keys in PHP
- From an array, group items based on the key value with PHP
- How to group an array based on inner array keys in PHP?
- php group array together based on key value being the same
- Group PHP arrays by key value