Searching in a datatable and setting columns

Petr.k007

Hello, I have a question about searching. Loading data from mysgl into Datatable works for me. The table has a total of 5 columns. If I search for a term, an error message pops up. When I remove two columns and the table contains 3 columns the search works. Where the number of columns in the table is set. Or where can the error be? The table contains Czech data. Thank you for your help.

SKRIPT

<?php
/* Database connection start */
$servername = " ****** ";
$username = " ****** ";
$password = " ****** ";
$dbname = " ****** ";

$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());
mysqli_set_charset($conn,"utf8");

// storing request (ie, get/post) global array to a variable
$requestData= $_REQUEST;

$columns = array(

0 => 'datum',
1 => 'cas',
2 => 'poradatel',
3 => 'misto',
4 => 'akce'

);

// getting total number records without any search
$sql = "SELECT id, datum, cas, poradatel, misto, akce ";
$sql.=" FROM FOTOAS_akce2 ";
$query=mysqli_query($conn, $sql) or die("data-songs.php: NACTENI MYSGL TABULKY RZH-SONGS-MY DO DATATABLE");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.

$sql = "SELECT id, datum, cas, poradatel, misto, akce ";
$sql.=" FROM FOTOAS_akce2 WHERE 1=1";
if( !empty($requestData['search']['value']) )
{
$sql.=" AND ( datum LIKE '".$requestData['search']['value']."%' ";

$sql.=" OR cas LIKE '".$requestData['search']['value']."%' ";
$sql.=" OR poradatel LIKE '".$requestData['search']['value']."%' ";
$sql.=" OR misto LIKE '".$requestData['search']['value']."%' ";
$sql.=" OR akce LIKE '".$requestData['search']['value']."%' )";

}

$query=mysqli_query($conn, $sql) or die("data-objednavky.php: NACTENI MYSGL TABULKY ESHOP-OBJEDNAVKY DO DATATABLE");
$totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result.
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." ";

/* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc */
$query=mysqli_query($conn, $sql) or die("data-objednavky.php: NACTENI MYSGL TABULKY ESHOP-OBJEDNAVKY DO DATATABLE");

$data = array();
while( $row=mysqli_fetch_array($query) )
{
$nestedData=array();

$nestedData[] = $row["datum"];
$nestedData[] = $row["cas"];
$nestedData[] = $row["poradatel"];
$nestedData[] = $row["misto"];
$nestedData[] = $row["akce"];

$nestedData[] = '<button class="btn btn-sm btn-success modal-eshop-edit" id="'.$row["id"].'" data-toggle="modal" data-target="#modal-eshop-edit" title="Editace"><i class="fa fa-edit" aria-hidden="true"></i></button>
<button class="btn btn-sm btn-danger" data-toggle="modal" data-target="#modal-eshop-delete" data-whatever="'.$row["id"].'" title="Smazat"><i class="fa fa-trash-o" aria-hidden="true"></i></button>';

$data[] = $nestedData;
}

$json_data = array(
"draw" => intval( $requestData['draw'] ), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
"recordsTotal" => intval( $totalData ), // total number of records
"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
"data" => $data // total data array
);

echo json_encode($json_data); // send data as json format

<?php > ?>

kthorngren

If I search for a term, an error message pops up.

What is the error?

EDIT: Please post your Datatables initialization code.

Kevin

Petr.k007

I am sending a screenshot

kthorngren

The place to start is to use the troubleshooting steps provided at the link in the error:
https://datatables.net/manual/tech-notes/1

Let us know what you find.

Kevin

Petr.k007

I can't find the given error. When I remove two columns from the table everything works correctly. I removed the date and time. The problem will be in the number of columns.

0 => 'poradatel',
1 => 'misto',
2 => 'akce'

When I have all 5 columns it writes an error.

0 => 'datum',
1 => 'cas',
2 => 'poradatel',
3 => 'misto',
4 => 'akce'

Petr.k007

When a table has three or four columns. Search works. I let

0 => 'cas',
1 => 'poradatel',
2 => 'misto',
3 => 'akce'

When I have all 5 columns it writes an error.

0 => 'datum',
1 => 'cas',
2 => 'poradatel',
3 => 'misto',
4 => 'akce'

kthorngren

I can't find the given error.

When you get the error what do you see in the XHR > Response tab of the network inspector?

If its empty then that is a JSON error.

Kevin

Petr.k007

I see a line with the path to the data-action.php file

kthorngren

Continue following the steps in the technote:

The network panel will show all of the requests made by Chrome to load the page.
Click the XHR option at the bottom of the window to reduce the requests to just the Ajax (XHR) requests.
Double click the Ajax request made by DataTables

Double click the second data-action.php which should correspond to the request generating the error. Then click on the response tab.

Kevin

Petr.k007

kthorngren

