Server-side query?

murrayjhart

Hello everyone on for forum, Trying ti use data tables for the first time and really struggling to find a solution for my query.

I have 2 tables that need to be populated,the first holds applicants details and the second holds team details.

for the first query i have used the following code:
applicants_processing.php

// applicants_processing
$table = 'applicants';

$primaryKey = 'id';

$columns = array(
    array( 'db' => 'id', 'dt' => 0 ),
    array( 'db' => 'firstname',  'dt' => 1 ),
    array( 'db' => 'lastname',  'dt' => 2 ),
    array( 'db' => 'city',   'dt' => 3 ),
    array( 'db' => 'contactnumber',   'dt' => 4 )
);

// SQL server connection information
$sql_details = array(
    'user' => 'root',
    'pass' => '',
    'db'   => 'gamejam',
    'host' => 'localhost'
);

require( 'ssp.class.php' );
echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

The above is used along with the custom ssp.class.php found here https://github.com/PouyaDarabi/PHP/blob/master/ssp.class.php

now i would like to do somwthing simmiler for the follwing mysql
query:

$sql = SELECT teamname,COUNT(*) as count, gametype FROM applicants GROUP BY teamname ORDER BY count DESC;

Any help would be greatly appreciated.

Thanks in advance.

allan

Hi,

There is no option in the demo SSP class to perform group by operations. You would need to create your own server-side processing script that does do that. The protocol for server-side processing's data interchange is documented in the manual.

Allan

murrayjhart

Yeah i have had a look at it and have got it running using the where statements, its just the group by and order by that i am having trouble with.

Am i correct in saying that i will need to adapt the SSP to something like this:

static function simple ( $request, $sql_details, $table, $primaryKey, $columns, $joinQuery = NULL, $extraWhere = '', $groupBy = '')
{......
 $where = Libs_SSP::filter( $request, $columns, $bindings, $joinQuery );
 
// IF Extra where set then prepare query
 if($extraWhere)
 $extraWhere = ($where) ? ' AND '.$extraWhere : ' WHERE '.$extraWhere;
 
 // Main query to actually get the data
 if($joinQuery){
 $col = Libs_SSP::pluckForJoin($columns, 'db');
 $query = "SELECT SQL_CALC_FOUND_ROWS ".implode(", ", $col)."
 $joinQuery
 $where
 $extraWhere
 $groupBy
 $order
 $limit";
 }else{
.......

Thanks

allan

You don't need to adapt the SSP library, but you can certainly do so if you want to use it as a starting point!

Allan

murrayjhart

Im LOST!

Thanks for the help anyways. Its just really frustrating that i can get it it work in mysql and not on here.

It should be simple its not that complex of a query is it?

$sql = SELECT teamname,COUNT(*) as count, gametype FROM applicants GROUP BY teamname ORDER BY count DESC;

allan

No - it is perfectly do able. How big is your data set, do you really need server-side processing is the first question?

If you do need it, then yes, you would need to modify your query to meet the requirements of server-side processing. If not, then just use the query directly.

Allan