How to create a dropdown with datatables, editor and mysql/php on server-side?

valois

Hi,

I can't figure out how to create a html <select> box with options using php and mysql.
I'm using Datatable 1.10.17 and Editor 1.7.4

Here is an example on how to create a <select>-box with datatable: https://datatables.net/examples/api/form.html (see the last column "office").

This is easy but no editor and no sql database is envolved. How can I create such a dropdown box as in the example above using mysql, php-library and the editor.

Following I describe the two tables "products" and "origin". The "product_origin"-column contains the id of the selected origin-row.

table: products
column: id, product_name, product_origin

table: origin
columns: id, origin

Example for products:
1, computer, 3
2, printer, 1
3, camera, 4

Example origin:

1, china
2, europe
3, india
4, japan

Any help apreciated.

Bindrid2

In my case, I use ajax before I create the datatable or editor to get the needed data, on success, I create the datatable and editor.

valois

Thanks for your answer.

What I found out so far is, that I have to do a leftJoin and that I have to use options:

    Field::inst( 'products.product_origin' )
    ->options(Options::inst()
        ->table('origin')
        ->value('id')
        ->label('origin_name')
        ),
    Field::inst('origin.origin_name')
    ->options(Options::inst()
        ->table('origin')
        ->value('id')
        ->label('name')
        )
    ->leftJoin('origin', 'origin.id', '=', 'products.product_origin)
        ->process( $_POST )
    ->json();

And then I initialize the Datatable:

        $('#productsHTMLTable').DataTable( {
            dom: "Bfrtip",
            ajax: "https://<some-url>products.php",
            columns: [
            ...
            ...
            ...
    { data: "origin.name"},
            ...
            ...
            ...

}

What works is that I get the origin.name and it will be displayed in my <tabel>. But what I couldn't find out is how to get a list of all origin.names and how I can present the elements of the list as <option>-Tags in a <select>-Tag. Any hint or advice highly appreciated. Thank.

allan

Hi,

Are you using Editor to show the editing form and that is where you want to show the select elements? If so, can you show me your Editor Javascript code? Could you also show me your full PHP? I would imagine you want to change the value on the host table rather than the origin.origin_name parameter (although you will need that to display in the table).

Also, have a look at this example which might be useful.

Allan