Grouping Array result set based on same key values

Question

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                |                             |

+---------------------------+------------------+------+-----+---------------------+-----------------------------+

Your code is good enough!

phpio.net
– phpio.net

2016-12-09 09:32:45 +00:00
Commented Dec 9, 2016 at 9:32 — phpio.net
– phpio.net, Commented Dec 9, 2016 at 9:32

Evinn · Accepted Answer · 2016-12-09 09:05:58Z

1

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

answered Dec 9, 2016 at 9:05

Evinn

1531 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Muhammad Omer Aslam Over a year ago

yeah you are right i was curious while implementing it that am i doing it the right way or not.

b2vincent · Accepted Answer · 2016-12-12 10:45:08Z

1

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'

edited Dec 12, 2016 at 10:45

answered Dec 9, 2016 at 9:32

b2vincent

6207 silver badges15 bronze badges

3 Comments

Muhammad Omer Aslam Over a year ago

i strongly agree, and i wanted to do it by mysql query too,please see my question i edited it and added more detail to describe my DB schema and the reason why i could not do it via query.

b2vincent Over a year ago

OK, I updated my answer according to my understanding of your DB schema.

Muhammad Omer Aslam Over a year ago

table soundcloud_upload is for tracking all the songs that ARE or WILL BE uploaded to soundcloud and the TrackId and PlaylistId inside the table soundcloud_upload is actually the Ids assigned by soundcloud when the track is uploaded there or the playlist is created on soundcloud . Yes to get the list of those songs that were selected to be uploaded as a single playlist on soundcloud, i would have to add the check IsPlaylist=1

Muhammad Omer Aslam · Accepted Answer · 2018-02-23 00:24:59Z

0

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);

edited Feb 23, 2018 at 0:24

Muhammad Omer Aslam

23.9k9 gold badges47 silver badges69 bronze badges

answered Dec 9, 2016 at 9:18

Manohar Verma

11 bronze badge

1 Comment

Muhammad Omer Aslam Over a year ago

what was asked is if the given approach would have any performance effect , didnt asked for how to do it

Collectives™ on Stack Overflow

Grouping Array result set based on same key values

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related