Get column names from an empty MySQL query result

Question

Is there a way to get the query column names that don't return data?

I use (several) complex 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 will be empty due to WHERE i.uid = "" . Is there a way to find column names when there is no result?

Note that I know the 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 multi-column requests.

Also note that I'm still using the original PHP MySQL extension (so not MySQLi and PDO).

Is anyone

Answer 1

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

Answer 2

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

Answer 3

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

Success will return the numeric index of the 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 create your database as usual:
$mysql = new mysqlz($host, $user, $pass, $database);

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

Depending on your table, the resulting array should look like this: var_dump () :

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

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

Answer 4

For AdoDB you can use:

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

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

Thanks to outis , I found this function after searching for "fetch_field ()" in the mysql driver adoDB: D