CODEWITHSUNDEEP
mysql
Mariya
Mariya

Reputation: 41

How can I retrieve the column names from an empty MySQL select query result

Is there a way to retrieve the column names of a query that returns no data? The result of this query would be empty. Is there a way how to find the column names when there's no result?

Please note that I'm aware of solutions using DESCRIBE and select column_name from information_schema.columns where table_name='person'; but I need a more flexible solution that will fit these multicolumn queries.

Please also note that I am still using the original PHP MySQL extention (so no MySQLi, and no PDO).

Upvotes: 4

Views: 4397

Answers (4)

akenney
akenney

Reputation: 492

If you wrap your query with the following SQL, it will always return the column names from your query, even if it is an empty query result:

select myQuery.*
from (select 1) as ignoreMe
left join (
select * from myTable where false -- insert your query here
) as myQuery on true

Note: When the results of the subquery are empty, a single row of null values will be returned. If there is data in the subquery it won't affect the output because it creates a cross-product with a single row...and value x 1 = value

Upvotes: 9

Payer Ahammed
Payer Ahammed

Reputation: 907

Execute following command if the result of your previous query is empty

SHOW columns FROM your-table;

For more details check this.

Upvotes: 2

Jerko W. Tisler
Jerko W. Tisler

Reputation: 989

I'm not sure if it will satisfy you but you can do this

SELECT *, COUNT(*) FROM table;

It will return null values (except last column which you can ignore) if the query is empty and you will be able to access all columns. It's not proper way of doing it and selecting names from INFORMATION_SCHEMA would be much better solution. Please note that result is aggregated and you need to use GROUP BY to get more results if there are any.

Upvotes: 2

alican akyol
alican akyol

Reputation: 288

You should , Select COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS Where TABLE_SCHEMA='yourdb' AND TABLE_NAME='yourtablename';

Upvotes: -2

Related Questions