Hello, I am a php developer. I want to know how I can code a template that will be sold on CodeCanyon with running an sql query every day once at midnight. I know how to create cronjobs that points to a php script that runs the sql commands. But I don't want the user to do that every time they want to install the script on their server. I want it to be easy. Is there a way I can schedule sql commands? I also tried events but you need a dedicated server to enable the scheduler. I also am thinking of running the php exec command to run a terminal command that will set up the cronjob when the script is installed. I don't know how other developers are doing it. Any ideas?
The easiest way, that still isn't easy is to have code on the page that runs each time a page is viewed. My strategy would be embed a 1x1 transparent image on the page - called something like cron-image.php when a page is viewed cron-image.php runs and checks the filemtime of cron-image.gif if the time is over 24 hours the image is regenerated and the scheduled tasks script is run cron-image.gif is returned with appropriate headers This approach won't slow down the loading of the page and if it fails it doesn't stop the page from being usable.
You can automate the process by creating a setup wizard in your script that automatically sets up the cronjob for the user. also, you can use a third-party service like Heroku Scheduler or AWS Lambda to schedule the SQL query execution. Make sure to include detailed documentation for users on how to set up the automatic scheduling.