1

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?

Scrolin
  • 13
  • 4
  • 1
    Please read [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) and [How do I ask a good question](https://stackoverflow.com/help/how-to-ask) and edit your question accordingly. _"However I've hit the usual problems with mysqli_stmt::bind_param"_ - I'm sorry, but I have no idea what that "usual" problem is. What does bind_param have to do with call_user_func_array? Please show us the code you're talking about instead of just explaining it. – M. Eriksson Dec 28 '20 at 12:36
  • The main question is why are you trying to use PHP 5.6 at all? Synology has PHP 7.4 available so you should definitely use that. Don't use PHP 5.6 anymore it has been dead for a few years. – Dharman Dec 28 '20 at 13:46
  • Does this help? https://stackoverflow.com/questions/64293916/dynamic-table-in-html-using-mysql-and-php/64296838#64296838 – Dharman Dec 28 '20 at 13:51

1 Answers1

0

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thanks for the quick response, Whilst I'm not greatly enamored by the 'limit 1' option it seems the only way to go. As for mysqli requiring execute before retrieving the metadata that is not my experience with all queries I've thrown at it so far. – Scrolin Dec 28 '20 at 13:49
  • 1
    But wait: it's impossible that you didn't `execute()` the statement in mysqli because that package also returns the metadata with the result set. The two packages work the same in this respect. – O. Jones Dec 28 '20 at 13:50
  • 1
    From the MySQL documentation "LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. It can also be employed to obtain the types of the result columns within applications that use a MySQL API that makes result set metadata available. With the mysql client program, you can use the --column-type-info option to display result column types." So it's going to be LIMIT 0 – Scrolin Dec 29 '20 at 15:34