Sorting multi-dimensional arrays of different length

[Big_Pat]

First of all, hi! I'm so glad to have found this forum. I'm a keen php writer in the early-to-mid stage of coding, and I'm quite used to searching online when stumped, but this one...well, I'm not sure what the search term would be, so I'm hoping a kind soul here will be able to help.

 

My problem is this. I've got a MySQL database of my music. Stupidly, I put the times in as strings as opposed to time values. If I call "select albumartist, time from the_table" and assign $albumartist from row[0] and $time from row[1] I'll have two arrays of the same length. How, though, do I add up the times from $time individually by albumartist and keep some sort of relationship between the two?

 

Imagine:

$albumartist will have, say, 1000 entries but of course only 100 unique entries, and undoubtedly less. (imagine an album has 10 songs, and there are ten albums) 

$time will have 1000 entries originally, but after totalling each album_artist's time totals together it will have far fewer.

 

How do I associate the two together to produce the final result of:

 

SomeArtist 1: 2hrs 55:20

SomeArtist2: 2hrs 37:29

.

.

etc.

 

Thanks in anticipation!

 

 

[requinix]

Sounds like your database is set up in a weird way. You're not putting times on the individual tracks? I don't know your needs but that's probably the best place to put them (if you do record tracks, otherwise albums are the next step up). If you want to get the total time for a thing then you do a query that totals it up for you.

 

As for the totaling I'd store the times in seconds. Fractions of a second, if you want that much detail. From there it's very easy to "reformat" the seconds into HH:MM:SS.

[Big_Pat]

No, the database is fine (apart from the fact I've got times as strings). I've got one for title, one for artist, one for album, etc, and one for time. There is a total of 14 fields, actually. 

[requinix]

Since we both agree the problem is you're storing times as strings, how about converting them into something that isn't a string? Like a number of seconds.

Edited April 26, 2013 by requinix

[The Little Guy]

I did something similar to this, what I came up with was something like this to fix my problem:

-- I would make end_time the length of the track which could look like this: '00:02:30' for a 2 minute 30 second song.
select sum(time_to_sec(timediff(end_time, '00:00:00'))) / 60 / 60 as hours from my_table group by artist_id;

So, maybe store the values as "times"

 

Then, using php I did this:

<?php
function lz($num){
    return (strlen($num) < 2) ? "0{$num}" : $num;
}

function convertTime($dec){
    // start by converting to seconds
    $seconds = $dec * 3600;
    // we're given hours, so let's get those the easy way
    $hours   = floor($dec);
    // since we've "calculated" hours, let's remove them from the seconds variable
    $seconds -= $hours * 3600;
    // calculate minutes left
    $minutes = floor($seconds / 60);
    // remove those from seconds as well
    $seconds -= $minutes * 60;
    // return the time formatted HH:MM
    return $hours . "h " . lz($minutes) . "m";
}

echo convertTime($row["hours"]);

I hope maybe this might get you on the correct path...

Edited April 26, 2013 by The Little Guy

[Big_Pat]

Thanks, tLG, I'll go and work with that. You're very kind.

[Big_Pat]

Cracked it, thanks to your help. The key was the query, remembering the format of the time string was 00:00.

 

 

select distinct artist,

                      sum(left(length, 2)),

                      sum(right(length, 2))

                      from songlist

                      group by artist

                      order by (sum(left(length, 2)))

                      desc

 

And then from there I converted it all to seconds just as you did and then from there to hours, mins and secs. 

Thanks a lot!