John Davidson

Message:

Here goes. I have a 200-line MySQL query that contains six different Excel formulas in one of the columns, like this:

SELECT '%%%=""Greg''s #""&INDIRECT(""G""&ROW()&""#"")' AS 'Location'

This code snippet will run correctly in MySQL, and because of the doubled-up punctuation, can be exported to a CSV without causing havoc in the CSV. However, now I have to put this query into a prepared statement in PHP as part of the process of automating my company's (you guessed it, Excel-based) reporting. How do I harden the prepared statement against all the doubled punctuation?

I've tried escaping with a \ before each quotation mark, but this somehow causes the MySQL query to return only the Excel formulas, and not the database data that has to go alongside it. Same with heredoc. PDO::quote() didn't even get me that far. I half suspect there's a way to use fputcsv to get around the problem, but I assume I have to prepare the statement before bringing fputcsv into the fray. (Not saying I've done any of these the correct way; I'm trying whatever Google says to try at this point.) So far, no queries have returned in PHP what they do in SQL.

The various data I care about exist in all three formats (and more besides...), and have to be collected as I go downstream, in this case from PHP to MySQL to an emailed CSV to a VBA-automated Excel report. My company thinks of data storage solutions like Pokémon. Gotta subscribe to them all. So this is a problem I can't just dodge.

Thank you, kind people. I'm doing my utmost to get our reporting under control, but this challenge has so far proved un-Googleable (probably because Google doesn't understand "double double quotes" or "paired double quotes").

---

My Answer:
CONCAT('SUM(',IFNULL(column1,0),'+',IFNULL(column2,0),')') AS excel_formula
FROM table_name;