maartenmachiels maartenmachiels - 4 months ago 6
MySQL Question

Retrieve the column names from an empty MySQL query result

Is there a way to retrieve the column names of a query that returns no data?

I'm using (somewhat) complicated queries such as:

SELECT
i.*,
ic1.permalink as category_permalink,
ic1.title as category_title,
ic1.sid as category_sid,
ic2.permalink as hook_category_permalink,
ic2.title as hook_category_title,
ic2.sid as hook_category_sid
FROM item i
LEFT JOIN item_to_item_category itic ON i.sid = itic.item_sid
LEFT JOIN item_category ic1 ON ic1.sid = itic.item_category_sid
LEFT JOIN item_category ic2 ON ic1.hook = ic2.sid
WHERE i.uid = ''
LIMIT 0,1


The result of this query would be empty because of
WHERE i.uid = ""
. 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).

Anyone?

Answer

For PDO, try PDOStatement->getColumnMeta(). For mysqli, there's mysqli_result->fetch_fields(), mysqli_result->fetch_field() and mysqli_result->fetch_field_direct(). For mysql, replace it with PDO or mysqli.