That is showing draw: 1. That is the first initial request to load the data. You said the problem happens when searching. The draw parameter is incremented for each request. We need to look at the response tab of the request generating the error.

Kevin

Petr.k007

kthorngren

You have two statements that look like this in the PHP script:

$query=mysqli_query($conn, $sql) or die("data-objednavky.php: NACTENI MYSGL TABULKY ESHOP-OBJEDNAVKY DO DATATABLE");

The response suggests that one these is failing with an error, ie, executing the die() function. You will need to do some debugging of the server script to determine what is causing the error.

Kevin

Petr.k007

82 / 5 000
I am sending the files for viewing. You can check it out and try it out
Thank you for your help. fotoas.cz/datatable.zip

kthorngren

The link doesn't seem to work. I don't use PHP so not sure how much help I can provide. You can contact @allan, by clicking the Ask a Private Question button at the top of the page , to provide the files and ask for help.

Kevin

allan

Yes, I can help debug custom PHP under the support packages.

If you can use a PDO connection rather than mysqli, then it might be worth using the Editor server-side libraries as they have server-side processing built in, and are open source (the Editor Javascript has a commercial license).

Allan

Petr.k007

Hi, I changed the PDO connection settings, but now my table is not loading. https://fotoas.cz/skript-php.txt My SGL is SET utf8 COLLATE utf8_czech_ci

rf1234

https://www.w3schools.com/php/func_mysqli_query.asp
mysqli_query doesn't work with PDO.

Read this please regarding the PDO syntax:
https://www.w3schools.com/php/php_mysql_select.asp

Here is a screenshot from that link:

More to read:
https://www.php.net/manual/en/book.pdo.php

Petr.k007

Hi, I'm still having trouble searching. Datatable is in Czech and UTF-8. The data is in UTF8 and in mysgl. Can you take a look at my code and advise me how to modify it? https://fotoas.cz/data-akce.txt

No success yet

kthorngren

Are you getting the same error?

Allan can help with debugging the custom PHP script using one of the support packages.

Kevin

allan

You probably need to execute SET NAMES utf8 as the first SQL command after the connection.

Allan

Petr.k007

Hello,
Script update

EXAMPLE

kthorngren

It doesn't look like your server script is returning JSON data. It looks like you may have gotten the PDO connection to work. IF this is the case have you looked at using the Editor server side libraries blog that Allan linked to earlier? Might be easier to use than trying to create your own server side processing script.

Kevin

rf1234

In addition to Kevin's remarks your $dsn variable doesn't look right. You will definitely need to use the "charset" parameter. Otherwise there will be no Czech characters. I recommend using charset "utf8mb4" with collation "utf8mb4_unicode_ci" because that doesn't only work with Czech but with all other European languages. But you would also need to adjust the charset of your database to that. Just using "utf8" may still be ok in your situation.

Your dsn - variable could look like this:

$dsn = 'mysql:host=' . $host . ';dbname=' . $databaseName . ';charset=utf8';

Petr.k007

Hi, I still can't set the Czech language for searching from the MySGL database. When I ěščřžý. enter the table, it doesn't search and it says error

rf1234

Very difficult to help you. Don't know what you got wrong. As I said: I use "utf8mb4" etc.

Even though I only support German and English it was no problem for me to find your Czech letters. "utf8mb4" even displays and finds emoticons.

Hence I guess this isn't a datatables issue but a database problem.

What is a MySGL database? Do you mean MySQL?

I use two database handlers. Both work. I post them here so that you can check them out and maybe use some of it.

1. Editor as defined in config.php (requires Editor at least for the back end):

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Database user / pass
 */
$sql_details = array(
    "type" => "Mysql",     // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
    "user" => "yourUser",          // Database user name
    "pass" => "yourPassword",          // Database password
    "host" => "localhost", // Database host
    "port" => "",          // Database connection port (can be left empty for default)
    "db"   => "yourDatabase",          // Database name
    "dsn"  => "charset=utf8mb4",          // PHP DSN extra information. Set as `charset=utf8mb4` if you are using MySQL
    "pdoAttr" => array( PDO::ATTR_STRINGIFY_FETCHES => true )   // PHP PDO attributes array. See the PHP documentation for all options
);


// This is included for the development and deploy environment used on the DataTables
// server. You can delete this block - it just includes my own user/pass without making 
// them public!
if ( is_file($_SERVER['DOCUMENT_ROOT']."/datatables/pdo.php") ) {
    include( $_SERVER['DOCUMENT_ROOT']."/datatables/pdo.php" );
}

1. my own db handler that I use in addition for proprietary SQL queries:

define("DB_USER",           "yourUser");
define("DB_PASS",           "yourPassword");
define("DB_HOST",           "localhost");
define("DB_NAME",           "yourDatabase");
define("DB_CHARSET",        "utf8mb4");

class Database
{
    private $host = DB_HOST;
    private $dbname = DB_NAME;
    private $charset = DB_CHARSET;
    private $user = DB_USER;
    private $pass = DB_PASS;    
    
