Populating the table with RAWSQL query

Populating the table with RAWSQL query

Khalid TeliKhalid Teli Posts: 32Questions: 8Answers: 0
edited July 29 in Free community support

I am using the raw sql query below to populate some columns in the datatble which is already created using Editor data-tables. I am not sure if I am doing it correctly. I believe that the** if ( ! isset( $_POST['action'] ) ) {** is not working as it should work.

How should the JSON response look like?

In my case I would like JSON to contain all the data 1) From RAW sql to populate few columns and 2) From Editor to populate renaming columns (else condition).

p.s i tried to remove the if condition so i can read all the data in one JSON and then handle them differently on client side but it is showing me** Error: Parse error**

**// if ( ! isset( $_POST['action'] ) ) {
   $rawquery = "SELECT start  AS time 
                FROM contract_bal";
   $data = $db->sql( $rawquery )->fetchAll();
   echo json_encode( array(
      'data' => $data
   ) );
 
// }else{
......editor stuff
}**    

and on the client side i included this:

**columns[
......
 
                     { data: "time" }
.....
]**

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 52,290Questions: 1Answers: 7,966 Site admin

    What is the response from the server? The details in this tech note will show you how to get that.

    Allan

  • Khalid TeliKhalid Teli Posts: 32Questions: 8Answers: 0
    edited July 30

    Thank you @allan

    1) when i don't use the if condition , the output looks like below, it reads both from RAWSQL ans EDITOR and the error it shows id parse error on line 9 in bold

      {
            "data": [{
                "starty": "1234"
            }, {
                "starty": "1234"
            }, {
                "starty": "1234"
            }]
       ** } {**
            "data": [{
                    "DT_RowId": "row_100758",
                    "contract_bal": {
                        "contract_id": "100758",
                        "member_name": "Foodservice",
                        "opt_one": "1190",
                        "opt_two": "0",
                        "opt_three": "0",
                        "opt_four": "0",
                        "opt_five": "0",
                        "opt_six": "0",
                        "monthly_input": "5231",
                        "start_date": "2019-10-01",
                        "end_date": "2020-09-30",
                        "contractinfo_id_fk": "4",
                        "product_code_fk2": "D230"
                    },
                    "contract_info": {
                        "contract_prod": "Tomatoes"
                    },
                    "products": {
                        "product_name": " Chopped Tomatoes",
                        "supplier_id_fk": "1068"
                    },
                    "supplier": {
                        "supplier_name": "Martin"
                    }
                }
            }],
        "options": {
            "contract_bal.contractinfo_id_fk": [{
                "label": "Freeze_Chill_Fries",
                "value": "26"
            }, {
                "label": "Fries",
                "value": "25"
            } {
                "label": "Tomatoes",
                "value": "14"
            }],
            "contract_bal.product_code_fk2": [{
                "label": "  Olive Oil      ",
                "value": "9260"
            } {
                "label": " Seafood",
                "value": "1123"
                                            
            }]
        },
        "files": [],
        "searchPanes": {
            "options": []
        },
        "draw": 1,
        "recordsTotal": "177",
        "recordsFiltered": "177"
        }
    

    2) when i put the if condition **~~(if ( isset( $_POST['action'] ) ) {)~~** it just, i guess it takes both RAWSQL ans Editor as post action and error shows unknown field for "starty" and for **~~(if ( ! isset( $_POST['action'] ) ) {)~~** there is no response

    3) Then I tried just using the RAW SQL query on server-side and successfully populate the datatatable but the problem is that there is no searching facility. I have to disable the server-side from client side in order to search the datatable

  • allanallan Posts: 52,290Questions: 1Answers: 7,966 Site admin

    EDITOR and the error it shows id parse error on line 9 in bold

    That looks like it is sending two different JSON objects back. That isn't valid JSON.

    Also the empty return from 2 isn't valid JSON either, hence the error you are seeing. So there are some issues with the server-side script. Can you show me what you have please? Are you using our PHP libraries?

    Allan

  • Khalid TeliKhalid Teli Posts: 32Questions: 8Answers: 0

    @allan Thank you.
    yes, I use PHP libraries.
    you are right, it returns two JSON objects which are concatenated and therefore resultant JSON is not a valid JSON.

    Please find my server-side script below

        <?php
    
        // DataTables PHP library
        include( "../lib/DataTables.php" );
    
        use
            DataTables\Editor,
            DataTables\Editor\Field,
            DataTables\Editor\Format,
            DataTables\Editor\Mjoin,
            DataTables\Editor\Options,
            DataTables\Editor\Upload,
            DataTables\Editor\Validate,
            DataTables\Editor\ValidateOptions,
            DataTables\Database,
               DataTables\Database\Query,
               DataTables\Database\Result;
    
    
        if ( isset(! $_POST['action'] ) ) {
          $rawquery = "SELECT start as starty FROM contract_bal";
           $data = $db->sql( $rawquery )->fetchAll();
        echo json_encode( [
          'data' => $data
        ] );
        }
    
        else {
    
        // Build our Editor instance and process the data coming from _POST
        Editor::inst( $db, 'contract_bal', 'contract_id' )
            ->fields(
                // Field::inst( 'contract_id' )
          //           ->set( false ),
                Field::inst( 'contract_bal.contract_id' )
                    ->validator( Validate::notEmpty( ValidateOptions::inst()
                        ->message( 'A Contract ID is required' )    
                    ) ),
                Field::inst( 'contract_bal.member_name' )
                    ->validator( Validate::notEmpty( ValidateOptions::inst()
                        ->message( 'A member name is required' )    
                    ) ),
                Field::inst( 'contract_bal.opt_one' ),
                Field::inst( 'contract_bal.opt_two' ),
                Field::inst( 'contract_bal.opt_three' ),
                Field::inst( 'contract_bal.opt_four' ),
                Field::inst( 'contract_bal.opt_five' ),
                Field::inst( 'contract_bal.opt_six' ),
                Field::inst( 'contract_bal.monthly_input' ),
                Field::inst( 'contract_bal.start_date' )
                    ->validator( Validate::dateFormat( 'Y-m-d' ) )
                    ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
                    ->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),
                Field::inst( 'contract_bal.end_date' )
                    ->validator( Validate::dateFormat( 'Y-m-d' ) )
                    ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
                    ->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),
    
         Field::inst( 'contract_bal.contractinfo_id_fk' )
                    ->options( Options::inst()
                        ->table( 'contract_info' )
                        ->value( 'contract_id' )
                        ->label( 'contract_prod' )
                    )
                    ->validator( Validate::dbValues() ),
    
         Field::inst( 'contract_info.contract_prod' ),
    
    
    
         Field::inst( 'contract_bal.product_code_fk2' )
                    ->options( Options::inst()
                        ->table( 'products' )
                        ->value( 'product_code' )
                        ->label( 'product_name' )
                    )
                    ->validator( Validate::dbValues() ),
    
         Field::inst( 'products.product_name' ),
    
          Field::inst( 'products.supplier_id_fk' )
                    ->options( Options::inst()
                        ->table( 'supplier' )
                        ->value( 'supplier_id' )
                        ->label( 'supplier_name' )
                    )
                    ->validator( Validate::dbValues() ),
                     Field::inst( 'supplier.supplier_name' ),
    
         // Field::inst( 'start' )
    
    
    
            )
    
    
             ->leftJoin( 'contract_info', 'contract_info.contract_id', '=', 'contract_bal.contractinfo_id_fk' )
              ->leftJoin( 'products', 'products.product_code', '=', 'contract_bal.product_code_fk2' )
                ->leftJoin( 'supplier', 'supplier.supplier_id', '=', 'products.supplier_id_fk' )
    
    
            ->process( $_POST )
            ->json();
          }
    
  • allanallan Posts: 52,290Questions: 1Answers: 7,966 Site admin

    It is as if the if / else is executing both blocks! I think this line is wrong though:

    if ( isset(! $_POST['action'] ) ) {
    

    It should be:

    if ( ! isset($_POST['action'] ) ) {
    

    Allan

  • Khalid TeliKhalid Teli Posts: 32Questions: 8Answers: 0
    edited July 31

    @allan
    Thank you. That was a silly mistake from my side. However, I changed this and nothing changed.

    It seems when it is not set ( if ( ! isset($_POST['action'] ) ) { ) it ready the data from raw sql query in if statement and doesn't go out of if loop to else statement.

    please look at the error below:

    **DataTables warning: table id=contracts - Requested unknown parameter 'contract_bal.contract_id' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4**

    Just in case you want to see how my 'columns' look from client side

                        "columns": [
    
    
                        { data: "contract_bal.contract_id" },
                        { data: "contract_bal.member_name"},
                        { data: "contract_bal.opt_one" },
                       { data: "contract_bal.opt_two" },
                                    { data: "contract_bal.opt_three" },
                                    { data: "contract_bal.opt_four" },
                                    { data: "contract_bal.opt_five" },
                                    { data: "contract_bal.opt_six" },
                                    { data: "contract_bal.monthly_input" },
                                    { data: "contract_bal.start_date" },
                                    { data: "contract_bal.end_date" },
                                    { data: "contract_info.contract_prod" },
                                    { data: "products.product_name" },
                                    { data: null,
                                     render: function (data, type, row)
                                        {
                                          //  var numFormat = $.fn.dataTable.render.number( ",", ".", 0 ).display;
                                            var open_balance = parseInt(data.contract_bal.opt_one) + parseInt(data.contract_bal.opt_two)+
                                            parseInt(data.contract_bal.opt_three) + parseInt(data.contract_bal.opt_four)+
                                            parseInt(data.contract_bal.opt_five) + parseInt(data.contract_bal.opt_six);
    
                                            return open_balance;
                                        }},
    
                                    { data: null,
                                     render: function (data, type, row)
                                        {
                                          //  var numFormat = $.fn.dataTable.render.number( ",", ".", 0 ).display;
                                            var remaining_balance = (parseInt(data.contract_bal.opt_one) + parseInt(data.contract_bal.opt_two)+
                                            parseInt(data.contract_bal.opt_three) + parseInt(data.contract_bal.opt_four)+
                                            parseInt(data.contract_bal.opt_five) + parseInt(data.contract_bal.opt_six)) - 
                                            parseInt(data.contract_bal.monthly_input);
    
                                            return remaining_balance;
                                        }},
    
    
                                    { data: null,
                                     render: function (data, type, row)
                                        {
                                          //  var numFormat = $.fn.dataTable.render.number( ",", ".", 0 ).display;
                                            var per_remaining_balance = ((((parseInt(data.contract_bal.opt_one) + parseInt(data.contract_bal.opt_two)+
                                            parseInt(data.contract_bal.opt_three) + parseInt(data.contract_bal.opt_four)+
                                            parseInt(data.contract_bal.opt_five) + parseInt(data.contract_bal.opt_six)) - 
                                            parseInt(data.contract_bal.monthly_input)) / 
    
                                            (parseInt(data.contract_bal.opt_one) + parseInt(data.contract_bal.opt_two)+
                                            parseInt(data.contract_bal.opt_three) + parseInt(data.contract_bal.opt_four)+
                                            parseInt(data.contract_bal.opt_five) + parseInt(data.contract_bal.opt_six))) * 100 )
    
    
                                            return Math.round(per_remaining_balance)+'%';
    
                                        }},
    
                                    { data: null,
                                     render: function (data, type, row)
    
                                         {
    
                                          var start_date = new Date(row.contract_bal.start_date);
                                          var end_date = new Date(row.contract_bal.end_date);
                                          var today = new Date() ;
    
                                          var sec_start = start_date.getTime();
                                          var sec_end = end_date.getTime();
                                          var sec_today = today.getTime();
    
    
                                          var total = sec_end - sec_start;
                                           var elapsed = sec_today - sec_start;
                                           var percent = Math.round(((total-elapsed)/total * 100)) + '%';
    
    
    
                                            return percent ;
    
    
    
                                        }},
                                    { data: null,
                                                render: function (data, type, row)
                                        {
                                          //  var numFormat = $.fn.dataTable.render.number( ",", ".", 0 ).display;
    
                                          //opening balance - remaining balance divided by no of months 
                                            var monthly_avg = 
    
                                            (parseInt(data.contract_bal.opt_one) + parseInt(data.contract_bal.opt_two)+
                                            parseInt(data.contract_bal.opt_three) + parseInt(data.contract_bal.opt_four)+
                                            parseInt(data.contract_bal.opt_five) + parseInt(data.contract_bal.opt_six) - 
    
    
                                           ( (parseInt(data.contract_bal.opt_one) + parseInt(data.contract_bal.opt_two)+
                                            parseInt(data.contract_bal.opt_three) + parseInt(data.contract_bal.opt_four)+
                                            parseInt(data.contract_bal.opt_five) + parseInt(data.contract_bal.opt_six) - 
                                            parseInt(data.contract_bal.monthly_input))))
    
    
    
    
                                            return Math.round((monthly_avg)/12);
    
                                        }},
    
                                        { data: "supplier.supplier_name" },
    
                                        { data: "starty" }
    
    
    
    
                                ],
    
  • allanallan Posts: 52,290Questions: 1Answers: 7,966 Site admin

    Can you show me the corrected PHP code? In the if statement above you are only selecting the start column and returning that. So yes, if you tried to tell the DataTable to display anything else, then it would error with the message you are seeing.

    Allan

  • Khalid TeliKhalid Teli Posts: 32Questions: 8Answers: 0
    edited August 1

    @allan Thank you. I am posting my code below. What I am trying to do is , populate one of the columns in datatable with Raw sql query ('SELECT start as starty from contract_bal') and the rest of columns to be filled by Editor instance

    So, is there a way to populate one datatble with both raw sql and editor instance at same time? or in other words can we populate editor with rawsql?

        <?php
    
        // DataTables PHP library
        include( "../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;
    
    
        if ( ! isset($_POST['action'] ) ) {
          $rawquery = "SELECT start as starty from contract_bal";
           $data = $db->sql( $rawquery )->fetchAll();
        echo json_encode( [
          'data' => $data
        ] );
        }
    
        else {
    
        // Build our Editor instance and process the data coming from _POST
        Editor::inst( $db, 'contract_bal', 'contract_id' )
            ->fields(
                // Field::inst( 'contract_id' )
          //           ->set( false ),
                Field::inst( 'contract_bal.contract_id' )
                    ->validator( Validate::notEmpty( ValidateOptions::inst()
                        ->message( 'A Contract ID is required' )    
                    ) ),
                Field::inst( 'contract_bal.member_name' )
                    ->validator( Validate::notEmpty( ValidateOptions::inst()
                        ->message( 'A member name is required' )    
                    ) ),
                Field::inst( 'contract_bal.opt_one' ),
                Field::inst( 'contract_bal.opt_two' ),
                Field::inst( 'contract_bal.opt_three' ),
                Field::inst( 'contract_bal.opt_four' ),
                Field::inst( 'contract_bal.opt_five' ),
                Field::inst( 'contract_bal.opt_six' ),
                Field::inst( 'contract_bal.monthly_input' ),
                Field::inst( 'contract_bal.start_date' )
                    ->validator( Validate::dateFormat( 'Y-m-d' ) )
                    ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
                    ->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),
                Field::inst( 'contract_bal.end_date' )
                    ->validator( Validate::dateFormat( 'Y-m-d' ) )
                    ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
                    ->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),
    
         Field::inst( 'contract_bal.contractinfo_id_fk' )
                    ->options( Options::inst()
                        ->table( 'contract_info' )
                        ->value( 'contract_id' )
                        ->label( 'contract_prod' )
                    )
                    ->validator( Validate::dbValues() ),
    
         Field::inst( 'contract_info.contract_prod' ),
    
    
    
         Field::inst( 'contract_bal.product_code_fk2' )
                    ->options( Options::inst()
                        ->table( 'products' )
                        ->value( 'product_code' )
                        ->label( 'product_name' )
                    )
                    ->validator( Validate::dbValues() ),
    
         Field::inst( 'products.product_name' ),
    
          Field::inst( 'products.supplier_id_fk' )
                    ->options( Options::inst()
                        ->table( 'supplier' )
                        ->value( 'supplier_id' )
                        ->label( 'supplier_name' )
                    )
                    ->validator( Validate::dbValues() ),
                     Field::inst( 'supplier.supplier_name' )
    
    
    
            )
    
    
             ->leftJoin( 'contract_info', 'contract_info.contract_id', '=', 'contract_bal.contractinfo_id_fk' )
              ->leftJoin( 'products', 'products.product_code', '=', 'contract_bal.product_code_fk2' )
                ->leftJoin( 'supplier', 'supplier.supplier_id', '=', 'products.supplier_id_fk' )
    
            ->process( $_POST )
            ->json();
          }
    
  • allanallan Posts: 52,290Questions: 1Answers: 7,966 Site admin

    What I am trying to do is , populate one of the columns in datatable with Raw sql query ('SELECT start as starty from contract_bal') and the rest of columns to be filled by Editor instance

    Right - but what the code is doing above is just returning starty in a data array. It can’t merge the data from the two different parts of the if statement! Only one half of it runs after all.

    So, is there a way to populate one datatble with both raw sql and editor instance at same time? or in other words can we populate editor with rawsql?

    You can certainly populate with raw SQL, but you can’t do both raw SQL and the Editor instance at the same time.

    I don’t actually understand what the goal is here. Why not just include Field::inst( 'contract_bal.start' ), in your field list?

    Allan

  • Khalid TeliKhalid Teli Posts: 32Questions: 8Answers: 0
    edited August 2

    @allan Thank you for the clarification. Much appreciated!
    1) This was for the test purpose , just to try if we can populate with both RAW SQL and EDITOR instance. My final goal was to perform some calculations with RAW SQL and then pass them to Editor instance which I can read from client side.

    a) However, is there a way to include SQL inside field instance inside here Field::inst( 'contract_bal.start' )
    **
    **b) or is it possible to use php code inside the field instance like we used to do in Datatbles using ssp class to perform some operations , for example , in the code below i am reading 'testing' column and perform some operations using php ****

    ! array(
    ! 'db' => 'testing',
    ! 'dt' => 13,
    ! 'formatter' => function( $d, $row ) {
    !
    !
    ! $splitstr = str_split($d,10);
    !
    ! $split_one = $splitstr[0];
    ! $split_two = $splitstr[1];
    !
    ! $start_date = strtotime(str_replace('/', '-', $split_one));
    ! $end_date = strtotime(str_replace('/', '-', $split_two));
    ! $today = time() ;
    !
    ! $total = $end_date - $start_date;
    ! $elapsed = $today - $start_date;
    ! $percent = round(($total-$elapsed)/$total * 100);
    !
    ! return sprintf($percent).'%';
    !
    ! }

    2) I successfully managed to populate my DataTable just with RAWSQL . However, the problem I face here is that when turning theServerside:'true' the searching doesn't work and only works when serverside is set to false .
    Below is my server-side script I used

        <?php
    
        // DataTables PHP library
        include( "../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,
            DataTables\Database,
                DataTables\Database\Query,
               DataTables\Database\Result;
    
    
          $rawquery = "SELECT contract_info.contract_prod, products.product_name, supplier.supplier_name, contract_bal.contract_id, contract_bal.start_date, contract_bal.end_date, contract_bal.member_name, 
    
    
        SUM((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six)) as total_opn_bal,
    
        sum(((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - monthly_input)) as total_rem_bal,
    
        sum(((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - monthly_input)) / SUM((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six))*100 as total_rem_bal_per,
    
        SUM((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six)) - 
    
        sum(((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - monthly_input)) as monthly_avg
    
    
        FROM contract_bal
    
        LEFT JOIN products
        ON   contract_bal.product_code_fk2 = products.product_code
    
        LEFT JOIN contract_info
        ON   contract_info.contract_id = contract_bal.contractinfo_id_fk 
    
        LEFT JOIN Supplier
        ON products.supplier_id_fk  = supplier.supplier_id group by product_name";
    
        $data = $db->sql( $rawquery )->fetchAll();
    
    
         echo json_encode( [
           "data" => $data
         ] );
    
  • allanallan Posts: 52,290Questions: 1Answers: 7,966 Site admin
    Answer ✓

    You can use an SQL function in the Editor Field information .e.g.:

    Field::inst(‘SUM(...)’, ‘mySum’)
      ->set(false)
    

    See the documentation for that here.

    Allan

  • Khalid TeliKhalid Teli Posts: 32Questions: 8Answers: 0

    @allan Thank you very much. You are just amazing!!!

Sign In or Register to comment.