How can I get the names of the columns with their corresponding row values as a list to use in a field (select list) of a form in Joomla MySQL

Question

My table '#__boats' is as follows:

id  category    type            AIG     ALLIANZ LLOYDS 
1   0           Motorboats          
2   0           Sailingboats            
3   0           Jet Ski         
4   1           up to 15hp              101,24
5   1           up to 25hp      88,32   86,96   118,11
6   1           26-30hp         89,32   87,96   119,11
7   1           31-50hp         90,32   88,96   120,11
8   2           51-70hp         107,64  104,35  168,74
9   2           71-75hp         111,78  104,35  168,74
10  3           76-100hp        111,78  104,35  168,74

The boathp element action is as follows:

function ff_boathp_action(element, action)
{
  switch (action) {
    case 'change':
      jQuery('#ff_elem9589').empty();
      jQuery.ajax({
        type: "POST",
        url: "https://myurl/boatspt.php",
        data: {
          boathp: element.value
        },
        success: function(data) {
          var obj = JSON.parse(data);
          var arr = obj;
          jQuery('#ff_elem9589').append(jQuery('<option>', {
            value: '',
            text: 'Επιλέξτε τύπο πλωτού'
          }));
          for (var i = 0; i < arr.length; i++) {
            console.log(arr[i]);
            jQuery('#ff_elem9589').append(jQuery('<option>', {
              value: arr[i],
              text: arr[i]
            }));
          }
        }
      });
      break;
    default:
      ;
  } // switch
} // ff_boathp_action

In a .php I have the following:

$judgePick = JRequest::getVar('boathp');
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('AIG,ALLIANZ,LLOYDS')
    ->from($db->quoteName('#__boats'))
    ->where($db->quoteName('type') . ' = ' . $db->quote($judgePick));
$db->setQuery($query);
$results = $db->loadAssocList();
if (!$results)
{
 echo "No results";
}
else
{
 echo json_encode($results);
}

If I use $results = $db->loadrow(); I get only the values of the row in question

If I add $columns = array_keys($results[0]); I get only the column names in question

but what I want to achieve in value descending order e.g. if the user requests 'type' = (up to 25hp), is the following:

ALLIANZ 86,96
AIG     88,32           
LLOYDS  118,11

Thank you

Answer 1

Start with:

$assoc = $db->loadAssoc();

• https://docs.joomla.org/Selecting_data_using_JDatabase#loadAssoc.28.29

This will provide a flat, associative result set array:

$assoc = [
    'AIG' => '88,32',
    'ALLIANZ' => '86,96',
    'LLOYDS' => '118,11',
];

Then prepare the results for sorting by creating a new column as the first element that represents the numeric value with a standard decimal point. Also, calculate and cache the combined length of the column name and the numeric value string for future use.

$filtered = array_filter(
    $assoc,
    function($v) {
        return $v && $v != '0,00';
    }
);
$prepared = [];
$max = 0;
foreach ($filtered as $columnName => $number) {
    $rawLength = strlen($columnName . $number);
    $max = max($max, $rawLength);
    $prepared[htmlentities($columnName)] = [
        'asDecimal' => (float) str_replace(',', '.', $number),  // declared as first element for sorting
        'length' => $rawLength,
        'value' => $number,
    ];
    
}
asort($prepared);

Now, set the minimum amount of space by which the two "columns" should be separated and store the 2-column string for use in the javascript. You can safely remove data that will not be used on the client-side scripting.

$min = 5;
foreach ($prepared as $encodedName => $subarray) {
    $prepared[$encodedName]['glue'] = str_repeat(' ', $max - $subarray['length'] + $min);
    unset($prepared[$encodedName]['asDecimal'], $prepared[$encodedName]['length']);
}

At this point you have this array data/structure:

array (
  'ALLIANZ' => 
  array (
    'value' => '86,96',
    'glue' => '     ',
  ),
  'AIG' => 
  array (
    'value' => '88,32',
    'glue' => '         ',
  ),
  'LLOYDS' => 
  array (
    'value' => '118,11',
    'glue' => '     ',
  ),
)

Here is a (PHP Demo). Then return your json:

echo json_encode($prepare)

---

In your javascript, you need to iterate the associative structure. append() is okay to use, but generating html tags from arguments via an object (jQuery('<option>', {...attributs&values})) is going to automatically apply html encoding as a security measure. Instead, create <option> tags as strings and append them as children into the <select> tag.

dataType: "json",
success: function(data) {
    data = {...{"":{glue: "", value:"Επιλέξτε τύπο πλωτού"}, ...data}};  // prepend your static property to the incoming object
    jQuery.each(data, function (encodedName, subdata) {
        jQuery('#ff_elem9589').append(
            '<option value="' + encodedName + '">'
            + encodedName + subdata.glue + subdata.value
            + '</option>'
        );
    });
}

Since <option> tags cannot contain html tags (in a valid HTML document as far as I know), you can only try to hack the display by applying font-family: monospace styling upon the option tags. This will ensure that all character have the same width -- this gives the effect of the split text being stuck to the left and right of each visible option.

Relevant reading:

• align two words in a select option
• Aligning part of a select option's text on the left and part on the right?
• How can I align part of text inside in select

Ultimately, I have to say that this probably a little too unsavoury of a hack for my liking. Unless you are fully committed to this approach, I recommend a re-think. Perhaps consider a set of radio buttons with the values; you might add a collapse/expand effect if you need to minimise the footprint on the webpage.