php - Ordering and grouping in a complex MySQL query -

i'm needing complex set of mysql queries generate data suitable plotting in d3 database of labels assigned different musical artists. rows in relevant table (called "lastfm_annotations") are: user_id, artist_id, tag_id, , tag_month (i.e. we've recorded many instances of particular user labeling particular artist particular tag @ particular time). embedded in php script.

what need generate in end json object contains, dates in given range, number of times each unique tag given artist used in month (including counts of 0 dates in given tag not used.

this have far (assume $itemid , artist_id interchangeable here):

$cal = array(); $result = mysql_query("select date calendar;"); // gets possible dates, of course changed different date range if (!$result) {     echo 'could not run query: ' . mysql_error();     exit; }  ($i = 0; $i < mysql_num_rows($result);  $i++) {     $date =mysql_fetch_row($result)[0];     $result2 = mysql_query("select t2.tag_id, case when t1.freq null 0 else t1.freq end freq (select distinct tag_id lastfm_annotations artist_id='" . $itemid . "') t2 left join (select tag_id, count(*) freq lastfm_annotations artist_id='" . $itemid . "' , tag_month='" . $date . "'   group tag_id) t1 on t2.tag_id = t1.tag_id group t2.tag_id");         $current = array();     $current['date'] = $date;     ($j = 0; $j < mysql_num_rows($result2); $j++) {         $row = mysql_fetch_row($result2);         $tag = $row[0];         $freq = $row[1];         $result3 = mysql_query("select tag_name lastfm_taglist tag_id ='" . $tag . "' limit 1;");         $tagname = mysql_fetch_row($result3)[0];         $current[$tagname] = $freq;     }     array_push($data, $current); } echo json_encode($data);  

(edit: big query follows)

select t2.tag_id      , case           when t1.freq null 0           else t1.freq         end freq            (select distinct tag_id            lastfm_annotations           artist_id='$itemid') t2         left join         (select tag_id, count(*) freq            lastfm_annotations           artist_id='$itemid'             , tag_month='$date'          group tag_id) t1         on t2.tag_id = t1.tag_id   group t2.tag_id 

(end edit.)

this works, has (at least) 2 big problems can't quite figure out. first, in big ugly sql query, i'm doing redundant work calling

(select distinct tag_id lastfm_annotations artist_id='" . $itemid . "') 

every time go through loop, though value same every time. idea how can around that? maybe it's possible somehow save unique tag_ids php array, , insert query?

second, need make sure tags ordered overall frequency (i.e. across time, not within particular month), i'm not sure how that. can proper sequence using query like:

select tag_id, count(*) freq lastfm_annotations item_id=xxx order freq desc 

but need ensure each query in loop returns tags in same order. ideas? perhaps better handle sorting in d3 when start plotting data, preferable if data started off in right order when make sql call.

sorry big question, , help!

here's query executed once each artist, rather each artist-month combination. solves first problem joining subqueries on artist_id , filtering on artist_id = $itemid in clause. db engine should push condition down subqueries when processes query, won't inefficient might seem, , since isn't called inside month loop should less work overall.

the second problem addressed getting overall frequency first subquery, , ordering entire result set frequency in descending order. put most-tagged months first.

the drawback of months no tags not represented in results. can around in app logic (for example, keeping track of months in date range haven't been seen each tag , synthesizing '0' rows). should possible expand query include missing months, due complexity won't details on unless you're interested.

select t1.tag_id      , t2.tag_month      , t2.freq month_freq      , t1.freq total_freq   (select tag_id              , artist_id              , count(*) freq           lastfm_annotations          group tag_id, artist_id) t1        inner join        (select tag_id              , tag_month              , artist_id              , count(*) freq           lastfm_annotations           group tag_id, tag_month, artist_id) t2        on t1.artist_id = t2.artist_id , t1.tag_id = t2.tag_id  t2.tag_month between '$daterangestart' , '$daterangeend'    , t1.artist_id = '$itemid'  order total_freq desc, t1.tag_id