2

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.

2

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: []
1
  • Ohh... nice find.
    – Brad Bell
    Feb 24 '16 at 18:03
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.

1
  • 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
    Feb 24 '16 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.