emran / ssp Goto Github PK

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`';

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

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

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>";
    }
)

);

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!

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

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"}

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 )
);`

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

Hello,

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

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?

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

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

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

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

$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)
);

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

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");