php to display the table; column 1 with the comments of the column mysql and the results of the line of column two transposed

advertisements

Apologies for the perhaps poor title - It is best to display my goal visually:

I want something like this to show on the page when user goes to SomeURL.com/test.php?id=101

| Questions      | Answers       |
----------------------------------
| 1(a) First Name| Pedro         |
----------------------------------
| 1(b) Surname   | Millers       |
----------------------------------
| 2(a) Weight    | 150lbs        |
----------------------------------

This will be acheived by queries to a mysql database. Firstly:

SELECT * FROM Questionnaire WHERE idQuestionnaire=101;

Which returns:

| idQuestionnaire | FirstName    | Surname    | Weight    |
-----------------------------------------------------------
| 101             | Pedro        | Millers    | 150lbs    |

In my table settings I have Column comments set. i.e. For the Column "FirstName" the comments read "1(a) First Name". To retreive all of these comments I can do another query:

SELECT COLUMN_NAMES FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Questionnaire';

Which returns:

| COLUMN_COMMENTS |
-------------------
| 1(a) First Name |
-------------------
| 1(b) Surname    |
-------------------
| 2(a) Weight     |
-------------------

The reason I want to automate the code with arrays/loops rather than hard code is because the actual table I am using has over 400 fields, and fields may be modified or added so I would like not to have to keep changing the php code as well when this happens. It seems like a pretty simple task but can't for the life of me find relevant documented solutions.


I'd recommend you use the mysqli fetch_fields() function on the result set, to get information about the columns contained in the resultset, rather than querying views in INFORMATION_SCHEMA.

The fetch_fields() will work for queries that reference only a subset of columns in a table, or queries that return expressions, or return columns from more than one table.

http://php.net/manual/en/mysqli.quickstart.metadata.php