Datatables.net ssp.class.php filters return empty

Question

This is most probably down to a basic misunderstanding on my part of how the ssp.class works, especially with regards to the column definitions. I don't know how the 'dt' key functions except to pour over the code. I think I can give it a column order, or a string in which case it becomes the key in the json.

Another possibility is that my pdo options are messed up and not allowing bindings in pdo connections? What kind of configuration screw up can I look for?

Another possibility, php 7.3.30: does it allow for bindings in this way? Just making sure.

Note: if I have any columns where the names don't match, I changed some to anonymize them at least a little.

Thanks in advance.

All I know is, if I do a global search, I can see that $request['search']['value']; is populated and then it enumerates the columns looking for "searchable" == 'true' (as a test I only included text fields) and I can see that a :binding_X is created on each field in the SQL and an appropriate binding in the bindings array is there :bindingX = "%searchphrase%". I also have $WhereAll set, so the sql ends up like this:

SELECT `author_id`, `download_type`, `download_type_text`, `pro_id`, `num_clicks`, `script_title`, `pro_name`, `pro_co`, `pro_addr`, `pro_city`, `pro_state`, `pro_country`, `pro_zip`, `tinyurl`, `share_source`, `link_type`, `original_title`, `dl_id`, `dl_date`, `viewed_by_writer`
FROM `SomeTable`
WHERE (`pro_name` LIKE :binding_0 OR `download_type_text` LIKE :binding_1 OR `script_title` LIKE :binding_2 OR `pro_co` LIKE :binding_3 OR `dl_id` LIKE :binding_4) AND author_id = 'testuser55'
             ORDER BY `dl_date` DESC
             LIMIT 0, 10

And I can see there are 5 bindings like so:

key:":binding_0" val:"%searchphrase%" type:2

even though the search phrase is valid and if I run this in MySQL and swap out the bindings, it is of course correct.

php 7.3.30 jquery.Datatables 1.10.22 Latest version of ssp.class.php

The queries and sorting for the table are working fine except when I attempt to filter, either with the global search filter or a column filter.

Datatable (I'm shortening this to a test version without column filters):

var viewtableType = 'something';
var viewingsTable = $("#viewingsTable").DataTable({
    pageLength: 10,
    dom: "some dom that works",
    serverSide: true,
    ajax: {
      url: "script_views_data.php",
      type: "POST",
      data: function (data) {
        data.CurrentTable = viewtableType;
      },
    },
    columns: [
      { name: "pro_name", data: "pro_name", title: "Viewed By", orderable: true, searchable: true },
      { name: "download_type_text", data: "download_type_text", title: "Viewed Item", orderable: true, searchable: true},
      { name: "script_title", data: "script_title", title: "Script Title", orderable: true, searchable: true },
      { name: "dl_date", data: "dl_date", title: "Date", orderable: true, searchable: false },
      { data: "pro_id", name: "pro_id", searchable: false, visible: false },
      { data: "pro_co", name: "pro_co", searchable: true, visible: false },
      { data: "pro_addr", name: "pro_addr", searchable: false, visible: false },
      { data: "pro_city", name: "pro_city", searchable: false, visible: false },
      { data: "pro_state", name: "pro_state", searchable: false, visible: false, },
      { data: "pro_zip", name: "pro_zip", searchable: false, visible: false },
      { data: "pro_country", name: "pro_country", searchable: false, visible: false, },
      { data: "dl_id", name: "dl_id", searchable: true, visible: false },
      ]
    }
  });

Serverside script wrapper for ssp.class (which is unaltered except removing the suggested lines at the top):

<?php
session_start();
include "db_stuff.php";
require("varStuff.inc");
require('ssp.class.php');

$current_table = "";
if (isset($_POST['CurrentTable']) && !empty($_POST['CurrentTable'])) {
  $current_table = $_POST['CurrentTable'];
} else {
  exit("No Current Table was set");
}
if (!isset($_SESSION['Userid'])) {
  exit("Not logged in");
}
$WhereAll = "writer_id = '{$_SESSION['Userid']}'";
$WhereResult = "";
if (isset($_POST['ScriptFilterID']) && !empty($_POST['ScriptFilterID'])) {
  $WhereResult = "script_reg_id = '{$_POST['ScriptFilterID']}'";
}

$db_view = 'vwSomeView';
$primaryKey = 'dl_id';
$columns = array(

  array('db' => 'author_id', 'dt' => 'author_id'),
  array('db' => 'download_type', 'dt' => 'download_type'),
  array('db' => 'download_type_text', 'dt' => 'download_type_text'),
  array('db' => 'pro_id', 'dt' => 'pro_id'),
  array('db' => 'num_clicks', 'dt' => 'num_clicks'),
  array('db' => 'some_title', 'dt' => 'some_title'),
  array('db' => 'pro_name', 'dt' => 'pro_name'),
  array('db' => 'pro_co', 'dt' => 'pro_co'),
  array('db' => 'pro_addr', 'dt' => 'pro_addr'),
  array('db' => 'pro_city', 'dt' => 'pro_city'),
  array('db' => 'pro_state', 'dt' => 'pro_state'),
  array('db' => 'pro_country', 'dt' => 'pro_country'),
  array('db' => 'pro_zip', 'dt' => 'pro_zip'),
  array('db' => 'tinyurl', 'dt' => 'tinyurl'),
  array('db' => 'share_source', 'dt' => 'share_source'),
  array('db' => 'link_type', 'dt' => 'link_type'),
  array('db' => 'original_title', 'dt' => 'original_title'),
  array('db' => 'dl_id', 'dt' => 'dl_id'),
  array(
    'db'        => 'dl_date',
    'dt'        => 'dl_date',
    'formatter' => function ($d, $row) {
      return date('n/j/Y', strtotime($d));
    }
  ),
  array(
    'db'        => 'viewed_by_author',
    'dt'        => 'viewed_by_author',
    'formatter' => function ($d, $row) {
      return ($d == null)? "" : date('n/j/Y', strtotime($d));
    }
  )
);

//Output the json:
echo json_encode(
  $resultSet
);

Answer 1

This demotivator is becomeing more and more real: https://despair.com/products/mistakes?variant=2457302467

I was struggling with the above issue for 6 (maybe 12) hours and not 10 minutes after I posted my question I found the below settings. All I needed wa to set the character set on the object:

$link_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$link_pdo->exec('SET NAMES "utf8"');