emran / ssp Goto Github PK

Customized Server Side Processing (SSP) Class For Datatables Library

HTML 18.04% CSS 48.40% JavaScript 6.62% PHP 26.94%

ssp's Introduction

Customized SSP Class For Datatables Library

SSP is a Server Side Processing class for Datatables Library v1.10.0. I have customized it for include JOIN, Extra condition and Rename acceptance within query.

While using Datatables with complex query, I faced problem like these

Get Data from Multiple table not supported via Joining.
Extra Where, except filtering was not possible.
During work on Multiple Table, to avoid Duplicate key was not possible.

So due to allow complex query, I have changed the SSP class like my own. What i made changes are

I have added option to ADD JOIN Query and make necessary changes.
I have changed Column ARRAY format to handle get data from multiple table. Add TWO new index for complex query handle.
Add Extra Where condition through SSP Class.
You can Group by the result via sending Query through simple function of SSP Class.
You can use Having as well same like Group By

New formatted COLUMN Array

$columns = array(
    array( 'db' => '`c`.`id`',       'dt' => 0, 'field' => 'id' ),
    array( 'db' => '`c`.`login`',    'dt' => 1  'field' => 'login' ),
    array( 'db' => '`c`.`password`', 'dt' => 2, 'field' => 'password' ),
    array( 'db' => '`c`.`name`',     'dt' => 3, 'field' => 'client_name', 'as' => 'client_name' ),
    array( 'db' => '`cn`.`name`',    'dt' => 4, 'field' => 'currency_name','as' => 'currency_name' )

    array( 'db' => '`c`.`id_client`', 'dt' => 5, 'formatter' => function( $d, $row ) {
                return '<a href="EDIT_URL"><span class="label label-inverse"><i class="fa fa-edit"></i> Edit</span></a>';},
            'field' => 'id_client' )

How to Use

$joinQuery = "FROM `{$table}` AS `c` LEFT JOIN `currency_names` AS `cn` ON (`cn`.`id` = `c`.`id_currency`)";
$extraCondition = "`id_client`=".$ID_CLIENT_VALUE;

echo json_encode(
       SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraCondition)
     );

I have described the changes at My personal blog. You can also check there to see details.

Hope it will help... Thanks

ssp's People

Contributors

Stargazers

Watchers

Forkers

katapofatico imadige vamoros mbmehulshah jakapondang agwanti nabilpn jmeler mikadev cholnhial masdhona npatelds hadesain nelsvent amkhan786000 lydongha kaixlin rlpe dodiyanilesh gunaones rpatgao alexromeroperez shefalitandel sukor itarafath agenceniou thocell timot7782 techcalibrator goygoybords jcchikikomori jenalgit h3ld3rm3 eminentor casudinlix jijosh jcaceli j0ssgz websd starman1108 mrsof tombs007 badyma kitaramu daveclifford niilante brijesh2501 daligou ox-b dfsmirnov devvgit tentenponce bev-ru snakom23 patriciagm94 shihabmorayur lovnic akisc wsimbirsky oyogbo ultra-prod jericrealubit gmohughes empaguia84 aquibmominjlt atouhou ikbalyes dragstor tds29 laoman ellwarco nrjchnd mardi1505 sachinranaorem xtreamcodes robbiecode eleeyas zenburn2010 ramasp ilhamsafiudin okantastan ospfbgp amanvision alexandrehebertvincent joed0e incomestore tccarson mohethedreamy horaciodominguez pcdrivers mjpramos 6miramira thethales whoamikenken hrz0n cristhoperdev tianchan apisitdko mahdiakhi zdienos

ssp's Issues

JOIN error only in php, but not in navicat

Hi, Emran!

// QUERY CREATED BY SSP CLASS

SELECT SQL_CALC_FOUND_ROWS
s.cid AS serie_cid,
s.titulo AS serie_titulo,
c.titulo AS curso_titulo,
s. TIMESTAMP AS serie_timestamp,
s.situacao AS serie_situacao,
s.id AS serie_id
FROM
SP_cursos_series AS s
LEFT JOIN SP_cursos AS c ON c.id = s.curso_id
WHERE
s.cid = 4
GROUP BY
s.titulo ASC
//---

When i run the same query on navicat no erros, but on ssp class i'm getting:

{"error":"An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 's.id' in 'field list'"}

Any idea?

Tanhks!

help for COUNT () with join

Hello, my friend, I want to do a count in a

SQLSTATE[42000]

code can be easily exploited by sql injection .

