Some SQL tables

volnistii11 · August 2020

Hello, does anyone have a good example of using an editor with multiple tables (3+, when there is a main table and several side ones that are connected by one field, when there may be a lot of duplicate information, I mean one to many relationship, etc.). I saw an example on the site, but it doesn't quite fit. Thanks for your attention.

tangerine · August 2020

https://editor.datatables.net/examples/simple/join.html

volnistii11 · August 2020

editing does not work for me with fields that refer to the applicability table, everything is fine with the main table fields (i looked at the sql logs and there the update query is not executed, when there is a change in the applicability table, in the main table is executed)

index.php

        <body>
        <div align="center">
            <h1>Картотека архива(таблица)</h1>
            <form action="index.php"><button>Обновить</button></form>
            <form action="../../Service_program.php"><button>Выход</button></form>      
            <br><br>
        <table id='empTable' class='display dataTable'>

          <thead>
            <tr>
                <th><b>Раздел</b></th>

                <th><b>Наименование</b></th>
                <th><b>Номер документа</b></th>

                <th><b>Применяемость<br>Дата</b></th>
                <th><b>Применяемость<br>Обозначение</b></th>
                <th><b>Применяемость<br>Шифр индекса изделия</b></th>




            </tr>
            </thead>
        <tfoot>
            <tr>
                <th><b>Раздел</b></th>

                <th><b>Наименование</b></th>
                <th><b>Номер документа</b></th>

                <th><b>Применяемость<br>Дата</b></th>
                <th><b>Применяемость<br>Обозначение</b></th>
                <th><b>Применяемость<br>Шифр индекса изделия</b></th>




            </tr>
            </tfoot>

        </table>
        </body>
        </html>
        <script>
        var editor;


        $(document).ready(function() {

        editor = new $.fn.dataTable.Editor( {
            ajax: {
            "url" : "query.php",
            },
            table: "#empTable",

            fields: [ 
                {
                    label: "Раздел",
                    name: "main.SelectionDescription",



                },


                {
                    label: "Наименование:",
                    name: "main.DocumentName",
                    type:  "textarea"
                }, 
                {
                    type:  "textarea",
                    label: "Номер документа:",
                    name: "main.DocumentNumber"
                }, 

                {
                    label: "Применяемость Дата:",
                    name: "applicability.DateApplicability"
                },
                {
                    type:  "textarea",
                    label: "Применяемость Обозначение:",
                    name: "applicability.DesignationApplicability"
                },
                {
                    type:  "textarea",
                    label: "Применяемость Шифр индекса изделия:",
                    name: "applicability.CypherApplicability"
                },





            ]
        } );

        // Activate an inline edit on click of a table cell
        $('#empTable').on( 'click', 'tbody td', function (e) {
            editor.inline( this, {

            buttons: { label: '&gt;', fn: function () { this.submit(); } }
            }           );
        } );







       $('#empTable').DataTable({

          'processing': true,
          'serverSide': true,
          "lengthMenu": [[10, 25, 50, 1000000000], [10, 25, 50, "All"]],
          "pageLength": 25,
            dom: 'lBrtip',
            buttons: [
                'excelHtml5',   
            ],
            select: true,

          'serverMethod': 'post',
          'ajax': {
                url:'query.php',
                 type: 'POST'

          },
          'columns': [

             { data: 'main.SelectionDescription', className: "dt-body-center" },

             { data: 'main.DocumentName' },
             { data: 'main.DocumentNumber' },


             { data: 'applicability.DateApplicability' },
             { data: 'applicability.DesignationApplicability' },
             { data: 'applicability.CypherApplicability' },



          ],



       });
    });
        </script>

