如何在mysql中选择Unknown Number of Columns? [英] How to select Unknown Number of Columns in mysql?

问题描述

鉴于表和某些列的名称，我有以下信息模式选择查询.

Given the table and names of some columns, I have the following Information schema select query.

SELECT `COLUMN_NAME`
                FROM `INFORMATION_SCHEMA`.`COLUMNS`
                    WHERE `TABLE_SCHEMA` = 'm_college'
                    AND `TABLE_NAME` = 'm_fee'
                    AND COLUMN_NAME NOT IN ('id', 'classid', 'semid')

但是此选择不会为我提供我选择的每个未知列的行值.我所得到的只是未知列的名称.是否可以选择行的值，以便在我的php脚本中可以将列作为键并将行作为值对?我需要在其他表中插入列名和行值.请帮忙或建议.

But this select does not give me the rows value for each unknown column I select. All I got is names of the unknown columns. Is it possible to select the values of the rows so that I can have column as key and rows as value pair in my php script? I need to insert the column names and row values in other table. Please help or suggest.

推荐答案

下面是显示相交表的快速尝试.

Below is a very quick attempt at showing an intersect table.

这使您拥有固定的结构并即时添加费用类型.

This allows you to have a fixed structure and add fee types on the fly.

CREATE TABLE IF NOT EXISTS `mz_fee222` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `classid` int(11) NOT NULL,
  `semid` int(11) NOT NULL,
  `batch` year(4) NOT NULL,
  `session` int(11) NOT NULL
);

create table fee_type
(   fee_id int auto_increment primary key,
    description varchar(100)
);
insert fee_type (description) values ('exam'),('marksheet'),('admitcard'),('centre'),('practical'); -- etc as you go

create table mz_fee_intersect
(   -- here is where the fee amount is stored. Flexible.
    id int auto_increment primary key,
    mz_id int not null, -- this is the id from my_fee222 table
    fee_id int not null, -- this is the fee_id from fee_type table
    fee int not null, -- here is the actual fee, like 100 bucks
    -- FK's below (not shown):

    -- 
    unique key (mz_id,fee_id) -- forbids duplicates
);

这篇关于如何在mysql中选择Unknown Number of Columns?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！