RE: [PHP-DB] sql output to a multidimensional array

> -----Original Message----- > From: Chris [mailto:dmagick@gmail.com] > Sent: Thursday, September 07, 2006 9:52 PM > To: K.A.Bouton > Cc: php-db@lists.php.net > Subject: Re: [PHP-DB] sql output to a multidimensional array > > > K.A.Bouton wrote: > > I need the output of my sql to be a multidimensional array > as follows. > > chart [ 'chart_data' ] =3D array ( array ( "", "2001", > "2002", "2003", > > "2004" ), > > array ( "AAA", 0, > 10, 30, > > 63 ), > > array ( "BBB", 100, > 20, 65, > > 55 ), > > array ( "CCC", 56, > 21, 0, > > 90 ) > > ); > > I have tried and am unable so far to get this out of my database. > > Any suggestions? > > "SELECT count( publications.title) AS title_number, > publications.year, > > publications.affiliation=20 FROM publications GROUP BY > affiliation, year > > ORDER BY year"; > > > > There is some "for loop" I am not getting, and am also not > getting the > > = zero counts with the count(*). > > > You won't be able to get an sql query to return in that > format (I know > what you're trying to do, I've used the same chart software). > > You won't get zero counts for data that doesn't exist, you'll need to > generate your series before hand: > > <?php > for ($i = 2000; $i < 2006; $i++) { > $data[$i] = 0; > } > > ?> > > then later on override that value. Thanks - but it's a dynamic dataset and I didn't want to hard code dates in. I managed to do it by first doing a query on the years, then doing a count query based on the year from the query above, and if no year was avaiable count was 0. Seems to work.