MySQL query to retrieve data with column names

Question

I am writing some tool that should retrieve the column names of the dataset obtained by querying it.

If you are familiar with phpMyAdmin, you will realize that the SQL panel does what it launches your query and shows the result with column names. I wonder how complicated the query will be, it always leads to column names and what is actually programming behind the scenes? It looks like he parses the query, and then finds the table names from it, and then first extracts the column names using the query show columns from table-name, and then the data?

Is there a better way?

UPDATED Sorry for the incomplete information, I think I should say that I use the MySQL connector for .NET and use C #.

Answer 1

Your columns are part of the DataReader or DataTable metadata that you load in C #

From MSDN to 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 DataReader, see. Can you get column names from SqlDataReader?

Answer 2

I solved the problem using the 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));
}

Answer 3

, , phpMyAdmin. mysql_fetch_assoc()

, :

PHP-

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

Answer 4

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 an ANSI tool for defining 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']

Answer 5

if you determined the number of columns you can do it in pure mysql. For example, for 3 columns:

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