Select2 concatenating various fields

carrarachristophe · December 2021

Hello,
I don't find any example of a Select2 field listing various concatenated mysql fields, or even including an icon (flag).
Would you have any example to share?
I am currently using the following:

            {
                label: "Langue:",
                name: "utilisateurs.langue",
                type: 'select2',
                placeholder: '',
                placeholderDisabled: false,
                placeholderValue: null
            },

Thank you

allan · December 2021

Hi,

The display of Select2 is dependent upon what you return from the server. By default it uses id and text properties in the array of results, although in Editor's plug-in we actually use value / label to match the other field types in Editor.

So your label return from the server would include the two (or more) concatenated fields. How are you currently populating the values? I presume you are reading them from an SQL database?

Thanks,
Allan

allan · December 2021

I forgot to reply about the icons, sorry. This Select2 documentation page shows how to use their templateResult option to render HTML.

Allan

carrarachristophe · December 2021

Hi Allan,
Yes I am using ajax to read mysql data

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.bibliotheque_auteurs.php',
        table: '#bibliotheque_auteurs',
        fields: [
            {
                label: "Auteur:",
                name: "bibliotheque_auteurs.auteur"
            },
            {
                label: "Nationalité:",
                name: "bibliotheque_auteurs.nationalite",
                type: 'select2',
                placeholder: '',
                placeholderDisabled: false,
                placeholderValue: null
            }
        ]
    } )

with the following in table.bibliotheque_auteurs.php:

<?php

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'bibliotheque_auteurs', 'auteur_id' )
    ->fields(
        Field::inst( 'bibliotheque_auteurs.auteur' )
            ->options( Options::inst()
                ->table( 'bibliotheque_auteurs' )
                ->value( 'auteur_id' )
                ->label( 'auteur' ))
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Auteur requis' )))
            ->validator( Validate::maxLen( 40 ) )
            ->validator( Validate::unique( ValidateOptions::inst()
                ->message( 'Existe déjà' ))),
        Field::inst( 'bibliotheque_auteurs.nationalite' )
            ->options( Options::inst()
                ->table( 'pays' )
                ->value( 'pays_id' )
                ->label( 'pays' ))
            ->setFormatter( Format::ifEmpty( null ) )
            ->validator( Validate::dbValues() ),
        Field::inst( 'pays.pays' ),
        Field::inst( 'pays.pays_code' )
    )
    ->leftJoin( 'pays', 'pays.pays_id', '=', 'bibliotheque_auteurs.nationalite' )
    ->process( $_POST )
    ->json();

The Select2 dropdown currently only displays the field pays (which apears as label) from the table pays.
I would like to display both pays and pays_code from the same mysql table pays

allan · December 2021

That will populate the Select list from:

            ->options( Options::inst()
                ->table( 'pays' )
                ->value( 'pays_id' )
                ->label( 'pays' ))

The documentation for the Options class is available here and as shown in one of it's examples you can pass an array of columns into the label method - e.g.:


            ->options( Options::inst()
                ->table( ['pays', 'pays_code'] )
                ->value( 'pays_id' )
            )

You can also specify a render function if you want to customise how the fields are joined together (by default they are space separated).

Allan

carrarachristophe · December 2021

Thank you Allan,
I was able to concatenate both pays and pays_code using the following:

        Field::inst( 'bibliotheque_auteurs.nationalite' )
            ->options( Options::inst()
                ->table( 'pays' )
                ->value( 'pays_id' )
                ->label( array('pays', 'pays_code') )
                ->render( function ( $row ) {
                    return $row['pays'].' ('.$row['pays_code'].')';
                } ))
            ->setFormatter( Format::ifEmpty( null ) )
            ->validator( Validate::dbValues() )

But I fail adding an image in the string. I tried the following:

        Field::inst( 'bibliotheque_auteurs.nationalite' )
            ->options( Options::inst()
                ->table( 'pays' )
                ->value( 'pays_id' )
                ->label( array('pays', 'pays_code') )
                ->render( function ( $row ) {
                    return '<img src="img/flags-iso/flat/16/' + $row['pays_code'] + '.png">' +' ' + $row['pays'].' ('.$row['pays_code'].')';
                } ))
            ->setFormatter( Format::ifEmpty( null ) )
            ->validator( Validate::dbValues() )

I am getting the following message:
DataTables warning: table id=bibliotheque_auteurs - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
http://datatables.net/tn/1

Warning: A non-numeric value encountered in C:\xampp\htdocs.....\php\table.bibliotheque_auteurs.php on line 7

Any idea of what I am doing wrong?
note: I changed the line number so you it matches with the above code.

allan · December 2021

     return '<img src="img/flags-iso/flat/16/' + $row['pays_code'] + '.png">' +' ' + $row['pays'].' ('.$row['pays_code'].')';

The string concatenation uses Javascript style concat for about half of it, and PHP style for the other half

     return '<img src="img/flags-iso/flat/16/'. $row['pays_code'] .'.png">' .' ' . $row['pays'].' ('.$row['pays_code'].')';

Should do it.

Allan

carrarachristophe · December 2021

Hi Allan,
Thanks for your reply, and merry Christmas to you!

I am confused. I should use the code in the php file, correct?

Because I am getting the following value in the dropdown: (example)
<img src="img/flags-iso/flat/16/AL.png"> Albania (AL)

instead of:
theimage Albania (AL)

allan · December 2021

Merry Christmas to you as well

From the Select2 documentation it looks like you need to specify at templateResult function in order to have it display HTML inside the dropdowns.

Allan

carrarachristophe · December 2021

Thanks Allan,
Looks like this is not something easy to do.
I think this post might also help me.

Select2 concatenating various fields

Select2 concatenating various fields

Answers

Howdy, Stranger!

Categories

DataTables

Select2 concatenating various fields

Select2 concatenating various fields

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables