Populate distinct values in select drop-down from MySQL database

avrrr

I have a custom form template for datatables editor, with a field called region, for which I would like to populate a select drop-down using values from the database, and not predefined using options array. The problem is that even though it does populate the values, there are duplicates as it pulls the values of the entire table and not only distinct values, as it should in terms of a select field. My PHP is included in excerpt A and the js in excerpt B:

Excerpt A:

Field::inst( 'Region' )
            ->validator( Validate::notEmpty() )
            ->options( Options::inst()
                        ->table( 'tblusers' )
                        ->value( 'MemId' )
                        ->label( 'Region' )
                    ),

Excerpt B:

{
                "label": "Streek:",
                "name": "Region",
                'className': 'full block',
                "type": "select",
                "def": "Kies n Streek",
                /*"options": [
                    "Gauteng",
                    "KwaZulu-Natal",
                    "Limpopo Provinsie",
                    "Mpumalanga",
                    "Noord-Kaap",
                    "Noordwes",
                    "Oos-Kaap",
                    "Vrystaat",
                    "Wes-Kaap"
                ]*/
            },

I don't seem to find any documentation on SQL Distinct for datatables editor? Any advise for this use case please?

colin

Hi @avrrr ,

This thread should help, it's asking the same thing.

Cheers,

Colin

avrrr

Hi Collin

Thanks for the info, I will give it a try and let you know the outcome, I am however not using joins, all the data is from the same table in the database.

Thanks

avrrr

HI Colin,

Unfortunately it does not work.

Thanks

colin

Hi @avrrr ,

Could you give more information, please, on what you've tried and why it doesn't work?

Cheers,

Colin

avrrr

Hi Colin

As per the thread you provided, I made the changes to the code to use distinct SQL queries, it however still gave me multiple values in the select dropdown. I have used this exact approach on custom PHP and SQL code (outside editor) with no issues, but for some reason when I am struggling to achieve this with editor.

Thanks

allan

Hi avrrr,

Could you show me your full PHP code for this Editor instance please? Also can you use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

Thanks,
Allan

avrrr

Hi Allan

Editor::inst( $db, 'tblusers', 'MemId' )
    ->fields(
        Field::inst( 'FirstName' )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'LastName' )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'Address' )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'Cell' )
            ->validator( 'Validate::unique', array(
            "message" => "Duplikaatinskrywing gevind."
        ) )
            ->validator( 'Validate::minMaxLen', array(
            'min' => 11,
            'max' => 11,
            "message" => "Voer asb n geldige Sel Nommer in."
        ) ),
        Field::inst( 'PhoneH' )
            ->validator( 'Validate::minMaxLen', array(
                'min' => 11,
                'max' => 11,
                "message" => "Voer asb n geldige Tel Nommer in."
        ) ),
        Field::inst( 'PhoneW' )
            ->validator( 'Validate::minMaxLen', array(
                'min' => 11,
                'max' => 11,
                "message" => "Voer asb n geldige Tel Nommer in."
        ) ),
        Field::inst( "Email" )
            ->validator( 'Validate::email', array(
            "message"  => "Voer asb n geldige e-posadres in."
        ) ),
        Field::inst( 'Region' )
            ->validator( Validate::notEmpty() )
            ->options( Options::inst()
                        ->table( 'tblusers' )
                        ->value( 'MemId' )
                        ->label( 'Region' )
                    ),
        Field::inst( 'City' )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'Notes' ),
        Field::inst( 'Dep1Name' ),
        Field::inst( 'Dep1Cell' )->validator( 'Validate::minMaxLen', array(
                'min' => 11,
                'max' => 11,
                "message" => "Voer asb n geldige Tel Nommer in."
        ) ),
        Field::inst( 'Dep2Name' ),
        Field::inst( 'Dep2Cell' )
        ->validator( 'Validate::minMaxLen', array(
                'min' => 11,
                'max' => 11,
                "message" => "Voer asb n geldige Tel Nommer in."
        ) ),
        Field::inst( 'Dep3Name' ),
        Field::inst( 'Dep3Cell' )
        ->validator( 'Validate::minMaxLen', array(
                'min' => 11,
                'max' => 11,
                "message" => "Voer asb n geldige Tel Nommer in."
        ) ),
        Field::inst( 'Dep4Name' ),
        Field::inst( 'Dep4Cell' )
        ->validator( 'Validate::minMaxLen', array(
                'min' => 11,
                'max' => 11,
                "message" => "Voer asb n geldige Tel Nommer in."
        ) ),
        Field::inst( 'Dep5Name' ),
        Field::inst( 'Dep5Cell' )
        ->validator( 'Validate::minMaxLen', array(
                'min' => 11,
                'max' => 11,
                "message" => "Voer asb n geldige Tel Nommer in."
        ) ),
        Field::inst( 'Zip' ),
        Field::inst( 'IDNumb' )
        ->validator( 'Validate::unique', array(
            "message" => "Duplikaatinskrywing gevind."
        ) )
            ->validator( 'Validate::minMaxLen', array(
            'min' => 13,
            'max' => 13,
            "message" => "Voer asb n geldige ID Nommer in."
        ) )
        /*  ->setFormatter( function ( $val ) {
            return encrypt( $val );
        } ) */,
        Field::inst( 'MemId' )
        //->get( true )
    )
    ->process( $_POST )
    ->json();

The debugger is stuck on "gathering data". We have about 10k members in the database loaded via server-side ajax.

allan

Thanks for the code. Could you try changing the last two lines to enable debugging - e.g.:

    ->debug(true)
    ->process( $_POST )
    ->json();

The Options class does a distinct by default, so I'm guessing that MemId and Region are unique for every option that it shown, even if it looks like Region is not unique.

That would make sense since MemId appears to be your primary key for tblusers (from the initialisation line).

What I'm not clear on is why you would want to set Region to be the primary key from the tblusers table (which in this case is your host table). Should the value actually be Region here? That would mean that existing regions would be shown in the list.

Allan

avrrr

Hi Allan

The MemId field is my primary key for this table and Region has multiple values, but even though a certain value might appear numerous times, only distinct values must be present in the drop-down options, hence the distinct option is required. If you could advise how to fix this it would be appreciated, but let me just test and get back to you as I think that might just the mistake I made - the primary key...

avrrr

Hi Allan

It is working now, it was in fact due to the mistake I made in using the primary key as opposed to the field value in the options declaration.

Thanks for your assistance!