Searching gives an error with data from two mysql tables
Searching gives an error with data from two mysql tables
arie0512
Posts: 5Questions: 1Answers: 0
Link to test case:
Debugger code (debug.datatables.net): arujob
Error messages shown: DataTables warning: table id=tabel-clienten - An SQL error occurred: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
Description of problem:
Hello,
I had a DataTable where I get my data from an AJAX request from 2 mysql tables.
If I want to do a search from the search field, I get the above error.
My javascript is:
$(document).ready(function() {
new DataTable('#tabel-clienten', {
createdRow: function( row, data, dataIndex ) {
$(row).attr('data-href', 'clienten-details.php?id=' + data[12]);
},
columnDefs: [
{ targets: [0], className: 'dt-body-right'},
{ targets: [0], render: DataTable.render.number( '.', ',', 0 ) },
{ visible: false, targets: [12] },
{ searchable: false, targets: [1,2,3,6,7,12] }
],
order: [[0, 'asc']],
pagingType: 'full_numbers',
pageLength: 25,
searching: true,
language: {
url: 'https://cdn.datatables.net/plug-ins/1.11.5/i18n/nl-NL.json'
},
ajax: '/crm/ajax/clienten-data.php',
processing: true,
serverSide: true
});
$('#tabel-clienten').on( 'click', 'tbody tr', function () {
window.location.href = $(this).data('href');
});
});
And my clienten-data.php is:
$table = 'clienten';
// Primary key
$primaryKey = 'clientnummer';
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
array( 'db' => 'clientnummer', 'dt' => 0 ),
array( 'db' => 'geslacht', 'dt' => 1 ),
array( 'db' => 'voorletters', 'dt' => 2 ),
array( 'db' => 'tussenvoegsel', 'dt' => 3 ),
array( 'db' => 'achternaam', 'dt' => 4 ),
array( 'db' => 'straat', 'dt' => 5 ),
array( 'db' => 'huisnummer', 'dt' => 6 ),
array( 'db' => 'toevoeging', 'dt' => 7 ),
array( 'db' => 'postcode', 'dt' => 8 ),
array( 'db' => 'woonplaats', 'dt' => 9 ),
array(
'db' => 'geboortedatum',
'dt' => 10,
'formatter' => function( $d, $row ) {
return date( 'd-m-Y', strtotime($d));
}
),
array( 'db' => 'email_1', 'dt' => 11 ),
array( 'db' => 'unieke_url', 'dt' => 12 )
);
// SQL server connection information
$sql_details = array(
'user' => '***',
'pass' => '***',
'db' => '***',
'host' => 'localhost'
);
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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('/var/www/vhosts/pma.info/httpdocs/crm/classes/ssp.class.php');
echo json_encode(
SSP::client_adres( $_GET, $sql_details, $table, $primaryKey, $columns )
);
And the function client_adres in ssp.class.php is:
static function client_adres ( $request, $conn, $table, $primaryKey, $columns ) {
$bindings = array();
$db = self::db($conn);
if (isset($request['json'])) {
$request = json_decode($request['json'], true);
}
// Build the SQL query string from the request
$limit = self::limit( $request, $columns );
$order = self::order( $request, $columns );
$where = self::filter( $request, $columns, $bindings );
// Main query to actually get the data
$data = self::sql_exec( $db, $bindings,
"SELECT clienten.clientnummer,
clienten.geslacht,
clienten.voorletters,
clienten.tussenvoegsel,
clienten.achternaam,
adressen.straat,
adressen.huisnummer,
adressen.toevoeging,
adressen.postcode,
adressen.woonplaats,
clienten.geboortedatum,
clienten.email_1,
clienten.unieke_url
FROM clienten, adressen WHERE clienten.adresnummer = adressen.adresnummer $order $limit"
);
// Data set length after filtering
$resFilterLength = self::sql_exec( $db, $bindings,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table`
$where"
);
$recordsFiltered = $resFilterLength[0][0];
// Total data set length
$resTotalLength = self::sql_exec( $db,
[],
"SELECT COUNT(`{$primaryKey}`)
FROM `$table`"
);
$recordsTotal = $resTotalLength[0][0];
/*
* Output
*/
return array(
"draw" => isset ( $request['draw'] ) ?
intval( $request['draw'] ) :
0,
"recordsTotal" => intval( $recordsTotal ),
"recordsFiltered" => intval( $recordsFiltered ),
"data" => self::data_output( $columns, $data )
);
}
Any idea why I get the error?
When I had only one table where the data came from everything works ok.
Kind regards,
Arie
Answers
Are you using the latest version of the SSP class? There was an issue matching what you describe a while back.
Allan
I am using the latest version now, tnx for pointing this out for me.
But unfortually this didn't help me solve the error.
I think it has something to do with the query but cant find out what's the problem.
I have made a copy of the Simple function and edit the main query $data:
In my humble opinion it looks allright but still getting the error whem I fill in one letter in the search field.
Any idea?
Or must I use the Complex function to get the search function works from two mysql tables?
Anyone any idea what goes wrong with two mysql tables with the search function?
In
function sql_execI'd suggest adding:The result will be invalid JSON (you could write the debug out to a file if you prefer to keep the JSON valid), but you can ignore that. The interesting bit will be to see what the SQL is, what bindings are in it, and what bindings have been registered.
Allan
The sql query give this:
Which looks like an good query
The binding is:
I have search for the letter a and than directly I get the popup error:
DataTables warning: table id=tabel-clienten - An SQL error occurred: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
I see that I only get 7 bindings, could that be the problem?
Because I have 13 fields in the query?
If so, how could I expand the bindings so that it will have 13 bindings?
anyone?
Sorry I lost track of this.
That query doesn't have any bindings, so the binding array for it should be empty. If that is the one that is failing, then there is certainly something going wrong.
The SSP class should execute three queries, not just the one shown above.
Can you show the full and unabbreviated response from the server when you get this error? (You'll actually get a JSON error instead in such a case, but that's okay).
Allan