DataTables access to multiple tables (php + mysqli)

steiniman

Hello,
Usually, certain data is not distributed in one table, but in several tables. As an example here times an order:

Tables:
- Order
-> Order ID
-> Customer ID
-> Payment method
...
- Customer
-> Customer ID
-> First name
-> Surname
...

I would like to take the table order as a representation table. In the Order table, for example, is a field with the customer ID. In the display and in the search, however, the customer ID is not to be displayed or searched, but by the first name and last name, which is specified in the customer table.

I have only found examples that relate to one table only. I have indeed found a way to get the presentation:

    array(  'db'        => 'rgadrid',     
            'dt'        => 3, 
            'formatter' =>  function( $d, $row ) {
                            $sql2   =   'SELECT     `ID`,
                                                    `rg_firma`,
                                                    `rg_titel`,
                                                    `rg_vname`,
                                                    `rg_nname`
                                        FROM        `sd_adre_rg` 
                                        WHERE `ID` = '.$d.';';
                            if (!$result = $mysqli->query($sql2)) {}
                            $row2       =   $result->fetch_assoc();
                            if ($row2['rg_firma'] <> '')
                                {   $str2   =   $row2['rg_firma'];   }
                            else
                                {   if($row2['rg_titel'] <> '')
                                        {   $str2   =  $row2['rg_titel'].' '.$row2['rg_vname'].' '.$row2['rg_nname'];   }
                                    else    
                                        {   $str2   =  $row2['rg_vname'].' '.$row2['rg_nname'];   }
                                }
                            return $str2;
                            }
            
    )

But the search and sorting does not work so, because these access to the source data.
Can you give me an approach, how do I change the search and sorting?

rf1234

Well you could code a join straight away like in this example (see Server Script). Then everything should be working.
https://editor.datatables.net/examples/advanced/joinLinkTable.html

I have attached a pdf with the Data Model that is being used in the example to make it easier to understand.

steiniman

I now have the editor package downloaded and integrated. I do not want to change data, but only display. There are problems with the integration of the first table. You are now trying to read all records (which does not work for 33,000 records), although 25 records per page is selected.
Slowly it begins to annoy.

$('#example').DataTable( {
        dom: 'Bfrtip',
        "language": { "url": "//cdn.datatables.net/plug-ins/9dcbecd42ad/i18n/German.json"},
        "paging":   true,
        "lengthMenu": [[25, 50, 100, 250, -1], [25, 50, 100, 250, "Alle"]],
        "serverSide": true,
        "deferRender": true,
        "ajax": "../bestellung.php",
        columns: [
            { data: "ak2_be_stammdaten.partnerid"},
            { data: "ak2_be_stammdaten.bestellnr"},
            { data: "ak2_be_stammdaten.bestzeit"},
            { data: "ak2_be_stammdaten.rgadrid"}
        ]
} );
} );

include( "DataTables.php");
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

Editor::inst( $db, 'ak2_be_stammdaten' )

    ->field(
        Field::inst( 'ak2_be_stammdaten.partnerid' ),
        Field::inst( 'ak2_be_stammdaten.bestellnr' ),
        Field::inst( 'ak2_be_stammdaten.bestzeit' ),
        Field::inst( 'ak2_be_stammdaten.rgadrid' )
    )
    ->process($_POST)
    ->json();

rf1234

I see you have serverSide processing enabled. 33,000 does not sound to be too much ... Hm, as far as I know you can only limit the number of rows retrieved through a where clause. To enable search datatables retrieves all data and then performs the search. I had a similar question a while ago. I implemented some kind of preselection. Data tables only retrieved the preselected result set which can then be searched with the datatables search function.
For example you could preselect orders with a data range that the user enters first. Then you pass the entered dates on to datatables so that they are used in the where clause of Editor. Let's assume you retrieve e.g. 5,000 rows then. These 5,000 rows can then be searched with the datatables search function.

tangerine

There are examples of joining tables in the Editor docs.
https://editor.datatables.net/examples/advanced/joinLinkTable.html

allan

Change:

"ajax": "../bestellung.php",

to be:

"ajax": {
  url: "../bestellung.php",
  type: 'post'
}

You are using ->process($_POST) in the PHP, so you would need to POST the parameters from the client-side!

Allan

steiniman

Thank you, the problem is solved with it. I have still something new, but I am still testing and will open a new post if necessary.