Add Database result As a Select Option

Question

I'm creating a Joomla 3x SP Pagebuilder3 addon, but I'm having a bit of challenge getting a database results as an option in a select type.

This is what I've done so far.

class MyCustomTypes {

public static function getBannerCats(){
    $db       = JFactory::getDbo();

    $query = $db->getQuery(true)
        ->select(array('id', 'title', 'alias'))
        ->from('#__categories')
        ->where('extension = ' . $db->quote('com_banners'))
        ->where('parent_id > 0')
        ->where('published > -1');
    $db->setQuery($query);
    $items = $db->loadObjectList();

     $options = array();
     if ($items)
     {
      foreach($items as $item)
      {
        // THIS IS WHERE MY PROBLEM IS
        $options[] = $item->title;
      }
     }

    // $options = array_merge(parent::getOptions(), $options);

     return $options;

  }

}

I want to options to show here

        'category_ids'=>array(
            'type'=>'select',
            'title'=>JText::_('Categories'),
            'desc'=>JText::_('Category Source'),
            'values'=>array(MyCustomTypes::getBannerCats()),// Result will show here like so 'result_id'=>'result_title'
        )

I will be glad if someone can help me. Thank you.

Answer 1

After updating your question...

If your $options array must be associative, then build it that way in the loop.

$options[$item->id] = $item->title;

Your values element looks to be too complex.

'values'=>array(MyCustomTypes::getBannerCats())  // multi-dim array

this will store your returned 1-dimensional array inside of an array. Instead, I suppose you should be using:

'values' => MyCustomTypes::getBannerCats()  // 1-dimensional array

---

Before the question update...

It looks like your select() method is receiving 3 parameters, but you intend to pass 1 string containing 3 values or an array with 3 elements.

I recommend this:

->select(['id', 'title', 'alias'])

or

->select('id, title, alias')

or

->select($db->qn(['id', 'title', 'alias']))

To explain what Joomla is doing... The select() method only accepts one parameter/argument, so only id is used and title and alias are simply ignored by the method. If you call $query->dump() you would see something like the following (with your table prefix in place of #_)

SELECT id
FROM #__categories
WHERE extension = 'com_banners' AND parent_id > 0 AND published > -1

Ergo, $item->title; will never have any data in it.

---

Before posting a question when thinks aren't working, please check for errors and dump your query to make sure that there are no simple typos.

p.s. if you are only going to use title then only write one value in the SELECT, and use a simpler result set retrieving method to process a single column of data.