sql output to a multidimensional array
am 07.09.2006 11:22:19 von k.a.bouton
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: sql output to a multidimensional array
am 07.09.2006 22:51:44 von Chris
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:
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: sql output to a multidimensional array
am 07.09.2006 22:56:05 von Mitch Miller
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.
SELECT p.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: sql output to a multidimensional array
am 07.09.2006 23:09:36 von Mitch Miller
Yeah ... it ain't purdy, and it sure doesn't scale so you have to be
careful where you use it.
This is usually more of a presentation issue that I'd suggest be left up
to the application doing the display of the data.
As an example, I believe Microsoft Excel has a crosstab function that
can query right from a database and do the 3D effect automagically.
If I wanted to do this regularly, I think I'd be inclined to write a job
that finds the number of columns (or rows) and then runs a separate
query for each of the respective rows (or columns). I was thinking
through an HTML example, where it's difficult to add columns to a table
on the fly, but having an "unknown" number of rows is no problem. I
think I'd query the db to find how many columns there are (and the
respective headings, in the example given, year numbers). Then, start a
loop querying for each product and their respective annual sales and
output each row.
Using this technique, you end up with a lot more queries back and forth
to the server, but as an application, it could automatically scale in
both width and height automatically.
-- Mitch
Chris wrote:
> 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) ;)
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: sql output to a multidimensional array
am 08.09.2006 10:47:25 von k.a.bouton
> -----Original Message-----
> From: Chris [mailto:dmagick@gmail.com]=20
> 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
>=20
>=20
> K.A.Bouton wrote:
> > I need the output of my sql to be a multidimensional array=20
> as follows.
> > chart [ 'chart_data' ] =3D3D array ( array ( "", "2001",=20
> "2002", "2003",
> > "2004" ),
> > array ( "AAA", 0, =20
> 10, 30,
> > 63 ),
> > array ( "BBB", 100, =20
> 20, 65,
> > 55 ),
> > array ( "CCC", 56, =20
> 21, 0,
> > 90 )
> > );
> > I have tried and am unable so far to get this out of my database.
> > Any suggestions?=20
> > "SELECT count( publications.title) AS title_number,=20
> publications.year,
> > publications.affiliation=3D20 FROM publications GROUP BY=20
> affiliation, year
> > ORDER BY year";=20
> >=20
> > There is some "for loop" I am not getting, and am also not=20
> getting the=20
> > =3D zero counts with the count(*).
>=20
>=20
> You won't be able to get an sql query to return in that=20
> format (I know=20
> what you're trying to do, I've used the same chart software).
>=20
> You won't get zero counts for data that doesn't exist, you'll need to=20
> generate your series before hand:
>=20
>
> for ($i =3D 2000; $i < 2006; $i++) {
> $data[$i] =3D 0;
> }
>=20
> ?>
>=20
> 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