I'm currently writing an AppleScript to trigger a MySQL script to run in order to send a csv every day at a certain time using a calendar event.
I believe I have the general idea on both parts correct. This is a version of the MySQL script (which works when run directly in MySQL):
SET @sql_text := CONCAT('SELECT "ID", "FirstName", "LastName", "Employer", "Activist/Lead"
SELECT * FROM
(SELECT e.id, m.firstname, m.lastname, e.employer, (CASE WHEN m.Lead="X" THEN "Lead" ELSE "Activist" END) AS "Activist/Lead"
FROM events e
LEFT JOIN people m ON e.id = m.id
WHERE ((STR_TO_DATE(e.startdate,"%m/%d/%Y") >= SUBDATE(now(), INTERVAL 1 YEAR)) AND (e.eventtype IN("House Party","Blitz","Politics"))
GROUP BY e.id
HAVING count(e.id)>=1) t
INTO OUTFILE \'/Users/Ryan/Activists_', DATE_FORMAT(NOW(), '%Y%m%d'), '.csv\'
FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\'
LINES TERMINATED BY \'\\n\'
PREPARE s1 FROM @sql_text;
DROP PREPARE s1;
Expected “"” but found unknown token.
I just stopped trying to post the script directly into AppleScript and used Terminal to grab my script
activists.sql directly from its text file by using this:
do script with command "mysql 'database' < activist.sql" within AppleScript after telling it to open Terminal.