SugarCRM - function to populate list from Database + static + Wincache

custom_utils.php

function get_options($lista, $where_sql = '', $order_by_name = false, $paramDB = NULL) {
	$mydb = NULL;
	static $defaultDB = NULL;
	
	$defaultDB = DBManagerFactory::getInstance();

	// Sometimes you may want to use custom connection to another database.
	// Then we pass it as the last parameter. Otherwise, we use the default one.
	$mydb = ($paramDB?:$defaultDB);

//	error_log('custom_utils.php: get_options: static $defaultDB: ' . isset($defaultDB) . ', $paramDB: ' . isset($paramDB));

	static $listas = array();
	$key = trim($lista . $where_sql);

	error_log("custom_utils.php: get_options(): key='{$key}'");

	if (empty($key) || empty($mydb)) {
		return array('' => '');
	}
 
	if (!isset($listas[$key]) || (array) $listas[$key] !== $listas[$key]) {
 
		$listas[$key] = array();
		// Get results from cache if possible. Otherwise, get results from database.
//		$listas[$key] = wincache_ucache_get($key, $success);
 
//		if (!$success) {
			$listas[$key][''] = '';
			/*
			* In the Database, create a VIEW, with "id" and "name" fields.
			* You may want to have other fields in the View, useful for the WHERE clause.
			* Here the values of $lista must be equal to the VIEW name or its substring,
			* (like in this case, we start view name with "vw_"), for easier integration.
			* you can add where and order_by clause also.
			*/
			$query = "SELECT id, name FROM vw_{$lista} " . $where_sql . ($order_by_name?' ORDER BY name':'');
			$result = $mydb->query($query, false);
			while (($row = $mydb->fetchByAssoc($result)) != NULL) {
				$listas[$key][$row['id']] = $row['name'];
			}
			// Add array of search results to user cache
//			wincache_ucache_add($key, $listas[$key], 600);
//		}
	}
	return $listas[$key];
}

This function is useful for us when populating several dropdown fields from database in SugarCRM/SuiteCRM.
For example, you may use it in the dictionary file like this:
$GLOBALS['app_list_strings']['db_XYZ_list'] = get_options('XYZ');

This is for Windows platform, uses wincache to store lists.
Querying a view helps hiding DB complexities behind, all fields already come like id - value pairs, but this is not mandatory, of course.
Here "Order By" clause is only sorted ASC, but is easy to change the code to accept ASC or DESC.