数据表服务器端搜索返回未知列在查询中使用别名时出错

Question

我在项目中使用了使用php的datatble服务器端。它运行得很好，但当我去搜索时，它会返回错误所在的未知列。我在下面附上了我的代码，然后我可以尝试更多的方法，但都不起作用。这是我的服务器端编码

<?php
header('Content-type: Text/HTML; Charset=UTF-8');
$type = $_REQUEST['type'];
$table = "SELECT p.*,u.name as unit,pt.name as proname,pt.sales_unitid as sales_unitid FROM purchase p LEFT JOIN product pt ON p.pid = pt.pid LEFT JOIN unit_master u ON p.unitid = u.aid ";
$counttable = "SELECT count(purchase_id) FROM purchase p LEFT JOIN product pt ON p.pid = pt.pid LEFT JOIN unit_master u ON p.unitid = u.aid WHERE p.status = 'a' AND purchase_dt = '".$type."'";
$cond = "p.status = 'a' AND purchase_dt = '".$type."'";
$primaryKey = 'purchase_id';

$columns = array(
array( 'db' => 'purchase_dtm','dt' => 0,
'formatter' => function( $d, $row ) {
return date( 'H:i:s', strtotime($d));
}),
array('db' => 'proname',
'dt' => 1),
array('db' => 'qty',
'dt' => 2),
array('db' => 'purchase_rate',
'dt' => 3,
'formatter' => function($d,$row) {
return number_format($d,2);
}),
array('db' => 'unit',
'dt' => 4),
array('db' => 'purchase_id',
'dt' => 5),
);

$sql_details = array(
'user' => 'msl',
'pass' => '',
'db'   => 'msl_pos',
'host' => 'localhost'
);
require( 'ssp_x.php' );
echo json_encode(
// SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
SSP::mycomplex( $_GET, $sql_details, $table,$columns ,$counttable,$cond )
);

然后我定制的SSP功能是

static function mycomplex ( $request, $conn, $table, $columns, $counttable, $cond )
{
$bindings = array();
$db = self::db( $conn );
$db->exec("set names utf8");
// Build the SQL query string from the request
$limit = self::limit( $request, $columns );
$order = self::order( $request, $columns );
$where = self::filter( $request, $columns, $bindings );
if($where)
{
$where = $where.' AND '.$cond;
}
else
{
$where = 'Where '.$cond;
}
// if($counttable=="")
$counttable=$counttable;
// Main query to actually get the data
$data = self::sql_exec( $db, $bindings,
"$table
$where
$order
$limit"
);
// Data set length after filtering
$resFilterLength = self::sql_exec( $db, $bindings,
"$counttable
"
);
$recordsFiltered = $resFilterLength[0][0];
/*
$stmt = $db->prepare( "$table $where" );
$stmt->execute();
$recordsFiltered = $stmt->rowCount();*/

// Total data set length
$resTotalLength = self::sql_exec( $db, $bindings,
"$counttable"
);
$recordsTotal = $resTotalLength[0][0];
/*$stmt = $db->prepare( $table );
$stmt->execute();
$recordsTotal = $stmt->rowCount();*/
/*
* Output
*/
return array(
"draw"            => isset ( $request['draw'] ) ?
intval( $request['draw'] ) :
0,
"recordsTotal"    => intval( $recordsTotal ),
"recordsFiltered" => intval( $recordsFiltered ),
"data"            => self::data_output( $columns, $data )
);
}

提前谢谢。当我要搜索它时，它会以的形式返回

dataTables warning: table id=purchase - 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 'WHERE (`purchase_dtm` LIKE '%k%' OR `proname` LIKE '%k%' OR `qty` LIKE '%k%' OR ' at line 2

然后我试着用下面这样的原名。不起作用

$table = "SELECT p.*,u.name,pt.name,pt.sales_unitid as sales_unitid FROM purchase p LEFT JOIN product pt ON p.pid = pt.pid LEFT JOIN unit_master u ON p.unitid = u.aid ";
$columns = array(
array( 'db' => 'purchase_dtm','dt' => 0,
'formatter' => function( $d, $row ) {
return date( 'H:i:s', strtotime($d));
}),
array('db' => 'pt.name',
'dt' => 1),
array('db' => 'qty',
'dt' => 2),
array('db' => 'purchase_rate',
'dt' => 3,
'formatter' => function($d,$row) {
return number_format($d,2);
}),
array('db' => 'u.name',
'dt' => 4),
array('db' => 'purchase_id',
'dt' => 5),
);

Answer 1

在ssp-class.php文件中，我的过滤函数被"Having"替换为"Where">