input / store/ query graph data in mysql

WebHelper Joined: 15 Jan 2004 Posts: 91

Hi all,

I have been having some problems with thinking of the most efficient way to store graph data in a mysql database.

I want to store one graph data with the x access = (0h to 23h) and the y axes = (0 to 9). I want the user to input:
(whatever eg 3) for 0
(whatever eg 4) for 1
(whatever eg 9) for 2
(whatever eg -) for -
(whatever eg 7) for 23

This data is to be linked to the person and date (eg bob gave the score 7 at 23:00hrs on Sunday the 23 June)

I want to query the graph data and plot a trend (of a specific value say 7) over say the last 12 months and display the data in graph form.

I can think of ways to implement this, but the database structure that I have thought of does not lead to an efficient database.

It is the time factor that I am having problems with. I can not decide if I should encode the graph data "in a string and store it in the database" and have some algorithm to plot the data on the graph or store the entire graph with data in its entirety. The latter option would probably be easier for the database structure, but would provide lots of duplicated data.

Could some point me in the direction of a similar database structure, or a database that acts in this way.

I would be very grateful, Thanks in advance,

Dominic

Posted: Sat Jul 11, 2015 8:40 pm (8 years, 9 months ago)

I am Joy new to this forum I really need a help,

I have a mysql table

Field Type
match_id, int10
match_date, date
match_time, time
match_round, int10
home_team, varchar50
away_team, varchar50
prediction, varchar50
home_score, int3
away_score, int3
form, varchar50

and I am trying to retrieve data as the query mentioned below,

PHP Code: