How do I store daily data to MySQL and show it in graph?
I would like to store daily data to Mysql and retrive it to show in a graph at the end of each month.
for example:
employee name is John: I would like to store his daily time sheets and work schedule to MySQL and then it should retrieve and displayed in graph format at end of each month.
I know the graph part but I don't know how to stor开发者_如何学运维e data daly in Johns name and call it .
any help??
(This is all basic suggestions, based on the limited information provided.)
Within MySQL you would probably be looking at using the GROUP BY
statement (Tutorial on "GROUP BY") using something like the following:
SELECT
( SUM( UNIX_TIMESTAMP( `workEnd` )
- UNIX_TIMESTAMP( `workStart` )
) / 3600 ) AS `hoursWorked` ,
DATE_FORMAT( `workStart` , "%Y-%m" ) AS `yearAndMonth`
FROM
`yourTableName`
WHERE
`employeeName`="John"
GROUP BY
`yearAndMonth`
ORDER BY
`yearAndMonth` DESC
This will return data somewhat like the following:
hoursWorked | yearAndMonth
--------------------------
22.0000 | 2010-02
15.2500 | 2010-01
From that data, you can then fill a graphing package, like Google Charts (Charts API) to represent that information in a graphic form.
(I am aware that this is not an exhaustive answer, but the hopes are to give you a few pointers on where to start looking, so you can start finding your own solution and then return to StackOverflow with more specific questions when parts of your solution are difficult.)
精彩评论