Easy way for getting options for dropdown in Admin UI

[Jimmi08]

This is just a bit annoying... maybe there could be a solution.

I know that I can use foreach or other code to get what I need, but...

For select field in Admin UI I need only ID (value) and Name (text).

I would like to have simple:

$cats = e107::getDb()->retrieve('hdu_helpdesk', "hdudesk_name", true, true, 'hdudesk_id');

but result is (complete wrong)

Array
(
    [] => Array
        (
            [hdudesk_name] => helpdest
        )

)

With
$cats = e107::getDb()->retrieve('hdu_helpdesk', "hdudesk_id, hdudesk_name", true, true, 'hdudesk_id');

Result is : (see correct key)

Array
(
    [1] => Array
        (
            [hdudesk_id] => 1
            [hdudesk_name] => Helpdesk 01cc
        )

    [3] => Array
        (
            [hdudesk_id] => 3
            [hdudesk_name] => helpdest
        )

)

with this I can use:

if (version_compare(PHP_VERSION, '7.4.0', '>='))
		{
			// Use arrow function for PHP 7.4+
			$result = array_map(fn($cats) => $cats['hdudesk_name'], $cats);
		}
		else
		{
			// Use anonymous function for older PHP versions
			$result = array_map(function ($cat)
			{
				return $cat['hdudesk_name'];
			}, $cats);
		}

Correct result:

Array
(
    [1] => Helpdesk 01cc
    [3] => helpdest
)

Old way:
$cats = e107::getDb()->retrieve('hdu_helpdesk', "hdudesk_id, hdudesk_name", true, true );

Array
(
    [0] => Array
        (
            [hdudesk_id] => 1
            [hdudesk_name] => Helpdesk 01cc
        )

    [1] => Array
        (
            [hdudesk_id] => 3
            [hdudesk_name] => helpdest
        )

)

You need to use foreach to get correct result.

Is there solution for this? Thanks

[Vodhin]

I would use the old $sql->select() way. Not as neat as $sql->retrieve() but concise and more flexible;

$sql = e107::getDb();
$cats = array();

$sql->select("hdu_helpdesk","hdudesk_id,hdudesk_name","ORDER BY hdudesk_name ASC");
while($rows=$sql->fetch()){
  $cats[$rows['hdudesk_id']] = $rows['hdudesk_name'];
  }

[Vodhin]

$sql->retrieve is just a newer function added to e107 e_db_interface.php. The actual code behind it:

public function retrieve($table, $fields = null, $where=null, $multi = false, $indexField = null, $debug = false);

which is implemented by e_db_pdo_class.php

	/**
	 * Query and fetch at once
	 *
	 * Examples:
	 * <code>
	 * <?php
	 *
	 * // Get single value, $multi and indexField are ignored
	 * $string = e107::getDb()->retrieve('user', 'user_email', 'user_id=1');
	 *
	 * // Get single row set, $multi and indexField are ignored
	 * $array = e107::getDb()->retrieve('user', 'user_email, user_name', 'user_id=1');
	 *
	 * // Fetch all, don't append WHERE to the query, index by user_id, noWhere auto detected (string starts with upper case ORDER)
	 * $array = e107::getDb()->retrieve('user', 'user_id, user_email, user_name', 'ORDER BY user_email LIMIT 0,20', true, 'user_id');
	 *
	 * // Same as above but retrieve() is only used to fetch, not useable for single return value
	 * if(e107::getDb()->select('user', 'user_id, user_email, user_name', 'ORDER BY user_email LIMIT 0,20', true))
	 * {
	 *        $array = e107::getDb()->retrieve(null, null, null,  true, 'user_id');
	 * }
	 *
	 * // Using whole query example, in this case default mode is 'one'
	 * $array = e107::getDb()->retrieve('SELECT
	 *    p.*, u.user_email, u.user_name FROM `#user` AS u
	 *    LEFT JOIN `#myplug_table` AS p ON p.myplug_table=u.user_id
	 *    ORDER BY u.user_email LIMIT 0,20'
	 * );
	 *
	 * // Using whole query example, multi mode - $fields argument mapped to $multi
	 * $array = e107::getDb()->retrieve('SELECT u.user_email, u.user_name FROM `#user` AS U ORDER BY user_email LIMIT 0,20', true);
	 *
	 * // Using whole query example, multi mode with index field
	 * $array = e107::getDb()->retrieve('SELECT u.user_email, u.user_name FROM `#user` AS U ORDER BY user_email LIMIT 0,20', null, null, true, 'user_id');
	 * </code>
	 *
	 * @param string $table if empty, enter fetch only mode
	 * @param string $fields comma separated list of fields or * or single field name (get one); if $fields is of type boolean and $where is not found, $fields overrides $multi
	 * @param string $where WHERE/ORDER/LIMIT etc clause, empty to disable
	 * @param boolean $multi if true, fetch all (multi mode)
	 * @param string $indexField field name to be used for indexing when in multi mode
	 * @param boolean $debug
	 * @return mixed
	 */
	public function retrieve($table=null, $fields = null, $where=null, $multi = false, $indexField = null, $debug = false)
	{
		// fetch mode
		if(empty($table))
		{

			if(!$multi)
			{
				 return $this->fetch();
			}

			$ret = array();

			while($row = $this->fetch())
			{
				if(null !== $indexField)
				{
					 $ret[$row[$indexField]] = $row;
				}
				else
				{
					 $ret[] = $row;
				}
			}
			return $ret;
		}

		// detect mode
		$mode = 'one';
		if($table && !$where && is_bool($fields))
		{
			// table is the query, fields used for multi
			if($fields)
			{
				 $mode = 'multi';
			}
			else
			{
				 $mode = 'single';
			}

			$fields = null;
		}
		elseif($fields && '*' !== $fields && strpos($fields, ',') === false && $where)
		{
			$mode = 'single';
		}

		if($multi)
		{
			$mode = 'multi';
		}

		// detect query type
		$select = true;
		$noWhere = false;
		if(!$fields && !$where)
		{
			// gen()
			$select = false;
			if($mode == 'one' && !preg_match('/[,*]+[\s\S]*FROM/im',$table)) // if a comma or astericks is found before "FROM" then leave it in 'one' row mode.
			{
			    $mode = 'single';
			}
		}
		// auto detect noWhere - if where string starts with upper case LATIN word
		elseif(!$where || preg_match('/^[A-Z]+\S.*$/', trim($where)))
		{
			// FIXME - move auto detect to select()?
			$noWhere = true;
		}


		// execute & fetch
		switch ($mode)
		{
			case 'single': // single field value returned.
				if($select && !$this->select($table, $fields, $where, $noWhere, $debug))
				{
					return null;
				}
				elseif(!$select && !$this->gen($table, $debug))
				{
					return null;
				}
				$rows = $this->fetch();
				return array_shift($rows);
			break;

			case 'one': // one row returned.
				if($select && !$this->select($table, $fields, $where, $noWhere, $debug))
				{
					return array();
				}
				elseif(!$select && !$this->gen($table, $debug))
				{
					return array();
				}
				return $this->fetch();
			break;

			case 'multi':
				if($select && !$this->select($table, $fields, $where, $noWhere, $debug))
				{
					return array();
				}
				elseif(!$select && !$this->gen($table, $debug))
				{
					return array();
				}
				$ret = array();
				while($row = $this->fetch())
				{
					if(null !== $indexField) $ret[$row[$indexField]] = $row;
					else $ret[] = $row;
				}
				return $ret;
			break;

		}

		return null;
	}

