RE: [PHP-DB] sql output to a multidimensional array
-Original Message- From: Chris [mailto:[EMAIL PROTECTED] 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. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] sql output to a multidimensional array
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(*). -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
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. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
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. then Chris wrote: 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). This is my MSSQL Server solution, and yep, it outputs EXACTLY like requested. I'm not sufficiently fluent with MySQL, so this may have some dialect translation that needs done. Also, this IS NOT a scalable solution at all. It assumes you know the years you want the information for, and requires that you specify the years in two specific areas of the query. Some SQL engines have a cross tab function that will make this a little more automatic (MSSQL is one such engine), but they are typically extensions to the SQL spec. and are specific to each engine (i.e. the MSSQL implementation would not work for MySQL). -- Mitch --- SQL BEGINS HERE - -- Table of products CREATE TABLE #Products ( Product CHAR(3) NOT NULL ) INSERT #Products(Product) VALUES('AAA') INSERT #Products(Product) VALUES('BBB') INSERT #Products(Product) VALUES('CCC') -- Sales history table. CREATE TABLE #Sales ( Product CHAR(3) NOT NULL , Quantity INT NOT NULL , SaleYear INT NOT NULL ) -- Product AAA, Intentionally left out 2003 INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 4, 2001) INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 3, 2001) INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 4, 2001) INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 2, 2002) INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 8, 2002) INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 5, 2002) INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 7, 2004) INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 5, 2004) INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 6, 2004) -- Product BBB, Intentionally left out 2002 INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 3, 2001) INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 2, 2001) INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 5, 2001) INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 2, 2003) INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 7, 2003) INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 2, 2003) INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 7, 2004) INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 1, 2004) INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 8, 2004) -- Product CCC, Intentionally left out 2001 INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 2, 2002) INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 9, 2002) INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 2, 2002) INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 9, 2003) INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 3, 2003) INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 7, 2003) INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 2, 2004) INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 1, 2004) INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 5, 2004) -- Now for the totally UNscalable query. SELECTp.Product, COALESCE(y1.TotalQuan, 0) AS Quan2001, COALESCE(y2.TotalQuan, 0) AS Quan2002, COALESCE(y3.TotalQuan, 0) AS Quan2003, COALESCE(y4.TotalQuan, 0) AS Quan2004 FROM #Products AS p FULL OUTER JOIN ( SELECT Product, Sum(Quantity) AS TotalQuan FROM #Sales WHERE SaleYear = 2001 GROUP BY Product ) AS y1 ON y1.Product = p.Product FULL OUTER JOIN ( SELECT Product, Sum(Quantity) AS TotalQuan FROM #Sales WHERE SaleYear = 2002 GROUP BY Product ) AS y2 ON y2.Product = p.Product FULL OUTER JOIN ( SELECT Product, Sum(Quantity) AS TotalQuan FROM #Sales WHERE SaleYear = 2003 GROUP BY Product ) AS y3 ON y3.Product = p.Product FULL OUTER JOIN ( SELECT Product, Sum(Quantity) AS TotalQuan FROM #Sales WHERE SaleYear = 2004 GROUP BY Product ) AS y4 ON y4.Product = p.Product -- Cleanup DROP TABLE #Sales DROP TABLE #Products -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql output to a multidimensional array
Mitch Miller wrote: K.A.Bouton wrote: I need the output of my sql to be a multidimensional array as follows. then Chris wrote: 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). This is my MSSQL Server solution, and yep, it outputs EXACTLY like requested. I'm not sufficiently fluent with MySQL, so this may have some dialect translation that needs done. Also, this IS NOT a scalable solution at all. It assumes you know the years you want the information for, and requires that you specify the years in two specific areas of the query. Some SQL engines have a cross tab function that will make this a little more automatic (MSSQL is one such engine), but they are typically extensions to the SQL spec. and are specific to each engine (i.e. the MSSQL implementation would not work for MySQL). I stand corrected :) mysql should support something like that but yeh it's not a great solution (ie it's a horrible query) ;) -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php