I'd like to run a SQL query via cron. I figured I'd create a .sql file and call it from a script that I place in the cron folder. The query is relatively large and not written by me, so I'd prefer to not have to re-create it in PHP.
Can anyone provide some assistance? I am not exactly clear on how to run the SQL query from the script. Thanks in advance.
EDIT: Just for some background info, the query is intended to grab data from multiple tables and organize it in a csv file.
Each database has a command line client tool
For MySQL, see man mysql
comand to add in cron something like this
cat /dir/query.sql|mysql database -u user -ppass >result
For PHP you can use file IO to read query, after that use string variable instead of the query in your requests.
result file had the man mysql page in it. Something is not working properly.
mysql database -u user -ppass </dir/query.sql >result work as well? :)
Jul 26, 2011 at 6:50
The solution I ended up using was to put the query in a "here document". This bash script was then implemented with cron. For more information, check out this link: http://snippets.dzone.com/posts/show/3078
And just in case that link goes dead, here's a snippet of the sample code:
#! /usr/bin/env bash
# execute some bash scripting commands here
mysql mydatabase <<SQL_ENTRY_TAG_1
SELECT *
FROM mytable
WHERE somecondition='somevalue';
SQL_ENTRY_TAG_1
# execute other bash scripting commands here
mysql mydatabase <<SQL_ENTRY_TAG_2
SELECT *
FROM myothertable
WHERE someothercondition='someothervalue';
SQL_ENTRY_TAG_2