MySQL Query To Retrieve Data With Column Names

Question

I am writing some tool which has to retrieve column names of the retrieved data set when any query is applied to it.

If you're familiar with phpMyAdmin, you would realize the SQL pane does what is it runs your query and shows result with the column names. I wonder how hard the query would be, it always results in column names and what's actually the programming behind the scene? Is it like, it analyzes the query and then find table names from it and then first retrieve column names using query show columns from table-name and then the data?

Is there any better way?

UPDATED Sorry for incomplete information, I think I must say I am using MySQL connector for .NET and am using C#.

What language are you writing in? There probably is a pre-made way to retrieve query metadata. — mechanical_meat, Jul 10 '11 at 17:25

score 6 · Answer 1 · answered Jul 10 '11 at 18:04

6

I resolved my issue using following code.

var QueryCommand = new MySqlCommand(txtQuery.Text, Connection);
var ResultReader = QueryCommand.ExecuteReader();

for (var f = 0; f < ResultReader.FieldCount; f++)
{
   ResultGrid.Columns.Add("column" + f, ResultReader.GetName(f));
}

answered Jul 10 '11 at 18:04

Umair A.

6,690
20
83
130

Which is similar to my answer which is for c#. Is there a ResultReader in c# natively? – gbn Jul 10 '11 at 18:06

score 5 · Accepted Answer · edited May 23 '17 at 11:45

5

Your columns are part of the metadate of the DataReader or DataTable that you load in c#

From MSDN for DataTable:

private void PrintColumnNames(DataSet dataSet)
{
    // For each DataTable, print the ColumnName.
    foreach(DataTable table in dataSet.Tables)
    {
        foreach(DataColumn column in table.Columns)
        {
            Console.WriteLine(column.ColumnName);
        }
    }
}

For a DataReader, see Can you get the column names from a SqlDataReader?

edited May 23 '17 at 11:45

Community

1
1

answered Jul 10 '11 at 17:39

gbn

422,506
82
585
676

I will mark your solution as answer because you got more votes than my own solution :) – Umair A. Jun 29 '13 at 21:06

score 2 · Answer 3 · edited Jul 10 '11 at 17:36

you can use SHOW COLUMNS

mysql> SHOW COLUMNS FROM City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+

See: http://dev.mysql.com/doc/refman/5.0/en/show-columns.html

But INFORMATION_SCHEMA is the ANSI means of determining columns, tables, constraints, etc. INFORMATION_SCHEMA.COLUMNS is equivalent to SHOW COLUMNS:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_name = 'tbl_name'
  [AND table_schema = 'db_name']
  [AND column_name LIKE 'wild']

The need is to get them with data itself. – Umair A. Jul 10 '11 at 17:28 — Umair A., Jul 10 '11 at 17:28

Alex Rashkov · Answer 4 · 2011-07-10T17:30:21.840

1

You need a programing language to do same as phpMyAdmin. You can use mysql_fetch_assoc()

It will return a row of data in which each of the elements will have for key the name of the column:

PHP solution

array(
  [0] => array(
    [id] => 1,
    [name] => 'Test name'
  ),
  [1] => array(
    [id] => 2,
    [name] => 'Name 2'
  )
)

edited Jul 10 '11 at 17:30

answered Jul 10 '11 at 17:25

Alex Rashkov

9,833
3
32
58

I am using C# and MySql .NET Connector – Umair A. Jul 10 '11 at 17:25
1

You hadn't specified any language in your question. – Alex Rashkov Jul 10 '11 at 17:29
1

You solution is no doubt helpful from PHP perspective as I didn't realize this either. – Umair A. Jul 10 '11 at 17:32

score 0 · Answer 5 · answered Jul 10 '11 at 18:03

if you have determinated column count you can do it in pure mysql. For example for 3 column:

(select 
max(f1), max(f2), max(f3)
from (
select 
IF(ordinal_position=1,column_name,0) AS f1,
IF(ordinal_position=2,column_name,0) AS f2,
IF(ordinal_position=3,column_name,0) AS f3
from information_schema.columns where table_name='t') tt)
union
(select * from `t`)

MySQL Query To Retrieve Data With Column Names

5 Answers5

Linked