Searching gives an error with data from two mysql tables

Searching gives an error with data from two mysql tables

arie0512arie0512 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

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

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

    Allan

  • arie0512arie0512 Posts: 5Questions: 1Answers: 0

    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?

  • arie0512arie0512 Posts: 5Questions: 1Answers: 0

    Anyone any idea what goes wrong with two mysql tables with the search function?

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    In function sql_exec I'd suggest adding:

    echo "Executing: $sql\n";
    print_r($bindings);
    

    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

  • arie0512arie0512 Posts: 5Questions: 1Answers: 0

    The sql query give this:

    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 BY `clientnummer` ASC LIMIT 0, 25
    

    Which looks like an good query

    The binding is:

    [{"key":":binding_0","val":"%a%","type":2},{"key":":binding_1","val":"%a%","type":2},{"key":":binding_2","val":"%a%","type":2},{"key":":binding_3","val":"%a%","type":2},{"key":":binding_4","val":"%a%","type":2},{"key":":binding_5","val":"%a%","type":2},{"key":":binding_6","val":"%a%","type":2}]
    

    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?

  • arie0512arie0512 Posts: 5Questions: 1Answers: 0

    anyone?

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    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

Sign In or Register to comment.