+- Hack Forums (https://hackforums.net)
+-- Forum: Tech (https://hackforums.net/forumdisplay.php?fid=444)
+--- Forum: Coding (https://hackforums.net/forumdisplay.php?fid=151)
+---- Forum: Web Development (https://hackforums.net/forumdisplay.php?fid=183)
+---- Thread: [PHP] How to structure data for charting (/showthread.php?tid=5780572)
How to structure data for charting - FaberCastell - Jan 6, 2018
So i'm just wondering what's the best model to structure the data which is later used for drawing graphs.
Say for drawing the graph of prices for stocks and if you have several different stocks.
Do you use one table with structure like;
stockName
date
price
And then you store records(a lot of them) for all stocks in the same table?
Or how do you approach such problem? Say that you later use Highcharts or some similar software to chart.
RE: How to structure data for charting - Rushax - Jan 6, 2018
I would say it really depends.
For e.g if you can use a web service that uses cURL to grab the data from another site, that might be a less resource-heavy method.
Then just cURL them across and use within the graph.
I would recommend http://www.chartjs.org/samples/latest/ as i think they look awesome and have great features for changing axis etc.
And I guess another way of storing could be in a PHP file with spaces inbetween? However I probably wouldn't recommend this.
If you were to go the DB route, yes I would store as you have. If you don't need the data after X amount of time, run a cron-job to delete or something.
RE: How to structure data for charting - AppleJuice - Jan 6, 2018
(Jan 6, 2018 03:02 PM)Rushax Wrote: And I guess another way of storing could be in a PHP file with spaces inbetween? However I probably wouldn't recommend this.
I'd have to disagree, there'd have to be a theoretical limit before it has a negative impact. You'll be using a lot more resources as your file gets larger. Use a database, they're built for this type of workload. It'd be a lot more efficient than loading a giant file. As well, when used properly the database engine can cache data so you're not overloading the server.
But yea, using a flat-file could be ideal if you have a lot of users, by having a background service calculating and saving data temporarily to a file, that way each user does not have to make the database work hard for the same data.
(Jan 6, 2018 03:02 PM)Rushax Wrote: For e.g if you can use a web service that uses cURL to grab the data from another site, that might be a less resource-heavy method.
Then just cURL them across and use within the graph.
cURL could be a way to do it, but I'd recommend you caching data somehow. You wouldn't want to be an ass and overload another service's API backend, they could limit or restrict you. Plus, if they have limits OP might not be able to fetch all data, so he'd probably have to store that somehow if he wanted historical data.
(Jan 6, 2018 02:35 PM)FaberCastell Wrote: And then you store records(a lot of them) for all stocks in the same table?
OP, I would recommend you storing each stock in one table. It'd be a lot easier to implement new stocks in the future. For drawing the data to a graph, a simple query of fetching all sorted rows between a date range, and then (depending on the graph library of course) you'd have to specify the stock name (as a new line) and have its x-axis set to the timestamp and it's y-value set to the stock price.
I HIGHLY recommend you to look into a Graph Database. In my previous experience, I used MySQL for graphing data, and with a database size well over 30GB, it was pretty hard to scale and was brutally slow. I don't really think MySQL is best for time-series data. You'd be able to structure the database as a time to each stock and their price.