I am developing an application to produce printed reports from a MySQL database. This uses layout files to define what data to retrieve and how to format the output. The data to be retrieved is defined by a select statement which could vary from a simple view to something very complicated. Obviously, validation of the layout requires analysis of the select statement, which is simple under mysqli – prepare the statement then use mysqli_stmt::result_metadata.
The well documented problems of calling mysqli_stmt::bind_params with dynamicly varying parameter counts has prompted me to look at PDO, but there I have the problem that the prepared query must be executed before PDOStatement::getColumnMeta can be used to identify column names. Is there a way to identify prepared select statement column names without executing the statement?
Advertisement
Answer
I guess you want to get the name and data type of each column in your result set, then use that information to help lay out your report.
The most reliable way to do this is to execute() the query. That puts absolute control of the columns and data types in the hands of the person who writes and troubleshoots the SQL in your layout file. I don’t believe there’s a reliable MySQL statement parser you can use in php to dig out aliases and data types from just the SQL.
Both PDO and mysqli require you, the programmer, to execute the query to get the metadata.
If you can organize your report program so it concludes its layout after it fetches a row of the result set, that’s a good way to go.
Or you can execute the query once appending LIMIT 1, do the layout, and execute it again to get the data. But your more complex queries may not benefit much from that attempted optimization. And queries already containing a limit just won’t work in this scheme.