{"error":"An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 10' at line 7"}

Is there any solution available

Get the value of some column and use in formater of another column

Hi, guy!

How can i get the value of some column and use in formater of another column?

Thanks!

$columns = array(
array( 'db' => 'nome', 'dt' => 0 ),
array( 'db' => 'email', 'dt' => 1 ),
array(
'db' => 'timestamp',
'dt' => 2,
'formatter' => function( $d, $row ) {
return date('d/m/Y H:i', $d);
}
),
array(
'db' => 'ultimo_acesso',
'dt' => 3,
'formatter' => function( $d, $row ) {
return date('d/m/Y H:i', $d);
}
),
array(
'db' => 'id',
'dt' => 4,
'formatter' => function( $d, $row ) {

I WANT USE HERE THE VALUE FROM COLUMN "email"

        return "<a href='usuarios/administradores/editar/".$d."/' class='btn btn-xs btn-primary'><i class='fa fa-pencil'></i></a> <a id='administradores_".$d."' href='javascript:;' class='btn btn-xs btn-danger del'><i class='fa fa-trash'></i></a>";
    }
)

);

SQLSTATE[42000] OR SQLSTATE[42S22]

When i set

// Table's primary key
$primaryKey = '`company`.`id`';

in ssp.class on this part

// Total data set length
$count_request = "SELECT COUNT(`{$primaryKey}`)";
if ($joinQuery) {
    $count_request .= $joinQuery;
} else {
    $count_request .= "FROM   `$table`";
}

I get this sql:

SELECT COUNT(``company`.`id``) FROM `company` LEFT JOIN `city` ON `company`.`city` = `city`.`id`

And error

{"error":"An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'company`.`id``) FROM `company` LEFT JOIN `city` ON `company`.`city` = `city`.`id' at line 1"}

And when i set

$primaryKey = 'company.id';

I get this sql:

SELECT COUNT(`company.id`) FROM `company` LEFT JOIN `city` ON `company`.`city` = `city`.`id`

And error

{"error":"An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'company.id' in 'field list'"}

I think that need set

$count_request = "SELECT COUNT({$primaryKey})";

without

And use

$primaryKey = '`company`.`id`';

concat not word

concat query not work this example concat work but this function concat not suport where clause...thanks :-)

https://datatables.net/forums/discussion/32686/server-side-with-joins

Changing column names

Recently i had issue when i was using renaming columns using AS using joined tables because some tables have same column names.
I was getting error undefined index: novi_naziv and search on column header and search input was not working.

$columns = array(
            array('db' => 'c.naziv AS novi_naziv', 'dt' => 1, 'field' => 'novi_naziv')
);

Using same name in fields was causing that i had same output in 2 columns in datatables, so i needed to use AS to change column name for joined tables, this is example for this scenario, you can see on image below also

$columns = array(
            array('db' => 'a.naziv', 'dt' => 1, 'field' => 'naziv'),
            array('db' => 'c.naziv', 'dt' => 2, 'field' => 'naziv')
);

FIX for this, at least it works for me

if (strpos($column['db'], 'AS') !== FALSE) {
    $column['db'] = explode('AS', $column['db'])[0];
}

those changes was made in those functions in SSP class

function order()
function filter()

you can see fork here https://github.com/mlukac89/ssp/blob/master/ssp.php

ssp for postgres

Hello,
Can you adapt your custom ssp class to work with PostgreSQL databases ?
Thank You !

Multiple WHERE Clause

Hello,

I want to do multiple where clause how can I do it

How to sum values with same ID

Hello,

I am using the ssp.php code and I really liked the result, but in my DATATABLES the double value of the items that have the same ID, how can I add the value of the items that have the same ID?

Can I use the SUM method of SQL in $ joinQuery?

this error LEFT JOIN

Does not work

<?php

// DB table to use
$table = 'usuario_vod';
// Table's primary key
$primaryKey = 'cve';

