Where are the initial query(ies) in the examples?

Sasori7

I've been looking for over a day and I can't lock down where the initial query is for inline editing.

For example, I'm trying to borrow from the serverside example, but I can't find the query that provides the dataset for that table. Can you help?

rf1234

If you look at this example: https://editor.datatables.net/examples/inline-editing/simple.html

Click on "Server script". Then you see the PHP code for the Editor instance that generates the required SQL queries for CRUD at runtime. That's the purpose of the Editor PHP, .NET etc. libraries: Save you from coding individual INSERT (create), SELECT(read), UPDATE and DELETE queries. Editor does this for you.

By using the debug option (->debug(true)) you can see the generated SQL in your browser's console as well.

Sasori7

Thank you for the help. Which file does that query come from? I get that it's generated, but where do I put a limiter on the type of entries to show?
I'm sorry that I'm not 'getting it', but I have to add a where in this to limit the types of students are showing in the table, like where active = 'Yes'

colin

I'm sorry that I'm not 'getting it', but I have to add a where in this to limit the types of students are showing in the table, like where active = 'Yes'

This section of the manual discusses WHERE conditions for the PHP library. There are a few threads with complete examples too, such as this one. Those WHEREs will limit the data that is displayed in the table.

I've been looking for over a day and I can't lock down where the initial query is for inline editing.

I'm not clear what you mean here. Inline editing is entirely on the browser until you click submit, at which point that data is sent to the server for processing.

It might help if you could explain what issue(s) you're trying to solve, and we can recommend approaches for those.

Colin

Sasori7

I suppose I'm mentioning editor because that's what I'm mostly trying to get to work.
Also, when I type 'where' in the search panel of datatables.net, the first example is in the editor manual.
:"This section of the manual discusses WHERE conditions for the PHP library"
sorry, perhaps you forgot the link. I'm still searching; I know, I'm dense.
I have figured out that the 'query' is built in staff.php.
based on the forum link you provided, is it possible that this would work?:
->where(function ($q) use($_SESSION) {
$q->where('active','yes');
})

colin

Sorry, yep, the coffee hadn't kicked in - this is the link I meant: https://editor.datatables.net/manual/php/conditions

Hopefully that'll help you get going, if not. please let us know,

Colin

rf1234

@Sasori7
You can also search the forum for really complex examples of where clauses using Editor. You can virtually do anything. Here is an example from my own coding.

This WHERE clause uses a parameterized subselect, it can do a fulltext search on parsed documents that had been made machine readable before, it can show records that are soft deleted and it is suitable for Data tables serverSide mode or clientSide mode. (For server side the filtering is mostly done on the server.)

->where( function ( $q ) {
    $q  ->where( function ( $r ) { 
        $subReg = " ctr_";
        if ( isset($_SESSION['subRegAnyPage']) ) {
            $subReg = " sub_";
        }
        $r  ->where( 'ctr.id',  
            '( SELECT DISTINCT a.ctr_id
                FROM ctr_has_ctr_govdept a
          INNER JOIN ctr_govdept b                   ON a.ctr_govdept_id = b.id    
          INNER JOIN' . $subReg . 'govdept_has_user_complete c 
                                                     ON b.id = c.ctr_govdept_id 
          INNER JOIN user_has_selected_ctr_govdept d ON b.id = d.ctr_govdept_id    
               WHERE c.user_id = :id 
                 AND d.user_id = :id 
               )', 'IN', false);
        $r  ->bind( ':id', $_SESSION['id'] );
    } );
    if ( isset($_POST['fullTextSearchString']) ) {
        $q  ->where( function ( $r ) { 
            $srchNoSpaces = str_replace( ' ', '', $_POST['fullTextSearchString'] );                
            $srchSpaces = $_POST['fullTextSearchString'];                
            if ($_SESSION['lang'] === 'de') {   
                $r  ->where( 'ctr_search_german.global_search_field', '%'. trim($srchNoSpaces, '"') .'%', 'LIKE' )
                    ->or_where( 'ctr_search_german.global_search_field', '%'. trim($srchSpaces, '"') .'%', 'LIKE' );
            } else {
                $r  ->where( 'ctr_search_english.global_search_field', '%'. trim($srchNoSpaces, '"') .'%', 'LIKE' )
                    ->or_where( 'ctr_search_english.global_search_field', '%'. trim($srchSpaces, '"') .'%', 'LIKE' );
            }
        } );
    }
    if ( isset($_POST['startDateExpired']) ) {
        $q  ->where( function ( $r ) { 
            $r  ->where( 'ctr.expired', 0 )
                ->or_where( function ( $s ) { 
                    $s  ->where( 'ctr.expired', 1 )
                        ->where( 'ctr.end_date', $_POST['startDateExpired'] . ' 00:00:00', '>='); 
                } );
        } );
    }
    if ( isset($_POST['showDeleted']) ) {
        $q  ->where( function ( $r ) { 
            $r  ->where( 'ctr.soft_deleted', 0 )
                ->or_where( 'ctr.soft_deleted', $_POST['showDeleted'] );
        } );
    }
} )

In case you wonder how to send those POST variables to the server: You specify them in the Javascript dt configuration. For the example above I send some POST variables but only if it is the serverSide version of my data table. (full text search on the server and filtering on the server only makes sense for server side data tables; for client side it is mostly better to do this client side.)

ajax: {
    url: 'actions.php?action=tblCtrManagement',
    type: 'POST',
    data: function ( d ) {   
        if ( serverSideDocSearchPage ) {
            d.fullTextSearchString = d.search.value;
            d.startDateExpired = nMonthsAgo( $('#monthsSelect').val() );
            d.showDeleted = showDeleted ? 1 : 0; //send 1 if true and 0 if false
        }
    }
},

Sasori7

Thanks very much!