Query in a table

antoniocib

Hi guys, i 've this table :

$('#scrivania2').DataTable( {
// serverSide: 'true',
// dom: 'Bfrtip',
// dom: 'Pfrtip',
ajax: 'php/table.scrivania1.php',
columns:[
{
"data": "scarico"
},
{
"data": "epal"
},
{
"data": "ind"
}
],
} );

And i need to add this query in this table:

SELECT scarico, sum(epal + perd), sum(ind) From scrivania1 where dlinea = '' and scarico !='' GROUP BY scarico

rf1234

ok, great! And what is your question?

Let's assume the question is "How to do this?"
Then take a look at the docs please:
Examples: https://datatables.net/examples/ajax/index.html
Manual: https://datatables.net/manual/ajax

If you are using Editor take a look at the respective Editor examples and manual. Thanks.

If you are not using Editor you should probably familiarize yourself with the "ssp-class" to help you retrieve your data from your server:
https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/ssp.class.php

My recommendation would be: Get an Editor license and use a view to retrieve the grouped values from your database table. Makes life a lot easier than working with the ssp class - but that is just my personal opinion. I am not affiliated with SpryMedia by the way and I certainly do not participate in any Editor license revenues.

rf1234

This is the view you would need:

CREATE VIEW scrivania2 AS
SELECT 
scarico,
dlinea,
SUM(epal + perd) AS epal,
SUM(ind)         AS ind
FROM scrivania1 
GROUP BY scarico, dlinea

client script:

$('#scrivania2').DataTable( {
    dom: 'Bfrtip',
    ajax: {
        url: 'php/table.scrivania2.php',
        type: 'POST'
    },
    columns: [
        { data: "scarico" },
        { data: "epal"    },
        { data: "ind"     }
    ]
} );

server script with Editor:

// 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;

Editor::inst( $db, 'scrivania2' )
    ->field(
        Field::inst( 'scarico' )->set( false ),
        Field::inst( 'epal' )->set( false ),
        Field::inst( 'ind' )->set( false )
    )
    ->where( 'dlinea', '' )       
    ->where( 'scarico', '', '!=' ) 
    ->process($_POST)
    ->json();

That's all IF you are using Editor. If not: more work. So invest a few pounds, dollars or euros and your life will be easier ...

antoniocib

I've the license of Editor,ahahahah u r a feels bad man i like!

antoniocib

@rf1234 return me this error:

i need to add in this:

CREATE VIEW s2 AS
SELECT
scarico,
dlinea,
SUM(epal + perd) AS epal,
SUM(ind) AS ind
FROM scrivania1
GROUP BY scarico, dlinea

too id?

and in this:

// 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;

Editor::inst( $db, 's2' )
->field(
Field::inst( 'scarico' )->set( false ),
Field::inst( 'epal' )->set( false ),
Field::inst( 'ind' )->set( false )
)
->where( 'dlinea', '' )
->where( 'scarico', '', '!=' )
->process($_POST)
->json();

too id?

antoniocib

This is the HTML i dont think there are problems in this:

                <table cellpadding="0" cellspacing="0" border="0" class="display" id="s2" width="50%">
                    <thead>
                        <tr>
                            <th >Scarico</th>
                            <th>EPAL</th>
                            <th>IND</th>
                        </tr>
                    </thead>
                    <tfoot>
                <tr>
                    <th >Scarico</th>
                        <th>EPAL</th>
                        <th>IND</th>
                </tr>
                </tfoot>
                </table>

rf1234

This is really a special case for which Editor wasn't really designed I guess. It normally requires an id field as the key but you can also define a compound key. Since we are not really editing anything here but only retrieving data we might get it done like this. Just give it a try:

Editor::inst( $db, 'scrivania2', array('scarico', 'dlinea') )
    ->field(
        Field::inst( 'scarico' )->set( false ),
        Field::inst( 'epal' )->set( false ),
        Field::inst( 'ind' )->set( false )
    )
    ->where( 'dlinea', '' )      
    ->where( 'scarico', '', '!=' )
    ->process($_POST)
    ->json();

https://editor.datatables.net/manual/php/getting-started#Primary-key-name

antoniocib

Thanks man works!