$columns = array(
	array( 'db' => '`u`.`user`', 'dt' => 0, 'field' => 'user', 'as' => 'user' ),
	array( 'db' => '`u`.`contrasenia`',  'dt' => 1, 'field' => 'contrasenia' ),
	array( 'db' => '`u`.`pin`',   'dt' => 2, 'field' => 'pin' ),
	array( 'db' => '`u`.`status`',     'dt' => 3, 'field' => 'status'),
	array( 'db' => '`ud`.`user`',     'dt' => 4, 'field' => 'user', 'as' => 'user' ),
	array( 'db' => '`u`.`fecha_vencimiento`', 'dt' => 5, 'field' => 'fecha_vencimiento', 'formatter' => function( $d, $row ) {
																	return date( 'd-M-Y', strtotime($d));
																}),
	array('db'  => '`u`.`idu_dispositivo`',     'dt' => 6, 'field' => 'idu_dispositivo'),
	array('db'  => '`u`.`idu_dispositivo`',     'dt' => 7, 'field' => 'idu_dispositivo')
);
// SQL server connection information
require('config.php');
$sql_details = array(
	'user' => $db_username,
	'pass' => $db_password,
	'db'   => $db_name,
	'host' => $db_host
);
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */
// require( 'ssp.class.php' );
require('ssp.customized.class.php' );
$joinQuery = "FROM `usuario_vod` AS `u` JOIN `usuario` AS `ud` ON (`ud`.`cve` = `u`.`cve_dealer`)";
$extraWhere = "";        
echo json_encode(
	SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere )
);

In the user fields I throw the same data since they use the same field the 2 tables

Null everywhere

Hi, hope you can help me.
this is my result when i call ajax function:

{"draw":1,"recordsTotal":236,"recordsFiltered":236,"data":[[null,null,null,null,null,null],[null,null,null,null,null,null],[null,null,null,null,null,null],[null,null,null,null,null,null],[null,null,null,null,null,null],[null,null,null,null,null,null],[null,null,null,null,null,null],[null,null,null,null,null,null],[null,null,null,null,null,null],[null,null,null,null,null,null]]}

and this is the code I call with ajax

`<?php
require_once('../includes/load.php');
page_require_level(3);

$table = 'products';

$primaryKey = 'id';

$columns = array(
array( 'db' => 'products.name', 'dt' => 0, 'field' => 'p_name'),
array( 'db' => 'products.note', 'dt' => 1, 'field' => 'p_note' ),
array( 'db' => 'categories.name', 'dt' => 2, 'field' => 'c_name'),
array( 'db' => 'producer.name', 'dt' => 3, 'field' => 'pro_name' ),
array( 'db' => 'products.prezzo', 'dt' => 4, 'field' => 'p_prezzo' ),
array( 'db' => 'products.quantity', 'dt' => 5, 'field' => 'p_quantity' )
);

require('../includes/config.php');
$sql_details = array(
'user' => DB_USER,
'pass' => DB_PASS,
'db' => DB_NAME,
'host' => DB_HOST
);

require('ssp.class.php');

$joinQuery = "FROM products LEFT JOIN categories ON products.categories_id = categories.id LEFT JOIN producer ON products.producer_id = producer.id";
$extraWhere = "";
$groupBy = "";
$having = "";

echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $groupBy, $having )
);`

Error with cyrillic data in db fields

In this part

// Is there a formatter?
if ( isset( $column['formatter'] ) ) {
    $row[ $column['dt'] ] = ($isJoin) ? $column['formatter']( $data[$i][ $column['field'] ], $data[$i] ) : $column['formatter']( $data[$i][ $column['db'] ], $data[$i] );
}
else {
    $row[ $column['dt'] ] = htmlentities( ($isJoin) ? $data[$i][ $columns[$j]['field'] ] : $data[$i][ $columns[$j]['db'] ] );
}

If I have cyrrilic data in db fields i get null of wrong data.
Need to set "UTF-8"

$row[$column['dt']] = htmlentities(($isJoin) ? $data[$i][$column['field']] : $data[$i][$column['db']], null, "UTF-8");

Not parsing the html

I have tried the formatting of html, but it does not allow that

Limit, Order and search does not work

$table = 'SP_cursos_series';

$primaryKey = 's.id';
$extraWhere = 's.cid = '.$cid.' ';
$groupBy = 's.titulo ASC';

$joinQuery = "FROM {$table} AS s LEFT JOIN SP_cursos AS c ON (c.id = s.curso_id)";

