Run SQL query from cron

Question

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.

Answer 1

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.

Answer 2

You could try writing a bash script to execute the .sql file and redirect the output.

Answer 3

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