Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-02-24T18:03:15.230Z
Answers.Text: Ohh... nice find.
Answers.UserDisplayName: Brad Bell
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-02-24T07:55:21.460Z
Answers.LastActivityDate: 2016-02-24T07:55:21.460Z
Answers.UserDisplayName: fuling
Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-02-24T08:03:35.170Z
Answers.Text: 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.
Answers.UserDisplayName: fuling
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-02-23T22:34:12.377Z
Answers.LastActivityDate: 2016-02-23T22:34:12.377Z
Answers.UserDisplayName: Brad Bell
Body: <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>
  
Tags: <plugin-development><database>
Title: Getting metadata/column names for empty resultset
ThreadText: # 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<resultset.getMetaData().getColumnCount();i++){
      columnNames.add(resultset.getMetaData().getColumnLabel(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.  
  
  ---
  
  