Retrieve the column names from an empty MySQL query result

Question

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?

I think I know what you are trying to accomplish, but could you edit your question to include some context? Why do you need to know the column titles for a query that returns no data? — , Feb 24 '12 at 21:24
The reason why I need to do this is rather a long story. It's for a backoffice I've created. The DB extention is just the traditional PHP MySQL extention. It's time for an upgrade, so it seems. — chocolata, Feb 25 '12 at 08:56
Please update the question with the requested information. In general, respond to requests for clarifications by updating your post, rather than replying with a comment. For one thing, a question should be understandable without reading comments. For another, SO is a QA & site, not a forum, and comments aren't intended (nor are they well suited) for discussions. — outis, Feb 25 '12 at 12:04

score 3 · Accepted Answer · answered Feb 24 '12 at 21:33

3

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.

answered Feb 24 '12 at 21:33

outis

75,655
22
151
221

Thank you for your answer. It's high time to upgrade to MySQLi! – chocolata Feb 25 '12 at 09:12
@maartenmachiels: PDO has a nicer interface. See the answers to [Getting the syntax of prepared statements right](http://stackoverflow.com/q/9425405/) for some examples. – outis Feb 25 '12 at 12:03

dsanchez · Answer 2 · 2012-07-10T09:17:57.837

Here is a simple class based on mysqli. This method will work on empty tables as well. Of course, add your own error checking as needed.

Success will return a numerically indexed array of column names from $table.
Failure or non-string input will return FALSE.

class mysqlz extends mysqli {

    function fetch_table_columns($table) {
        if(!is_string($table)) {
            return false;
        } elseif($obj = $this->query("SHOW COLUMNS FROM " . $table)) {
                while($fields = $obj->fetch_object()) {
                    $columns[] = $fields->Field;
                }
                return $columns;
        } else {
            return false;
        }
    }
}

To use, first instantiate your database as usual:
$mysql = new mysqlz($host, $user, $pass, $database);

Then, call the method to retrieve the column names:
$columns = $mysql->fetch_table_columns("table_name_here");

Depending on your table, the resulting array should var_dump() similar to this:

array(3) { [0]=> string(2) "id" [1]=> string(10) "first_name" [2]=> string(9) "last_name" }

If method returns false (var_dump returns bool(false)) you can check the current error with $mysqlz->error. For more information on error checking, take a look at the PHP mysqli manual at http://www.php.net/manual/en/book.mysqli.php.

score 1 · Answer 3 · answered Feb 24 '12 at 21:34

1

Assuming you are calling the query from PHP. You can call mysqli_fetch_fields even with an emtpy query.

answered Feb 24 '12 at 21:34

slaakso

8,331
2
16
27

score 0 · Answer 4 · edited May 23 '17 at 12:22

0

For AdoDB you can use:

$db->Execute("SELECT .......");  
$colInfo = $res->FieldTypesArray();

The output is similar to method MetaColumns($table), but you can use it on every result set not only for tables (including joins). It works for empty results or empty tables too.

Thanks to outis, I found this function after searching "fetch_field()" in adoDBs mysql-driver :D

edited May 23 '17 at 12:22

Community

1
1

answered Aug 04 '16 at 09:27

Radon8472

4,285
1
33
41

Retrieve the column names from an empty MySQL query result

4 Answers4