    private $dbh;
    private $error;
    
    private $stmt;
    
        
    public function __construct() {
         // Set DSN
        $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname . ';charset=' . $this->charset;
        // Set options
        $options = array(
            PDO::ATTR_PERSISTENT            => true,
            PDO::ATTR_ERRMODE               => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_STRINGIFY_FETCHES     => true
        );
        // Create a new PDO instanace
        try{
            $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
        }
            catch(PDOException $e) {  
                $this->error = $e->getMessage();               
                file_put_contents('PDOErrors.txt', $this->error, FILE_APPEND);
                die ("Database Connection Error");
        }
    }    
    public function query($query) {
        $this->stmt = $this->dbh->prepare($query);        
    }
    public function bind($param, $value, $type = null){
    //determine type as input for bindValue
    if (is_null($type)) {
        switch (true) {
            case is_int($value):
                $type = PDO::PARAM_INT;
                break;
            case is_bool($value):
                $type = PDO::PARAM_BOOL;
                break;
            case is_null($value):
                $type = PDO::PARAM_NULL;
                break;
            default:
                $type = PDO::PARAM_STR;
            }
        }
        // PDO method bindValue (PDOStatement::bindValue)
        $this->stmt->bindValue($param, $value, $type);
    }
    public function execute(){
        return $this->stmt->execute();
    }
    
//    This is returned by resultset():
//    Multidimemnsional array with column labeled values
//    and numbered values
//    Array
//(
//    [0] => Array
//        (
//            [name] => pear
//            [0] => pear
//            [colour] => green
//            [1] => green
//        )
//
//    [1] => Array
//        (
//            [name] => watermelon
//            [0] => watermelon
//            [colour] => pink
//            [1] => pink
//        )
//
//)
    public function resultset(){
        $this->execute();
        return $this->stmt->fetchAll();
    }
    
//    This is returned by resultsetAssoc():
//    Multidimemnsional array with column labeled values
//    and NO numbered values
//    Array
//(
//    [0] => Array
//        (
//            [name] => pear
//            [colour] => green
//        )
//
//    [1] => Array
//        (
//            [name] => watermelon
//            [colour] => pink
//        )
//
//)
    public function resultsetAssoc(){
        $this->execute();
        return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
    }
//  With single():
//  The array has only one dimension!!!
    public function single(){
        $this->execute();
        return $this->stmt->fetch();
    }
//  With singleAssoc():
//  The array has only one dimension!!!
    public function singleAssoc(){
        $this->execute();
        return $this->stmt->fetch(PDO::FETCH_ASSOC);
    }
    public function rowCount(){
        return $this->stmt->rowCount();
    }
    public function lastInsertId(){
        return $this->dbh->lastInsertId();
    }
    public function autocommitOff(){
        return $this->dbh->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
    }
    public function autocommitOn(){
        return $this->dbh->setAttribute(PDO::ATTR_AUTOCOMMIT, true);
    }
    public function beginTransaction(){
        return $this->dbh->beginTransaction();
    }
    public function endTransaction(){
        if ( $this->dbh->inTransaction() ) {
            return $this->dbh->commit();
        }
    }
    public function inTransaction(){
        return $this->dbh->inTransaction();
    }
    public function cancelTransaction(){
        return $this->dbh->rollBack();
    }
    public function debugDumpParams(){
        return $this->stmt->debugDumpParams();
    }
    // array is returned: [0] SQLSTATE, [1] some useless error code [2] error message
    public function errorInfo() {
        return $this->stmt->errorInfo();
    }
}

Petr.k007

I also tried the example from the loss datatable. My code:

$table = 'FOTOAS_akce2';

// Table's primary key
$primaryKey = 'id';

// 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' => 'datum', 'dt' => 0 ),
array( 'db' => 'cas', 'dt' => 1 ),
array( 'db' => 'poradatel', 'dt' => 2 ),
array( 'db' => 'misto', 'dt' => 3 ),
array( 'db' => 'akce', 'dt' => 4 )
);

// SQL server connection information
$sql_details = array(
'user' => 'xxxxxxx',
'pass' => 'xxxxxxx',
'db' => 'xxxxxxx',
'host' => 'xxxxxxx',
'charset' => 'utf8mb4' // Depending on your PHP and MySQL config, you may need this
);

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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( 'ssp.class2.php' );

echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

allan

And what happens when you run that script? Please link to a test case.

Also I don't know what ssp.class2.php is.

Petr.k007

Hi, I still can't set the Czech language for searching from the MySQL database. When I ěščřžý. enter the table, it doesn't search and it says error

rf1234

I gave you a detailed description of what you would need to change. Have you tried that?

allan

There are a number outstanding questions from myself as well. If you would like free support, please provide the information we ask for, otherwise we can't help you.

Allan