arrays - PHP: help with this logic -

i have problem. have table of support tickets (wh_task). each task has date_completed (d/m/y) , has_met_sla field (0 or -1). want allow user search table date_completed , display chart based on results.

the charts data has can populate barchart (fusion charts):

year: 2010 | month: nov | sla met: 12 | sla missed: 2

year: 2010 | month: oct | sla met: 15 | sla missed: 1

the chart have numbers x-axis , "nov 2010" along y. each month along y has 2 columns, met , not met.

so, can create kind of chart no problem it's generating data i'm having trouble coming with. below query:

        $tsql = "select task_id, has_met_service_level_agreement, date_completed ".                 "from wh_task ".                 "where (task_status_id = 5) , (account_id =$atid)";          $stmt = sqlsrv_query( $conn, $tsql);         if( $stmt === false)         {                  echo "error in query preparation/execution.\n";                  die( print_r( sqlsrv_errors(), true));         }           //sla counters         $met = 0;         $missed = 0;            /* retrieve each row associative array , display results.*/         while( $row = sqlsrv_fetch_array( $stmt, sqlsrv_fetch_assoc))         {             $date = $row['date_completed'];             $monthnumber = date_format($date, "n");             $year = date_format($date, "y");             $hasmetsla = $row['has_met_service_level_agreement'];          }       } 

can give me hand logic here? i'm guessing need store data in array containing month, year, met total, , not met total. each task check if year month combination exist in array , if ammend totals based on $hasmetsla , if not add array??

thanks all!

jonesy

here's if had in php (using sql better option, here's 1 method of doing post-facto):

first, i'd setup multi-dimensional array following structure:

array(     'year1' => array(         'month1' => array(             'met' => 0,             'missed' => 0,         ),     ), ), 

then, i'd change while loop this:

$yearinfo = array(); while( $row = sqlsrv_fetch_array( $stmt, sqlsrv_fetch_assoc)) {     $date = $row['date_completed'];     $monthnumber = date_format($date, "n");     $year = date_format($date, "y");     $hasmetsla = $row['has_met_service_level_agreement'];     if (!isset($yearinfo[$year])) {         $yearinfo[$year] = array(             $monthnumber => array(                 'met' => 0,                  'missed' => 0             )         );     } elseif (!isset($yearinfo[$year][$monthnumber])) {         $yearinfo[$year][$monthnumber] = array(             'met' => 0,             'missed' => 0,         );     }     $key = $hasmetsla ? 'met' : 'missed';     $yearinfo[$year][$monthnumber][$key]++; } 

then, when display:

$data = ''; foreach ($yearinfo $year => $months) {     foreach ($months $month => $status) {         $data .= 'year: '.$year.' | '.               'month: '.$month.' | '.               'sla met: '.$status['met'].' | '.               'sla missed: '.$status['missed']."\n";     } }