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