query.php

    <?php

    // DataTables PHP library
    include( "DataTables/Editor-PHP-1.9.4/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;


    /*
     * Example PHP implementation used for the join.html example
     */
    Editor::inst( $db, 'ineum_archive_fileindex_main' )
        ->field(
            Field::inst( 'main.SelectionDescription' ),
            Field::inst( 'main.DocumentName' ),
            Field::inst( 'main.DocumentNumber' ),


            Field::inst( 'applicability.DateApplicability' ),
            Field::inst( 'ineumapplicability.DesignationApplicability' ),
            Field::inst( 'applicability.CypherApplicability' )



        )
        ->leftJoin( 'applicability', 'applicability.DocumentNumber', '=', 'main.DocumentNumber' )
        ->process($_POST)
        ->json();

allan · August 2020

Perhaps you can link to a test case showing the issue please? I'm not entirely clear what the problem is.

Also you've got invalid PHP in your query.php file there:

Field::inst( main.DocumentNumber' ),
// should be
Field::inst( 'main.DocumentNumber' ),

Allan

volnistii11 · August 2020

There is no way to provide a test case(since everything is on the local server) = (
I'll try to explain it again.
If I change the data in the fields that refer to the main table in the sql database, then everything works correctly.
If I change the data in the fields that relate to the applicability table in the sql database, then there is no change.
That is, everything is fine with the main table, but everything is bad with the table that is assigned to it.

allan · August 2020

In what way is it bad? Do you get SQL errors, or something else?

Another thing to do is immediately before the ->process($_POST) add ->debug(true), then use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

Allan

volnistii11 · August 2020

Hi. This?
https://debug.datatables.net/inevov

allan · August 2020

Thanks for the debug trace - although I've got to confess, I'm not seeing an issue there. You have 25 rows and 20 columns of data. It appears to be getting rendered into the HTML correctly.

Was that trace taken in the "bad" state, or the "good" one? I'm still not understanding what is actually wrong with the table when it is "bad".

Allan

volnistii11 · August 2020

Hello.
Everything is displayed correctly (that is, the select query in the database is working correctly).
I can change the data in the first 17 columns correctly (the update request to the "main" table works correctly, it is in the logs)
I cannot change the data in the last three columns, nothing happens when I send new data (there is no update request to the "applicability" table in the logs.

allan · August 2020

Can you show me your full Javascript for the DataTable and Editor then please? If you have that many forms and columns, the code shown above must be truncated.

Allan

volnistii11 · August 2020

Hi, allan.What I have given above is my complete code.

volnistii11 · August 2020

Maybe there are some requirements for sql tables in the database, that it worked correctly?

colin · August 2020

You said there's 17 columns, but in the code above, as Allan said, there's only 6, so there's an inconsistency somewhere, that's why Allan was asking for all the code.

Colin

volnistii11 · August 2020

Oh well, I just trimmed it down a bit to make it easier to watch, here's the full one.

index.php

        <!DOCTYPE html>
            <html>
        <head>
        <title>Картотека архива</title>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <meta http-equiv="Content-Language" content="ru">
        <link rel="stylesheet" type="text/css" href="../DataTables/datatables.min.css">
        <link rel="stylesheet" type="text/css" href="../DataTables/Editor-PHP-1.9.4/css/editor.dataTables.min.css">
        <script type="text/javascript" charset="utf8" src="../DataTables/datatables.min.js"></script>
        <script type="text/javascript" src="../DataTables/Editor-PHP-1.9.4/js/dataTables.editor.min.js"></script>

        </head>
        <body>
        <div align="center">
            <h1>Картотека архива(таблица)</h1>
            <form action="index.php"><button>Обновить</button></form>
            <form action="../../Service_program.php"><button>Выход</button></form>      
            <br><br>
        <table id='empTable' class='display dataTable'>

          <thead>
            <tr>
                <th><b>Раздел</b></th>

                <th><b>Наименование</b></th>
                <th><b>Номер документа</b></th>
                <th><b>Подразделение</b></th>
                <th><b>Подл. на предпр.</b></th>
                <th><b>Издание</b></th>
                <th><b>Светок</b></th>
                <th><b>Формат</b></th>
                <th><b>Подлинник<br>Инвентарный номер</b></th>
                <th><b>Подлинник<br>Дата поступления</b></th>
                <th><b>Подлинник<br>Кол. листов</b></th>
                <th><b>Дубликат<br>Инвентарный номер</b></th>
                <th><b>Дубликат<br>Дата поступления</b></th>
                <th><b>Дубликат<br>Кол. листов</b></th>
                <th><b>ТК<br>Инвентарный номер</b></th>
                <th><b>ТК<br>Дата поступления</b></th>
                <th><b>ТК<br>Кол. листов</b></th>

                <th><b>Применяемость<br>Дата</b></th>
                <th><b>Применяемость<br>Обозначение</b></th>
                <th><b>Применяемость<br>Шифр индекса изделия</b></th>




            </tr>
            </thead>
        <tfoot>
            <tr>
                <th><b>Раздел</b></th>

                <th><b>Наименование</b></th>
                <th><b>Номер документа</b></th>
                <th><b>Подразделение</b></th>
                <th><b>Подл. на предпр.</b></th>
                <th><b>Издание</b></th>
                <th><b>Светок</b></th>
                <th><b>Формат</b></th>
                <th><b>Подлинник<br>Инвентарный номер</b></th>
                <th><b>Подлинник<br>Дата поступления</b></th>
                <th><b>Подлинник<br>Кол. листов</b></th>
                <th><b>Дубликат<br>Инвентарный номер</b></th>
                <th><b>Дубликат<br>Дата поступления</b></th>
                <th><b>Дубликат<br>Кол. листов</b></th>
                <th><b>ТК<br>Инвентарный номер</b></th>
                <th><b>ТК<br>Дата поступления</b></th>
                <th><b>ТК<br>Кол. листов</b></th>

                <th><b>Применяемость<br>Дата</b></th>
                <th><b>Применяемость<br>Обозначение</b></th>
                <th><b>Применяемость<br>Шифр индекса изделия</b></th>




            </tr>
            </tfoot>

        </table>
        </body>
        </html>
        <script>
        var editor;


        $(document).ready(function() {

        editor = new $.fn.dataTable.Editor( {
            ajax: {
            "url" : "query.php",
            },
            table: "#empTable",

            fields: [ 
                {
                    label: "Раздел",
                    name: "ineum_archive_fileindex_main.SelectionDescription",

                },


                {
                    label: "Наименование:",
                    name: "ineum_archive_fileindex_main.DocumentName",
                    type:  "textarea"
                }, 
                {
                    type:  "textarea",
                    label: "Номер документа:",
                    name: "ineum_archive_fileindex_main.DocumentNumber"
                }, 
                {
                    label: "Подразделение:",
                    name: "ineum_archive_fileindex_main.Subdivision"
                }, 
                {
                    label: "Подл. на предпр.:",
                    name: "ineum_archive_fileindex_main.ContinueOnCompany"
                }, 
                {
                    label: "Издание:",
                    name: "ineum_archive_fileindex_main.Edition"
                }, 
                {
                    label: "Светок:",
                    name: "ineum_archive_fileindex_main.Svetok"
                }, 
                {
                    label: "Формат:",
                    name: "ineum_archive_fileindex_main.DocumnetFormat"
                }, 
                {
                    label: "Подлинник Инвентарный номер:",
                    name: "ineum_archive_fileindex_main.OriginalInventoryNumber"
                }, 
                {
                    label: "Подлинник Дата поступления:",
                    name: "ineum_archive_fileindex_main.OriginalDataSupplay"
                }, 
                {
                    label: "Подлинник Кол. листов:",
                    name: "ineum_archive_fileindex_main.OriginalNumberOfSheets"
                }, 
                {
                    label: "Дубликат Инвентарный номер:",
                    name: "ineum_archive_fileindex_main.DuplicationInventoryNumber",
                }, 
                {
                    label: "Дубликат Дата поступления:",
                    name: "ineum_archive_fileindex_main.DuplicationDataSupplay",
                },
                {
                    label: "Дубликат Кол. листов:",
                    name: "ineum_archive_fileindex_main.DuplicationNumberOfSheets",
                },
                {
                    label: "ТК Инвентарный номер:",
                    name: "ineum_archive_fileindex_main.TKInventoryNumber",
                }, 
                {
                    label: "ТК Дата поступления:",
                    name: "ineum_archive_fileindex_main.TKDataSupplay",
                },
                {
                    label: "ТК Кол. листов:",
                    name: "ineum_archive_fileindex_main.TKNumberOfSheets",
                },


                {
                    label: "Применяемость Дата:",
                    name: "ineum_archive_fileindex_applicability.DateApplicability"
                },
                {
                    type:  "textarea",
                    label: "Применяемость Обозначение:",
                    name: "ineum_archive_fileindex_applicability.DesignationApplicability"
                },
                {
                    type:  "textarea",
                    label: "Применяемость Шифр индекса изделия:",
                    name: "ineum_archive_fileindex_applicability.CypherApplicability"
                },





            ]
        } );

        // Activate an inline edit on click of a table cell
        $('#empTable').on( 'click', 'tbody td', function (e) {
            editor.inline( this, {

            buttons: { label: '&gt;', fn: function () { this.submit(); } }
            }           );
        } );







       $('#empTable').DataTable({

          'processing': true,
          'serverSide': true,
          "lengthMenu": [[10, 25, 50, 1000000000], [10, 25, 50, "All"]],
          "pageLength": 25,
            dom: 'lBrtip',
            buttons: [
                'excelHtml5',   
            ],
            select: true,

            language: {
                          "processing": "Подождите...",
                          "search": "Поиск:",
                          "lengthMenu": "Показать _MENU_ записей",
                          "info": "Записи с _START_ до _END_ из _TOTAL_ записей",
                          "infoEmpty": "Записи с 0 до 0 из 0 записей",
                          "infoFiltered": "(отфильтровано из _MAX_ записей)",
                          "infoPostFix": "",
                          "loadingRecords": "Загрузка записей...",
                          "zeroRecords": "Записи отсутствуют.",
                          "emptyTable": "В таблице отсутствуют данные",
                          "paginate": {
                            "first": "Первая",
                            "previous": "Предыдущая",
                            "next": "Следующая",
                            "last": "Последняя"
                          },
                            "buttons":
                            {
                                "create": "Создать",
                                "edit": "Изменить",
                                "remove": "Удалить",

                            },
                            "Update": "Изменить",
                          "aria": {
                            "sortAscending": ": активировать для сортировки столбца по возрастанию",
                            "sortDescending": ": активировать для сортировки столбца по убыванию"
                          },
                          "select": {
                            "rows": {
                              "_": "Выбрано записей: %d",
                              "0": "Кликните по записи для выбора",
                              "1": "Выбрана одна запись"
                            }
                          }
                        },
          'serverMethod': 'post',
          'ajax': {
                url:'query.php',
                 type: 'POST'

          },
          'columns': [

             { data: 'ineum_archive_fileindex_main.SelectionDescription', className: "dt-body-center" },

             { data: 'ineum_archive_fileindex_main.DocumentName' },
             { data: 'ineum_archive_fileindex_main.DocumentNumber' },
             { data: 'ineum_archive_fileindex_main.Subdivision' },
             { data: 'ineum_archive_fileindex_main.ContinueOnCompany' },
             { data: 'ineum_archive_fileindex_main.Edition' },
             { data: 'ineum_archive_fileindex_main.Svetok' },
             { data: 'ineum_archive_fileindex_main.DocumnetFormat' },
             { data: 'ineum_archive_fileindex_main.OriginalInventoryNumber' },
             { data: 'ineum_archive_fileindex_main.OriginalDataSupplay' },
             { data: 'ineum_archive_fileindex_main.OriginalNumberOfSheets' },
             { data: 'ineum_archive_fileindex_main.DuplicationInventoryNumber' },
             { data: 'ineum_archive_fileindex_main.DuplicationDataSupplay' },
             { data: 'ineum_archive_fileindex_main.DuplicationNumberOfSheets' },
             { data: 'ineum_archive_fileindex_main.TKInventoryNumber' },
             { data: 'ineum_archive_fileindex_main.TKDataSupplay' },
             { data: 'ineum_archive_fileindex_main.TKNumberOfSheets' },

             { data: 'ineum_archive_fileindex_applicability.DateApplicability' },
             { data: 'ineum_archive_fileindex_applicability.DesignationApplicability' },
             { data: 'ineum_archive_fileindex_applicability.CypherApplicability' },



          ],


          initComplete: function () {

                $('#empTable tfoot th').each( function () {
                        var title = $(this).text();
                        $(this).html( '<input type="text" placeholder="Поиск '+title+'" />' );
                    } );

                var r = $('#empTable tfoot tr');
                  r.find('th').each(function(){
                    $(this).css('padding', 8);
                  });
                  $('#empTable thead').append(r);
                  $('#search_0').css('text-align', 'center');
                // Apply the search
                this.api().columns().every( function () {
                    var that = this;

                    $( 'input', this.footer() ).on( 'keyup change clear', function () {
                        if ( that.search() !== this.value ) {
                            that
                                .search( this.value )
                                .draw();
                        }
                    } );
                } );
            }

       });
    });
        </script>

volnistii11 · August 2020

query.php

    <?php

    // DataTables PHP library
    include( "DataTables/Editor-PHP-1.9.4/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;


    /*
     * Example PHP implementation used for the join.html example
     */
    Editor::inst( $db, 'ineum_archive_fileindex_main' )
        ->field(
            Field::inst( 'ineum_archive_fileindex_main.SelectionDescription' ),
            Field::inst( 'ineum_archive_fileindex_main.DocumentName' ),
            Field::inst( 'ineum_archive_fileindex_main.DocumentNumber' ),
            Field::inst( 'ineum_archive_fileindex_main.Subdivision' ),
            Field::inst( 'ineum_archive_fileindex_main.ContinueOnCompany' ),
            Field::inst( 'ineum_archive_fileindex_main.Edition' ),
            Field::inst( 'ineum_archive_fileindex_main.Svetok' ),
            Field::inst( 'ineum_archive_fileindex_main.DocumnetFormat' ),
            Field::inst( 'ineum_archive_fileindex_main.OriginalInventoryNumber' ),
            Field::inst( 'ineum_archive_fileindex_main.OriginalDataSupplay' ),
            Field::inst( 'ineum_archive_fileindex_main.OriginalNumberOfSheets' ),
            Field::inst( 'ineum_archive_fileindex_main.DuplicationInventoryNumber' ),
            Field::inst( 'ineum_archive_fileindex_main.DuplicationDataSupplay' ),
            Field::inst( 'ineum_archive_fileindex_main.DuplicationNumberOfSheets' ),
            Field::inst( 'ineum_archive_fileindex_main.TKInventoryNumber' ),
            Field::inst( 'ineum_archive_fileindex_main.TKDataSupplay' ),
            Field::inst( 'ineum_archive_fileindex_main.TKNumberOfSheets' ),


            Field::inst( 'ineum_archive_fileindex_applicability.DateApplicability' ),
            Field::inst( 'ineum_archive_fileindex_applicability.DesignationApplicability' ),
            Field::inst( 'ineum_archive_fileindex_applicability.CypherApplicability' )



        )
        ->leftJoin( 'ineum_archive_fileindex_applicability', 'ineum_archive_fileindex_applicability.DocumentNumber', '=', 'ineum_archive_fileindex_main.DocumentNumber' )
        ->debug(true)
        ->process($_POST)
        ->json();

allan · August 2020

Thank you. So the difference between the last three columns and the earlier ones is that the last three are on the ineum_archive_fileindex_applicability table. All the others are on your main table.

Generally with a join, you would have a single field that would change which row the host links to. Is that not what you want? You want to be able to change the data in the linked table, and not be able to change the link?

Allan

volnistii11 · September 2020

Hello, the problem is that the data that is in the ineum_archive_fileindex_applicability table does not change when I try to change it.

volnistii11 · September 2020

Maybe you have more examples of using several tables, so that I can understand by analogy where I went wrong? It is desirable that one record of the main table corresponds to several records of the secondary table, etc. And then in your example, according to the link below, the number of records 1 to 1 is implemented, also, as I understand it, you use a "transit field" there.
https://editor.datatables.net/examples/simple/join.html

allan · September 2020

If you want to edit the data in the joined table (which is relatively unusual - normally just the key to the joined table is updated) you need to include the primary key of the joined table in your field list as well. It also needs to be in the editor field list client-side as a hidden field.

Allan

volnistii11 · September 2020

Hello, could you please give a small example of how this will look or using my code example? I add a hidden field to html, otherwise I need to write it to javascript in the query itself (query.php), otherwise something doesn't work out for me. Thanks for your attention.

allan · September 2020

In the PHP:

Field::inst('ineum_archive_fileindex_applicability.DocumentNumber')->set(false)`

And in the Javascript Editor fields:

{
  name: 'ineum_archive_fileindex_applicability.DocumentNumber',
  type: 'hidden',
}

Hopefully that should do it.

I would urge caution using this method though! If you have other rows which also reference that same DocumentNumber you would need to reload the table to get the latest joined data for them.

Allan

volnistii11 · September 2020

Not working = (
And how do you do when you need all the fields of all tables (both primary and secondary) to be changeable?
Maybe I don’t understand something, but it seems to me that this is a very common need or not?

allan · September 2020

I might be wrong, but it isn't that common in my experience. It does pop up now and then, but generally, when you want to edit in a different table it will (or can) effect multiple rows in the host table. Thus normally you would edit the different table separately.

Since you have debug enabled, could you show me the JSON response from the server when you send the edit request?

Thanks,
Allan

Some SQL tables

Some SQL tables

Answers

Howdy, Stranger!

Categories

DataTables

Some SQL tables

Some SQL tables

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables