How can I get the names of the columns in an object or table with Joomla's SQL?

Question

With the existing output I have from a basic query, what would be the best way to get the names of the columns in the resultant output object without having to run an additional COLUMNS query on the database - in order to save database calls?

    $db = JFactory::getDbo();
    $query = $db->getQuery(true);
    $query->select('*');
    $query->from('#__table');
    $db->setQuery($query);
    $results = $db->loadObjectList();

    ----

    foreach($results as $key=>$value)
    {
       echo $key;
    }

It returns an object, but none of my attempts to get the key from key => value seem to be working, even with a foreach loop. I don't want to run extra queries just to get my keys. How would I do this with the existing output from $results?

Answer 1

Your foreach will result in situation where $key is array key and $value is one row from database. If you want to get column names from result just use get_object_vars:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('*');
$query->from('#__table');
$db->setQuery($query);
$results = $db->loadObjectList();

// this will return array of object properties
// in format: array('column1'=>'content1','column2'=>'content2')
$properties = get_object_vars($results[0]);

You can also just ask for array of arrays instead of array of objects. Its made like this:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('*');
$query->from('#__table');
$db->setQuery($query);
$results = $db->loadAssocList();

// this will return array of column names properties
// in format: array('column1','column2')
$columns = array_keys($results[0]);

Answer 2

To get all columns of a table as arrays use JDatabase class method as follows:

$db=JFactory::getDBO();
$columnArr=$db->getTableColumns("#__table_name");
foreach ($columnArr as $key => $value) {
    // here $key will be column name and $value will be type
    // process array code
}

Answer 3

$db = JFactory::getDbo();
$columns = $db->getTableKeys('tableName');