PDA

View Full Version : Help with grid (cube)



loopb
20 Aug 2009, 6:44 AM
http://img39.imageshack.us/img39/1760/hshp.jpg
I have a 2 tables. One With Classes (items) and the second one with classes "day prices"

I want to do a Grid (cube?) (editable) with:
- X headers date - MSH_PRICES_DATE
- Y header class name - MSH_CLASSES_NAME
- and XY values = MSH_PRICES_PRICE

can someone help me with that ;/

21 Aug 2009, 7:29 AM
you first need to figure out how to get the stuff from the SQL query to JSON from a HTTP request.

loopb
22 Aug 2009, 2:23 AM
Yep, that's true
but it's posible?

I want to generate x,y headers of grid Dynamic like this:

http://img228.imageshack.us/img228/2818/priceo.jpg

Condor
22 Aug 2009, 2:30 AM
Can your database transform a table? That would be the easiest solution.

Otherwise you will have to transform the data either on the server or on the client (in javascript).

loopb
22 Aug 2009, 5:32 AM
no I use mysql 5.0 and inodb tables.
i think there is a wy to do Transform, Pivot(?) or Crosstab(?) in mysql
but my knowledge about it is insignificant

ouh and i use php5&mysql5&extjs3

// i think it could look like this http://www.smartclient.com/#basicCube

so i think I could select values or create a view

SELECT p.MSH_CLASSES_NAME, o.MSH_PRICES_DATE, o.MSH_PRICES_PRICE
FROM MSH_CLASSES p, MSH_PRICES o
WHERE o.MSH_PRICES_MSH_CLASSES_ID = p.MSH_CLASSES_ID;

and use then AdoDB Pivot statement http://phplens.com/adodb/pivot.tables.html, could I?

Condor
22 Aug 2009, 8:07 AM
Do you really want to create the overhead of using ADO instead of a direct MSSQL connection?
Maybe you should transform the data in PHP before you send it to the client.

loopb
22 Aug 2009, 9:57 AM
now I don't know :-?
:((

whats the best way?
directly from mysql create a statement to create w pivot(?)?
i realy don'u use it so i dont know how to create one

pass the data by json to ext?
create grid and view the data?

loopb
30 Aug 2009, 10:21 AM
ok, i can get headers like this



SELECT GROUP_CONCAT(DISTINCT
CONCAT('\nCOUNT(CASE WHEN c.MSH_PRICES_DATE= "',
c.MSH_PRICES_DATE, '" THEN id ELSE NULL END) AS
', c.MSH_PRICES_DATE)) AS column_list
FROM MSH_PRICES c;
i will get something like this



COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-16" THEN id ELSE NULL END) AS
2009-08-16,
COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-17" THEN id ELSE NULL END) AS
2009-08-17,
COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-18" THEN id ELSE NULL END) AS
2009-08-18,
COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-19" THEN id ELSE NULL END) AS
2009-08-19,
COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-20" THEN id ELSE NULL END) AS
2009-08-20
i can use it to my SQL pivot

soo



SELECT o.MSH_CLASSES_NAME

{ column_list }

FROM MSH_CLASSES o
WHERE o.MSH_CLASSES_ID = c.MSH_PRICES_MSH_CLASSES_ID
GROUP BY o.MSH_CLASSES_NAME
how to use { column_list } ?

do i think the right way?

Condor
30 Aug 2009, 9:50 PM
It might be faster to do the counting in PHP, but your SQL will indeed work.

Since the number of columns and their names are now variable you will need to send metaData along with the JSON data to the client (see JsonReader API docs for details).

loopb
31 Aug 2009, 12:39 PM
I must work wwith my sql

it works



SELECT (SELECT o.MSH_CLASSES_NAME FROM MSH_CLASSES o WHERE o.MSH_CLASSES_ID = c.MSH_PRICES_MSH_CLASSES_ID) AS CLASSES,
MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-16" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-16",
MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-17" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-17",
MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-18" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-18",
MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-19" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-19",
MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-20" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-20"
FROM MSH_PRICES c
GROUP BY c.MSH_PRICES_MSH_CLASSES_ID
LIMIT 0, 30;
i get from it

http://img177.imageshack.us/img177/4893/classes.jpg

But I have my



SELECT GROUP_CONCAT(DISTINCT CONCAT('\nCOUNT(CASE WHEN c.MSH_PRICES_DATE= "',c.MSH_PRICES_DATE, '" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS', c.MSH_PRICES_DATE)) AS column_list FROM MSH_PRICES c;
but how i can use it (->column_list) in my first sql? i have tried and i have errors in phpmyadmin ;/
can help someone?(:|

this dosn't work i gues i have to use cursor but how can i do this?



SELECT (
SELECT o.MSH_CLASSES_NAME
FROM MSH_CLASSES o
WHERE o.MSH_CLASSES_ID = c.MSH_PRICES_MSH_CLASSES_ID
) AS CLASSES,(
SELECT GROUP_CONCAT(DISTINCT CONCAT(
'MAX(CASE WHEN c.MSH_PRICES_DATE =
"',c.MSH_PRICES_DATE,'"
THEN c.MSH_PRICES_PRICE ELSE NULL END) AS
"',c.MSH_PRICES_DATE,'"'))
)
FROM MSH_PRICES c
GROUP BY c.MSH_PRICES_MSH_CLASSES_ID
LIMIT 0, 30;


from this i get:

http://i27.tinypic.com/34978k3.jpg