Creating 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 create reports with this data using a PHP script. You can specify the field names from these tables in the drop-down list so that the user can say: " first_nameequal to John." Fine. But what if you want these field names to be a little readable? For example, I would like the user to select "Name of First Pet" as the field instead of " first_pet_name". I definitely do not want to store this information in the markup, since we can often add and delete tables. What is the easiest way to remove this?

Answer 1

I would do something simple, for example, replacing the underscore with spaces, and the top with the first letter.

Answer 2

You can create a table to bind text information to table + column pairs. Say you have table users and you want to show the column name "First Favorite Name" instead of first_name.

Say (table name ColumnTextInformations):

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

This way you have unique identifiers for column labels.

Using it is very simple:

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

Answer 3

.

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)
);

- , " " mysql ( USE ); .

, , DBA .

, MySQL , , .

: varchar 64, , MySQL . , , , , - _. Columns. - , , ( , , " " ), .

Answer 4

- . , , , SQL ..

SQL- ( CREATE TABLE, ).

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 ( ) .

. " ".

Answer 5

I'm not saying this is a good idea, but technically - technically - MySQL will allow you to create tables and columns that include spaces if you surround these identifiers with reverse outputs 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 heard of column aliases?

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