Extract columns names resulting by query

Question

is it possible to get the columns names resulting by a query?

So (for example) if I have this query:

SELECT Id AS IdNumber, 
    (SELECT COUNT(*) FROM tab2 WHERE IdRif = T1.Id) AS TotCount 
FROM tab1 T1

I'd like to get:

IdNumber
TotCount

I saw MySQL query to get column names? (and also other questions) but I wasn't able to use it for what I need.

What do you want to get? Column names of a query, not a table? Why? You already have them inside the query. — user4035, Mar 30 '15 at 10:40
It may appear strange but I have a complex query and I get confused when I need to use the values in the array who gets query values. So If I can get columns names I'll work more easily (I'm not a pro) — genespos, Mar 30 '15 at 10:46
Are you using PHP along with MySQL ? To work on the Query result. — Venkata Krishna, Mar 30 '15 at 10:52
@genespos In both php and perl you can get the results into a hash. Keys of the hash will be column names. So, no need for a special query. What language/library do you use? — user4035, Mar 30 '15 at 10:53
I'm using Excel-VBA but, if possible, I'd like to use an SQL code to get columns names. In any case a vba code can be useful. — genespos, Mar 30 '15 at 12:33
In VBA I get data from query in an array with `myData = rs.getrows` (where 'rs' is my recordset) and I can get fields names with `Set myFields = rs.Fields`. But myFields is an Object and to get fields names in an array I'll need more code (and I cannot use debug window). What I need is to know the position of a certain field so to insert the exact index while coding. So, in the example above, when I'll need to call the "TotCount" I'll need to know that It's in "myData(1,x)" . This is why I'd like using mysql: I wish to see the field names (and their numbers) on screen. — genespos, Mar 30 '15 at 15:05

score 0 · Answer 1 · answered Mar 30 '15 at 10:53

If your query returns results just use the object(fetch_object) / array (fetch_assoc) from the row and use array_keys($row)

if no rows are returned use http://php.net/manual/en/mysqli-result.fetch-field-direct.php

$result = $db->query($sql);
$i = 0;
while ($i < $db->field_count) {
    $info = $result->fetch_field_direct($i++);
    echo $info->name;
}

as an example

score 0 · Answer 2 · answered Mar 30 '15 at 11:06

You can fetch the query result into an Associative Array using mysql_fetch_array($result,MYSQL_ASSOC) or $result->fetch_array(MYSQLI_ASSOC) or whatever method you are using, so that the Key=>Value pair of your Associative Array would be your ColumnName=>ColumnValue.

So, irrespective of whether you are using mysql or mysqli, you would be using the same logic to get the column names.

Extract columns names resulting by query

2 Answers2