The $sql->select() is ultimately simpler since you're bypassing a lot of checks that $sql->retrieve() includes.

[Vodhin]

Even less complicated (as far as code behind the function) is $sql->gen() It requires more input on your end but runs lighter in the end;

$sql = e107::getDb();
$cats = array();
$sql->gen("SELECT hdudesk_id, hdudesk_name FROM #hdu_helpdesk ORDER BY hdudesk_name ASC");
while($rows=$sql->fetch()){$cats[$rows['hdudesk_id']] = $rows['hdudesk_name']; }

// note: the table name needs the '#' prefix.

Both of my examples are ready for the options of a drop down list.

[Vodhin]

I you have a lot of dropdown opts to fill you could add the following function to your e_admin_ui :

public function getopts($table,$kfld,$vfld,$where='',$arg='',$noWhere=false,$debug=false,$log_type='',$log_remark=''){
  $ret = array();
  $sql = e107::getDB();
  $sql->select($table,$kfld.','.$vfld,$where,$arg,$noWhere,$debug,$log_type,$log_remark);
    while($rows = $sql->fetch()){$ret[$rows[$kfld]] = $rows[$vfld];}
    return $ret;
    }

then simply call it in your init() like so:

public function init(){
   $this->fields['field_name']['writeParms']['optArray'] = $this->getopts('hdu_helpdesk','hdudesk_id','hdudesk_name');
   }

[Jimmi08]

Hi, nice attempt, but I know how to get my options array. I avoid to use select()and fetch() for long time already. It is the ntention after updating content plugin.

The point was something else... first query should work and it doesn't do. I have 2 fields and I want to create dropdown options easily, nothing more. The fifth parameter of retrieve() was made exactly for this,

[Jimmi08]

@Vodhin I don't want to go into discussion on this topic because I already spent a lot of time many years ago when I first reported this.
But this is real example why I use only retrieve() in my plugins:

file:

		if ($sql->select("jobsch_cats", "*", "where find_in_set(jobsch_catclass,'" . USERCLASS_LIST . "')  order by jobsch_catname", "nowhere", false))
		{
			while ($jobsch_row = $sql->fetch())
			{
				$jobsch_sc->setVars($jobsch_row);
				 
				$jobsch_text .= $tp->parsetemplate($JOBSCH_CAT_DETAIL, false, $jobsch_sc);
				
			} // while
		}

If the template has a shortcode with next $sgl and select used... first select the last query is overridden for first query too and your results are wrong. In this case it was shortcode for displaying subcategories. Maybe the reason is that I am updating older plugins or so, but I prefer retrieve() because it was always correct.

[rica-carv]

I use getdb_table, only needs two code lines:

protected $prefs = array('def_pais' => array('title' => LAN_PHILCAT_12." ".PHILCATLAN_11, 'tab'=>2, 'type' => 'dropdown', 'data' => 'str', 'readParms' => array()),

$this->prefs['def_pais']['writeParms']['optArray'] = varset(self::getdb_table('pais', 'selprefarray'), array());