Whats the easiest way to make a dynamic line graph from a mysql database?

So what I'm doing is storing data from a website every 4hrs. I want to have a line graph of the last two days, the y-axis would be number of players and that value can be anywhere from 0-30,000, the value is dependent开发者_如何学C on the scrape of the website.

What is the best way to store the data in mysql and where is a easy to use graphing solution?

Has anyone used Raphaël?

Hey, Google Charts is exactly what you're looking for. It can create any type of chart from a data set, and is very customizable.

As for the actual data retrieving, the answers above will help you. :)

You could have a table structure like

Player_Stats
players int
hour int

Then each hour you could write something like:

insert into Player_stats (players, hour) values(NUMBEROFPLAYERS, HOUR#);

Where HOUR# is a value from 1 to X number of possible hours (if you want to only store things in a running log, otherwise, change hour to a timestamp)....the insert would be more like

insert into Player_Stats (players, timestamp) values(NUMBEROFPLAYERS, NOW());

Then you'd retreive your data with:

select players, hour from Player_Stats;

Or if you kept things in perpetuity and wanted to grab a range from now to 2 days ago:

select players, timestamp from Player_Stats where timestamp between now() and date_sub(timestamp, interval 2 day);

Then you could use a charting library like Google Visualizations...they have good documentation on formatting the data specifically for their different charts.

I've done something similar. I stored the number of players along with a time stamp in a table, then used jquery and jqplot to display the data.

I would set up a cron to run a SELECT TO OUTFILE myFile statement regularly. Note that myFile cannot be an existing file for security purposes (docs), so you'd have to have the cron also delete the file after the plot is created.

I have found ploticus to be very easy to work with, and can make some very complex plots without too much difficulty.