My Scenario:
I have a question regarding exporting data. I have a script in PHP running a complex query that takes some time to return the data.
When I get the data I presented to the user and they have the ability to export if they desired to.
My logic
To avoid rerun the query when users export the data I set a Session variable with the data returned by the query and if the user click in export the scripts builds the excel with the data set in a session variable.
My Problem
Since the data is store in a session variable if the user runs a different report all the data hold by the session var will be only the last one they ran.
That's the case in drill down on the same report.
My Questions
1- How others developer handle this situation ?
2- They just rerun the query one the user click on export data ? (Doesn't matter takes double the time)
3- Set Different session variable one for each report ? (If you have 100 users and 20 reports then you will have 2,000 session variable in case user runs all the reports)
Thank you for your comments.
You can make use of PHP query caching to reduce the cost of the second run of the query.
https://blog.engineyard.com/2015/query-caching-with-php-mysqlnd