Select2 concatenating various fields

Select2 concatenating various fields

carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2

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

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    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

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

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

    Allan

  • carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2

    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

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin
    Answer ✓

    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

  • carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2

    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</b>: A non-numeric value encountered in <b>C:\xampp\htdocs.....\php\table.bibliotheque_auteurs.php</b> on line <b>7</b>

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

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin
    edited 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

  • carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2

    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)

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    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

  • carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2

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

Sign In or Register to comment.