Populating PHP array from MySQL for use by Amcharts -

i'm trying set simple amcharts graphs company intranet webpage. on last 2 weeks have created html/js , produced nice graphic using amcharts (data hard-coded in html demo purposes). installed xampp , created mysql database, populated tables , data gets imported csv files.

so far, working fine - can display nice graphics , can collect data supplying data graphs. however, have been approaching problem 2 ends (getting source data database , presenting data in graph on webpage). need join these 2 ends, can feed amcharts data mysql.

i know need use php data mysql , put array can used amcharts php knowledge basic , i'm struggling code.

what have php code connects mysql , extracts data display in browser - works. don't know how data multi-dimensional array in format amcharts needs plotting graph.

it great if guys give me here , fill in missing pieces. have pseudo code logic of creating array basis 'real' php code.

this pseudo code populating array:

;charset=utf-8', '', '', array(pdo::attr_emulate_prepares => false, pdo::attr_errmode => pdo::errmode_exception));

$stmt = $db->query("select * <mytable> <mycondition>");  $prevweek = "9999"; $headrowdone = 0;  while($row = $stmt->fetch(pdo::fetch_assoc)) {   if ($prevweek < $row['weeknumber']) {       // new week write out category x label ('week') week number of following data rows       chartdata.push($datarow);       $headrowdone = 0;   }    if (!$headrowdone) {       $datarow = "week: "+$row['weeknumber'];       $headrowdone = 1;   }   // write out x-axis category value , y-axis value   $datarow = $datarow+$row['xaxiscategory']+": "+$row['yaxiscategory'];   $prevweek = $row['weeknumber']; } chartdata.push($datarow); ?> 

the sql table looks like:

create table ( weeknumber varchar(4), xaxiscategory varchar(50), yaxisvalue integer );

and has data like: '1301','a',10 '1301','b',20 '1301','c',24 '1302','a',11 '1302','b',22 '1302','c',27 '1303','a',14 '1303','b',23 '1303','c',28 ...etc

the data array amcharts needs like:

var chartdata = [{ week: "1301", a: 10, b: 20, c: 24 }, { week: "1302", a: 11, b: 22, c: 27 }, { week: "1303", a: 14, b: 23, c: 28 ....etc }];

// spoofing fetch via pdo $rows [] = array('weeknumber'=>1301, 'a'=>10); $rows [] = array('weeknumber'=>1301, 'b'=>20);  $rows [] = array('weeknumber'=>1301, 'c'=>25);  $rows [] = array('weeknumber'=>1302, 'a'=>12); $rows [] = array('weeknumber'=>1302, 'b'=>22);  $rows [] = array('weeknumber'=>1302, 'c'=>27); //var_dump($rows);  // set vars $lastweek = ''; $ctr = 0; $data = array();   // loop thru vars, build array foreach( $rows $row){  if($row['weeknumber'] !== $lastweek){   $ctr++;   $data[$ctr] = array('week'=>$row['weeknumber']);   $lastweek= $row['weeknumber']; } // nicer, if( isset($row['a']) ) $data[$ctr]['a'] = $row['a']; if( isset($row['b']) ) $data[$ctr]['b'] = $row['b']; if( isset($row['c']) ) $data[$ctr]['c'] = $row['c'];  }  var_dump($data); 

then use json_encode() format want.

this answer bit kludgy, @ least gets away building strings make json.