4

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?

7

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]);
  • Thank you! It was that trailing [0] I've been missing on the array_keys version, I've tried it a couple attempts but never got it right. – Merlin Jul 13 '15 at 0:31
  • If you use IDE add Joomla /libraries directory to includes. That way you will know what certain function return. – Artur Stępień Jul 13 '15 at 9:55
3

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
}
-1
$db = JFactory::getDbo();
$columns = $db->getTableKeys('tableName');
  • 1
    This looks like a good answer, but I don't think that the method takes an array: github.com/joomla/joomla-cms/blob/… – David Fritsch Jan 4 '16 at 1:12
  • You are right, I was looking at github.com/joomla/joomla-cms/blob/… which suggests that it should take an array..but implementation is inconsistent with that – Klas Jan 5 '16 at 9:05
  • This is a handy method, but it does not satisfy the question requirements. The OP wants the column names and the data in a single query. Your single query only delivers half of the required data. – mickmackusa May 27 '18 at 9:42

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.