Ryan Webb Ryan Webb - 11 months ago 46
MySQL Question

Automatically save data to file at certain time from web page

I have a webpage and I am getting data from a database. I was wondering if there was a way that at a specific time (say 6:00pm) I can automatically run a SQL query and save the data in a file on the site, without any user interaction.

I would use this to display data in a graph.

I am using phpMyAdmin for my database, and am familiar with PHP, MySQLi, javascript and jQuery.

I appreciate that this may be a bit of a weird question.

EDIT: The server is hosted on linux

Answer Source

If you are on linux it is very common to use cron for such a task. Simply run

[vim|vi|nano|whatever] crontab -e

And create the task. You will need the PHP script to get the data and ensure you can execute it like

php [/path/to/script/]script.php

Then set your schedule and it will work

Crontab examples http://www.thegeekstuff.com/2009/06/15-practical-crontab-examples

To save data to a file you could take the result (if using SQL most likely an array) you could do something like

$result = $PDO->query($Query)->fetchAll(PDO::FETCH_ASSOC);
file_put_contents([file].json, json_encode($result, JSON_PRETTY_PRINT);

This will make a json file that will have the query result. Your javascript can pull it or you can serve it with PHP like an API. This will also overwrite the old data every time it runs.