Generating reports from MySQL tables

Question

Let's say you have a bunch of MySQL tables, and you want your end users to be able to generate reports with that data with a PHP script. You can present the field names from those tables in a dropdown, so a user might be able to say, "first_name equals John." Great. But what if you want those field names to be a little more readable? For instance, I'd like the user to be able select "Name of First Pet" as a field, instead of "first_pet_name." I definitely don't want to store that information in the markup, as we might be adding and removing tables pretty frequently. What's the simplest way to pull this off?

Answer 1

I'd do something simple like replacing underscores with spaces, and upper casing the first letter.

Answer 2

You can create a table, to bind text informations to table+column pairs. Let's say you have a table users, and you want to show column name 'Name Of First Pet' instead of *first_name*.

Let's say (table name ColumnTextInformations):

ParentTable | ParentColumn | Text
      users |   first_name | Name of First Pet
...

So you have unique identifiers for your column labels.

Using it it's very easy:

SELECT Text 
FROM ColumnTextInformations
WHERE parentTable = ? AND parentColumn = ?

Answer 3

I'd store it in the database.

CREATE TABLE human_labels (
    schema  varchar(64) not null,
    table   varchar(64) not null,
    column  varchar(64) not null,
    label   tinytext    not null,
    primary key (schema, table, column)
);

Where schema is what you commonly call "database" in mysql (what goes after USE when you switch databases); and table and column are pretty obvious.

Of course, you'll have to make sure the DBA updates that whenever changing the schema.

I believe MySQL allows a comment on a table, but not a column, or you could use that.

Edit: Changed the varchar to 64 because that's what the MySQL manual documents as the max size. Also, it turns out you can put a comment on each column if you wish — and you can read those back from information_schema.columns. But I'd still do it the way shown above as its more flexible (you can put additional data in there easily, such as your "should I show this field" flag) and also allows comments to be used for their intended purpose.

Answer 4

The simplest solution is to use delimited identifiers. This allows you to spell table names and column names containing whitespace, punctuation and special characters, SQL keywords, etc.

You have to delimit the table names and column names in SQL queries (including the CREATE TABLE statements used to define them).

CREATE TABLE `User Facts` (
  `User Name`         VARCHAR(100)
  ...
  `Name of First Pet` VARCHAR(100)
  ...
);

SELECT `Name of First Pet`
FROM `User Facts`
WHERE `User Name` = 'Bill';

MySQL uses back-ticks (as shown above) for delimited identifiers by default.

See "Do different databases use different name quote" for more details.

Answer 5

I'm not saying this is a good idea, but technically -- technically -- MySQL will let you create tables and columns that include spaces if you surround those identifiers with backticks throughout your code. For example:

mysql> create table `This is a Table` (`First Name` varchar(50),
    `Name of First Pet` varchar(20));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into `This is a Table` values ('Tom', 'Skippy');
Query OK, 1 row affected (0.01 sec)

mysql> select * from `This is a Table`;
+------------+-------------------+
| First Name | Name of First Pet |
+------------+-------------------+
| Tom        | Skippy            |
+------------+-------------------+
1 row in set (0.00 sec)

Answer 6

Ever heared of column aliases ?

SELECT pet AS Name of First Pet, user_name AS Name of User FROM users WHERE user = 'Bill';