How do I get the row name from a database to add to output.

Jeff
Jeff used Ask the Experts™
on
I am currently using 2 querys, one to get column names and one to get data from a mysql database.
How can I make this into on query?

$sql = "SHOW COLUMNS FROM $table_name";

$sql = "SELECT * FROM $table_name";
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL does not define something like the "Row name".  Yes, we have column names but no row names like in Excel sheets. Rows are identified by the data in these rows and you may select just certain rows by adding WHERE clause to your query.

The most obvious way how to identify rows in SQL data is to define one column as the Primary key which has unique values across the whole table.  Primary key can be an integer number which is optionally generated by SQL engine as autoincremented value (see https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html). You may use almost any data type for Primary key.

You may also generate Row numbers by appropriate function but these numbers cannot identify the rows because the value depends on the final output order. More about row numbers in MySQL: http://www.mysqltutorial.org/mysql-row_number/

Some SQL dialects define elements which we could handle as the Row name. The closest one is the ROWID used in Oracle (https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm).

To use the query which returns column names does not have frequent usage obviously. If you need the data in your application then you know column names in advance obviously. Also SELECT * FROM table is not used frequently because the application needs columns of known names and not all table columns obviously.  Of course, I can imagine some universal data manipulation tools which would need these commands to investigate the data structures first.

If you really need to mark each data row with the name then add one character column to your table and store the name inside this column. Then it may appear on the output as Row name.
Distinguished Expert 2017

Commented:
What language are you using to extract data they all include an option through a single select that includes the column names in the header of the response.
Are you working with php?
After running the execute SQL, the response data can be accessed as an array or as a hash ref, the index/elements are the column names while the values are the data from those columns.
The other option since you know the columns of interest to your query, explicitly specify them in the select column1, column2 from tablename


additional detail on what you are using, and what is it you are trying to do. It seems unnecessary to run the two queries as you have.

Author

Commented:
Is it possible to edit the question. I meant to put "column" and not "row" in the title.

Sorry I wasn't more specific. I am using PHP and MySQL.
I have a customer that wants the data from db tables dumped into an xls spreadsheet. What I am trying to achieve is simplify my dump data into csv code. It needs to be simple to reuse (change the 1 query).
I have the working code and wanted to know if it is possible to combine the 2 querys into 1.

I would like to query for Column Names as well as all data

SELECT ColumnNames, * FROM TableName;

FirstName, LastName, Email
Jim, Jones, jim2yahoo.com
Steve, Miller, steve@gmail.com
etc...

Right now I query for the ColumnNames Then for the row data.
Distinguished Expert 2017

Commented:
you can convert the data into any format you want.
I do not understand where you are running into issues. when you select * from tablename
you get the answer as a table that includes the the column headings.
Are you working of a premise that you know tables but have no idea what the columns are ?

What does your PHP look like?
http://php.net/manual/en/book.mysqli.php
The below is the information you want
http://php.net/manual/en/mysqli-result.fetch-fields.php

you run the single query
....
Try running the query only and use the tools available to get the other data.....?
It seems you would need something like the following query. Table test1 has two columns named col1 and col2 and these names appear in the first output row. You cannot use SELECT * but you have to cast all columns to CHAR data type.:
SELECT CAST(MAX(CASE ordinal_position WHEN 1 THEN column_name ELSE NULL END) AS CHAR CHARACTER SET utf8) col1, 
       CAST(MAX(CASE ordinal_position WHEN 2 THEN column_name ELSE NULL END) AS CHAR CHARACTER SET utf8) col2
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'test1'
UNION ALL
SELECT CAST(col1 AS CHAR CHARACTER SET utf8) col1, 
       CAST(col2 AS CHAR CHARACTER SET utf8) col2
  FROM test1

Open in new window

So from the above command complexity (when the table has many columns) seems to be better to use the query for column names separately from the table data query. In such case you don't need to convert the data to CHAR but you may process them in their natural form.
Simply said you may do following:
1) Issue SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'test1'
   and use the results to build the output heading
2) Issue SELECT * FROM test1
   and use the results for output rows.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial