1

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

3

Start with:

$assoc = $db->loadAssoc();

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('&nbsp;', $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' => '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;',
  ),
  'AIG' => 
  array (
    'value' => '88,32',
    'glue' => '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;',
  ),
  'LLOYDS' => 
  array (
    'value' => '118,11',
    'glue' => '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;',
  ),
)

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.

enter image description here

Relevant reading:

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.

6
  • Found it! Your code works fine! The problem was the greek characters. When I changed the column names in the table from greek to latin, everything is alligned perfectly! Thank you! Now I am trying to understand why the data = {...{"":"Επιλέξτε τύπο πλωτού", ...data}}; appears as undefinedundefined. How can I also omitt values <1 thus, not to have zero values.
    – GalanopD
    Jul 17 at 18:42
  • array_filter() should remove all null and zero values. foreach (array_filter($assoc) as $columnName => $number) {
    – mickmackusa
    Jul 17 at 20:08
  • This doesn't remove the zero values because the type in db is float(4,2) in order to show the decimal (e.g. 52.36, 115.11 or 2341.39), therefore when I set the value to 0 it turns to 0.00. The zero values are not shown when the type is set to float because then the value is 0. Also, any idea on the undefinedundefined ?
    – GalanopD
    Jul 18 at 4:25
  • Regarding undefinedundefined -- this is my mistake. I didn't update my js since passing glue from php. It is printing "" + subdata.glue + subdata.value = "" + "undefined" + "undefined". If your floats in php are 0,00, then write a custom function for array_filter(). array_filter($assoc, function($v) { return $v && $v != '0,00'; } ); I've updated my answer.
    – mickmackusa
    Jul 18 at 4:41
  • 1
    Everything works fine! There is nothing left but to thank you for your time and patience. Your support is really valuable !
    – GalanopD
    Jul 18 at 7:26

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.