Q:

output mysql query to a shell script

Hi guys I have the following mysql query that I need to output to a table for emailing to clients hands off!. I only need to format the results to a table.
 select division_name,
     count(*) "Total Cases on CTS",
     sum(if(Date(Filing_Date) = DATE_SUB(curdate(), INTERVAL 1 DAY), 1,0)) "entered yesterday",
     sum(if(DATE(Filing_date) = curdate(), 1, 0)) 'entered today',
     sum(if(YEAR(filing_date) = YEAR(curdate()) AND Month(filing_date) = Month(curdate()), 1,0)) "entered this month",
     sum(if(YEAR(filing_date) = YEAR(DATE_SUB(curdate(), INTERVAL 1 MONTH)) AND Month(filing_date) = Month(DATE_SUB(curdate(), INTERVAL 1 MONTH)), 1,0)) as entered_last_month
     from cases2v
     where court_rank_name like 'High Court'
          OR court_rank_name like 'Magistrate Court'
     group by division_name";

I have tried outputting the query to a csv file then using ssconvert in linux (Centos 7) to convert to excel and emailing with postfix, the output file doesn't look good.. it lacks table headers.
I was thinking using a shell script to output the query to an array then loop through the results to create a table would be better and quicker but I don't know how! this is what I have tried
dbquery=$(mysql -u user -ppass -e "use db_name; select division_name, count(*) "Total Cases on CTS", sum(if(Date(Filing_Date) = DATE_SUB(curdate(), INTERVAL 1 DAY), 1,0)) "entered yesterday", sum(if(DATE(Filing_date) = curdate(), 1, 0)) 'entered today', sum(if(YEAR(filing_date) = YEAR(curdate()) AND Month(filing_date) = Month(curdate()), 1,0))  "entered this month", sum(if(YEAR(filing_date) = YEAR(DATE_SUB(curdate(), INTERVAL 1 MONTH)) AND Month(filing_date) = Month(DATE_SUB(curdate(), INTERVAL 1 MONTH)), 1,0)) as entered_last_month from cases2v  where court_rank_name like 'High Court' OR court_rank_name like 'Magistrate Court' group by division_name;")

array=($(for i in $dbquery; do echo $i; done )) 

A:

You might try adding the column headers to the result using a union. Also, be mindful of your quotes. I use backticks for columns and tables, and single quotes for strings, just to be clear and avoid confusion. You had a couple of places where you had double quotes inside of double quotes
SELECT 
    'division_name' AS `division_name`,
    'Total Cases on CTS' AS `Total Cases on CTS`,
    'entered yesterday' as `entered yesterday`,
    'entered today' AS `entered today`,
    'entered this month' AS `entered this month`,
    'entered_last_month' AS `entered_last_month`
UNION
SELECT 
    `division_name`,
    count(*) AS `Total Cases on CTS`,
    sum(if(Date(Filing_Date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY), 1,0))  AS `entered yesterday`,
    sum(if(DATE(Filing_date) = CURDATE(), 1, 0)) AS `entered today`,
    sum(if(YEAR(filing_date) = YEAR(CURDATE()) AND Month(filing_date) = Month(CURDATE()), 1,0)) AS `entered this month`,
    sum(if(YEAR(filing_date) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND Month(filing_date) = Month(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), 1,0)) as `entered_last_month`
FROM cases2v
WHERE court_rank_name LIKE 'High Court'
    OR court_rank_name LIKE 'Magistrate Court'
GROUP BY division_name";