$columns = array(
array( 'db' => 's.titulo AS s_titulo', 'dt' => 0, 'field' => 's_titulo' ),
array( 'db' => 'c.titulo AS c_titulo', 'dt' => 1, 'field' => 'c_titulo' ),
array(
'db' => 's.timestamp AS s_timestamp',
'dt' => 2,
'field' => 's_timestamp',
'formatter' => function( $d, $row ) {
return date('d/m/Y H:i', $d);
}
),
array( 'db' => 's.situacao AS s_situacao', 'dt' => 3, 'field' => 's_situacao' ),
array(
'db' => 's.id AS s_id',
'dt' => 4,
'field' => 's_id',
'formatter' => function( $d, $row ) {

        if($row[3] == "ativa"){$corsit = "success";}
        if($row[3] == "inativa"){$corsit = "danger";}           

        $comandos = '<a href="cursos/editar/'.$row[4].'/" class="btn btn-xs btn-primary"><i class="fa fa-pencil"></i></a> <a id="series_'.$row[4].'" href="javascript:;" class="btn btn-xs btn-danger del"><i class="fa fa-trash"></i></a> <div class="btn-group text-right"><button type="button" class="btn btn-'.$corsit.' br2 btn-xs fs12 dropdown-toggle" data-toggle="dropdown" aria-expanded="false"> '.$row[3].' <span class="caret ml5"></span></button><ul class="dropdown-menu dropdown-menu-right dropdown-menu dropdown-menu-right-right" role="menu"><li><a href="javaScript:mudaSituacao(\'SP_cursos_series\', \' '.$row[4].' \', \'ativa\');">Ativa</a></li><li><a href="javaScript:mudaSituacao(\'SP_cursos_series\', \' '.$row[4].' \', \'inativa\');">Inativa</a></li></ul></div>';

        return $comandos;

    }
)

);

require( '../classes/ssp.class.php' );

echo json_encode(
SSP::simple ( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $groupBy)
);

Multiple JOINS

Hi,

I have 4 tables needed to JOIN, with only 2 all works well,
$join = "FROM vouchersJOINvoucherTypesONvouchers.poukaz=voucherTypes.idJOINvoucherFamilyONvoucherTypes.family = voucherFamily.id JOIN status ON vouchers.status = status.id JOIN users ON (vouchers.vystavil=users.id";

$columns = array( array( 'db' => 'vouchers.id', 'dt' => 0, 'field' => 'id' ), array( 'db' => 'vouchers.poukaz', 'dt' => 1, 'field' => 'poukaz' ), array( 'db' => 'vouchers.individual', 'dt' => 3, 'field' => 'individual' ), array( 'db' => 'vouchers.poznamkaIntern', 'dt' => 4, 'field' => 'poznamkaIntern' ), array( 'db' => 'vouchers.dokupSluzeb', 'dt' => 5, 'field' => 'dokupSluzeb' ), array( 'db' => 'vouchers.termin', 'dt' => 6, 'field' => 'termin' ), array( 'db' => 'vouchers.status', 'dt' => 7, 'field' => 'status', 'as' => 'rawStatus' ), array( 'db' => 'vouchers.celeJmeno', 'dt' => 8, 'field' => 'celeJmeno' ), array( 'db' => 'vouchers.telefon', 'dt' => 9, 'field' => 'telefon' ), array( 'db' => 'vouchers.email', 'dt' => 10, 'field' => 'email' ), array( 'db' => 'vouchers.vystaveno', 'dt' => 11, 'field' => 'vystaveno' ), array( 'db' => 'vouchers.platnost', 'dt' => 12, 'field' => 'platnost' ), array( 'db' => 'vouchers.cena', 'dt' => 13, 'field' => 'cena' ), array( 'db' => 'vouchers.varSymbol', 'dt' => 14, 'field' => 'kod', 'as' => 'kod' ), array( 'db' => 'vouchers.varSymbol2', 'dt' => 15, 'field' => 'varSymbol' , 'as' => 'varSymbol' ), array( 'db' => 'voucherFamily.humanName', 'dt' => 16, 'field' => 'cena' , 'as' => 'voucherType' ), array( 'db' => 'status.name', 'dt' => 17, 'field' => 'name', 'as'=>'status' ), array( 'db' => 'voucherTypes.family', 'dt' => 18, 'field' => 'family', 'as'=>'druhPoukazu' ), array( 'db' => 'voucherTypes.family`', 'dt' => 19, 'field' => 'family' ),

);`

using this throws: Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/easyvoucher/milenium/vendor/datatables/datatables/examples/server_side/scripts/ssp.class.php on line 166
{"error":"An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'voucherFamily.idJOINstatusONvouchers.status=status.idJOINu' at line 2"}

"An SQL error occurred: SQLSTATE [23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous"}

If two tables has field 'id' this error will appear

Libs_SSP

Hello and a lot of thanks for your contribution!

On your SSP class there are several calls like this:

Libs_SSP::method($params);

a) Why the class name is SSP and not Libs_SSP? Better is Libs_SSP, isn't?
b) Better than a):Those calls should be like this:

self::method($params);