Architecture questions

[eluser]nydeveloper[/eluser]
I'm using CodeIgniter to develop what is a fairly basic site, but have some doubts about the best way to architect the desired solution. Essentially what I'm doing is requesting data from various API URLs, parsing that data, and then storing it accordingly in my local DB. I've already created the code to accomplish this. I first query a table which contains a different API URL and some other info that I need in each row. Once the above is complete for row 1, I move on to row 2 and so forth until I've exhausted each row in the table.

My next step, which I haven't done yet, is to display this data which I've now stored in my local DB on the home page in a table of sorts. Here's where it gets tricky. This data, retrieved from hundreds of API URLs, changes quite often - sometimes by the minute. My first question is - how do I keep my data which I'm displaying somewhat up to date. Do I run a cron job which calls my retrieve/store function described in the first paragraph of this post every minute? This seems rather resource intensive I may not get me the desired result anyway. Do I skip the part where I store the data in my local DB and just parse the APIs and display the results on my home page with each page load (i.e., on the fly)? The latter approach does not strike me as being realistic, but perhaps I'm wrong there.

Any guidance here would be greatly appreciated. Thanks in advance for your time!

[eluser]t'mo[/eluser]
Hard to tell without knowing more about what the API's are and how often their data changes.

You mention some change frequently, even within minutes. What about the others? Daily? Monthly? Perhaps your data retrieval function could be made a little bit "smarter" - e.g., store how often a particular API should be re-fetched, and when the (overall) data retrieval process is triggered (maybe every minute), it only calls part of the API's.

What other options do the API's offer? Is there a way any of them can notify you when to refresh your data (webhook or something similar)?

I know this isn't really a specific answer to your question, but hopefully I've given you some ideas of things to consider.

[eluser]nydeveloper[/eluser]
Thanks for the reply. I should have been more detailed in my original post, but the values being retrieved from these APIs all change very often - they are similar to stock quotes.

Therefore what I need to determine is the best way to ensure my values are as close to up to date as possible. I could run my retrieve/parse/store function constantly (via cron?), and I suppose this would meet my needs to a certain extent.

But should I actually be storing this data in local DB after parsing? Or should a page load on my home page trigger the retrieve/parse function from the various APIs and just display those values directly after parse - rather than retrieving the values from my local DB with each page load?

Hope this makes sense.... Thanks again.

[eluser]InsiteFX[/eluser]
The best solution to your problem would be to use JavaScript to update the page or use jQuery.

[eluser]nydeveloper[/eluser]

The best solution to your problem would be to use JavaScript to update the page or use jQuery.

I'm familiar with jQuery, but to be clear... are you recommending that I continue to use PHP/CI to retrieve/parse/store values from the APIs mentioned above, and then utilize jQuery to display them on my home page (retrieving the data to be displayed from my local DB)?

[eluser]boltsabre[/eluser]

are you recommending that I continue to use PHP/CI to retrieve/parse/store values from the APIs mentioned above, and then utilize jQuery ....

Without trying to steal InsiteFX's thunder, it sounds like a good solution. Let your backend handle all the API stuff, then use jQuery to retrieve the "last_update_timestamp" from your DB for that particular API, if new grab it and push it onto the page.

One thing that springs to mind, is how often you'd do this? Once every 15 seconds, 30, 60, 5 minutes? Could be quiet a DB strain if you've got 100's of API's to check (as you mentioned) and lots of users, you'd really want to do a LOT of load testing.

Possible solutions is to run a number of slave servers and spread the load, or maybe has a new table called perhaps "api_last_updated" with a unique api key and the last update timestamp, whenever you get new api data you update this table as well, then you only have to query this small table to see if that particular api has new data.

All speculation, there are many ways to skin a cat, and without having full knowledge of your project, db setup, server capabilities, traffic levels, etc it's all mere speculation.

Good luck!