MySQL Graphing

GeForce · 29 Apr 2008 at 21:27

Evening all,

OK, so I have a MySQL database with some data (suprisingly enough), one field of which is a UNIX timestamp. Another of these is "temperature".

Now a record is added to the database automatically every fifteen minutes. Somehow I'd like to automatically pick out data from the database and generate a graph of the temperatures over the last 24 hours. Ie. Time along the x-axis and temperature up the y-axis.

I've had a look at things like JPGraph, but I'm not very experienced with PHP and MySQL (I've done a reasonable amount, but by no means am I an expert). Is this the best way to go about doing this?

The basic concepts of JPGraph I understand, but how would I extract the data from the database into an array that I can then feed into JPGraph? And how do I get time along the x-axis?

Thanks for any help, it really is appreciated!

Jon

growse · 29 Apr 2008 at 23:30

I'd use amcharts myself. Flash based, but looks amazing.

Should be very simple to knock up a php script that reads the last 24 hours worth of data, and chuck it out in a simple xml format. Stick the amcharts flash file on a page, point it's argument at the script, and you're done.

Mark JB · 29 Apr 2008 at 23:53

JPGraph is great (we use it at worK and no icky flash

) and ive found that once you have done one graph you can do them all.

Bascially to get the information out the database with php you need to get to know these mysql functions.

You then generally replace the array in the example code with your returned database results in array format and after a bit of presentation hacking your good to go.

Obviously its better to really understand what's going on and thats great if you have the time so recommend reading the documentation fully, but if not i reckon your on the right path.

GeForce · 30 Apr 2008 at 07:19

Thanks for the replies both of you

For the moment I think I'll carry on trying JPGraph, I've managed to get the data out of the database and create a simple line graph of it.

The bit I'm currently having problems with is how to get the time along the x-axis. The time for each data point is stored in the database in a UNIX Timestamp format. And I'd like to convert this to a "real" time (which I can do in PHP) and then display it along the x-axis.

Jon

EDIT: Done it

I'm sure I'll be back with another question soon!