<p>I was close - I think the problem was the <code>$command-&gt;prepare()</code> did not do anything useful, using <code>$command-&gt;query()</code> executes the query.</p>

<p>This is working for me</p>

<pre><code>    $sql = "select t.foo, count(o.bar) as amount from craft_table t join craft_otherTable o on t.id = o.id where 1=0 group by t.foo;"
    $command = craft()-&gt;db-&gt;createCommand($sql);
    $reader = $command-&gt;query($parameters);
    $statement = $command-&gt;getPdoStatement();
    $result = [];
    $headers = [];
    for($j=0;$j&lt;$statement-&gt;columnCount();$j++){
      $headers[] = $statement-&gt;getColumnMeta($j)['name'];
    }
    $result['headers'] = $headers;
    $result['rows'] = $reader-&gt;readAll();

    // $result['headers'] is now: ['foo', 'amount']
    // $result['rows'] is now: []
</code></pre>

CC BY-SA 3.0
2016-02-24T18:03:15.230Z
Ohh... nice find.
Brad Bell
CC BY-SA 3.0
2016-02-24T07:55:21.460Z
2016-02-24T07:55:21.460Z
fuling
<p>Not very pretty, but the only way I can think of to achieve that behavior is to explicitly check for an empty result set and then run separate <code>SHOW CREATE TABLE</code> commands on the tables to grab the column names and add them as keys to the results.</p>

CC BY-SA 3.0
2016-02-24T08:03:35.170Z
That could work if i did not use any aliases, but would still require some parsing of the sql to get the specific columns in the select statement.
fuling
CC BY-SA 3.0
2016-02-23T22:34:12.377Z
2016-02-23T22:34:12.377Z
Brad Bell
<p>I have a dynamically generated sql. How can I know what the column names are, even if the database returns an empty result?</p>

<pre><code>$sql = "select t.bar, count(o.foo) as amount
        from craft_table t join craft_otherTable o on t.id = o.id";
$resultset = craft()-&gt;db-&gt;createCommand($sql)-&gt;queryAll(true);
</code></pre>

<p>In Java, i can do something like this:</p>

<pre><code>for(int i=0;i&lt;resultset.getMetaData().getColumnCount();i++){
    columnNames.add(resultset.getMetaData().getColumnLabel(i))
}
</code></pre>

<p>I am looking for a similar solution in PHP.</p>

<p>I have tried this:</p>

<pre><code>$command = craft()-&gt;db-&gt;createCommand($sql);
$command-&gt;prepare();
var_dump($command-&gt;getPdoStatement()-&gt;getColumnMeta(0));
</code></pre>

<p>...but the output is <code>bool(false)</code>. I feel like I am missing something.</p>

<plugin-development><database>
Getting metadata/column names for empty resultset
# Getting metadata/column names for empty resultset

(Asked by: username_0 on 2016-02-22)

I have a dynamically generated sql. How can I know what the column names are, even if the database returns an empty result?
$sql = "select t.bar, count(o.foo) as amount
        from craft_table t join craft_otherTable o on t.id = o.id";
$resultset = craft()->db->createCommand($sql)->queryAll(true);

In Java, i can do something like this:
for(int i=0;i
I am looking for a similar solution in PHP.
I have tried this:
$command = craft()->db->createCommand($sql);
$command->prepare();
var_dump($command->getPdoStatement()->getColumnMeta(0));

...but the output is bool(false). I feel like I am missing something.

**Tags:** plugin-development, database


---

## 2 answers

### Answer by username_0 on 2016-02-24. Score: 2 | Accepted answer

I was close - I think the problem was the $command->prepare() did not do anything useful, using $command->query() executes the query.
This is working for me
    $sql = "select t.foo, count(o.bar) as amount from craft_table t join craft_otherTable o on t.id = o.id where 1=0 group by t.foo;"
    $command = craft()->db->createCommand($sql);
    $reader = $command->query($parameters);
    $statement = $command->getPdoStatement();
    $result = [];
    $headers = [];
    for($j=0;$j<$statement->columnCount();$j++){
      $headers[] = $statement->getColumnMeta($j)['name'];
    }
    $result['headers'] = $headers;
    $result['rows'] = $reader->readAll();

    // $result['headers'] is now: ['foo', 'amount']
    // $result['rows'] is now: []

> **Comments:**
> **username_1**: Ohh... nice find.  

---

### Answer by username_1 on 2016-02-23. Score: 1

Not very pretty, but the only way I can think of to achieve that behavior is to explicitly check for an empty result set and then run separate `SHOW CREATE TABLE` commands on the tables to grab the column names and add them as keys to the results.

> **Comments:**
> **username_0**: That could work if i did not use any aliases, but would still require some parsing of the sql to get the specific columns in the select statement.  

---

