mysql选择字段生成报表,从MySQL表生成报表

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?

解决方案

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.