Searching gives an error with data from two mysql tables

arie0512

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

allan

Are you using the latest version of the SSP class? There was an issue matching what you describe a while back.

Allan

arie0512

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:

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

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?