Filtering table data

[MadSmol Qwert]

Hi!

I have a question about how to implement a filter for each column (date_visit, model_fr, sn_fr) of a table with the following structure:

<div id= "rozreport_table" class= "zdk\ - datatable zdk-synchronize" data-zdk\ - cols\ - resize= "true" title= "History"
data-zdk-action="rozreportctrl: data"
data- zdk-paginator="10"
data-zdk-columns='[{"field": "id", "HeaderText": "ID", "sortable": true, "icon": "fa\ - business card"},
{"field": "date_visit", "HeaderText": "dare work", "sortable": true},
    {"field": "model_fr", "HeaderText": "models", "sortable": true," sortable": true},
{"field": "sn_fr", "HeaderText": "serial number", "tooltip": true, "sortable": true},
    ]'>
</div>

[ZnetDK]

Hello,

First you can add a form in html before the datatable. This form contains an input field for each column to filter.

For example :

<form  id="rozreport_filter_form" class="zdk-form">

      <input type="text" name="date_visit_filter" placeholder="dare work">

      <input type="text" name="model_fr_filter" placeholder="models">

      <input type="text" name="sn_fr_filter" placeholder="serial number">

</form>

Next add an event handler for the change events applied to the filter input fields.

For example :

$('#rozreport_filter_form').on('change', 'input', function(evt) {

   const filters = {};

   const dateVisitVal = $("#rozreport_filter_form input[name=date_visit_filter]").val();

   const modelVal = $("#rozreport_filter_form input[name=model_fr_filter]").val();

   const snVal = $("#rozreport_filter_form input[name=sn_fr_filter]").val();

   if (dateVisitVal !== '') {

      filters.dateVisit = dateVisitVal;

   }

   if (modelVal !== '') {

      filters.model = modelVal;

   }

   if (snVal !== '') {

      filters.sn = snVal;

   }

   // Filter values are sent to the controller action in JSON format

   $("#rozreport_table").zdkdatatable('filterRows', JSON.stringify(filters));

});

Finally, you can retrieve the filter values in your PHP controller action. 

For example :

static protected function action_data() {
   $request = new \Request();

   // Filter values are in JSON format so they have to be decoded first...
   $criteria = json_decode($request->search_criteria, TRUE);

   if (is_array($criteria) {

      // Value for date visit

      $dateVisitVal = $criteria['dateVisit'];

      // Value for model

      $modelVal = $criteria['model'];

      // Value for serial number

      $snVal = $criteria['sn'];

      // Next, apply these filters to your SQL query ...

   }

}

Hoping to have helped you,

Pascal

[MadSmol Qwert]

Thanks for Pascal's previous reply! There is another question about the filter. How do I make it possible to specify the parameters for SQL sampling in one comma-separated filter field? For example snVal='1234,876,998'; the sql query should be:
SELECT * from setup_table_retail_ver2
WHERE setup_table_retail_ver 2.sn LIKE '%1234%' OR setup_table_retail_ver 2.sn LIKE '%876%' OR setup_table_retail_ver 2.sn LIKE '%998%'

How can this be implemented at the controller and model level?

пятница, 28 марта 2025 г. в 00:48:43 UTC+3, ZnetDK:

[ZnetDK]

Hello,

Here is an example of model and controller classes.
I didn't test this code but it should be a good basis for solving your problem.

First, the model class:

<?php
// 'MySnDAO.php' script is installed in the 'znetdk/applications/default/app/model/' folder.
namespace app\model;
class MySnDAO extends \DAO {
protected function initDaoProperties() {        
        $this->query = "SELECT * from setup_table_retail_ver2";
        }

public function setSnAsFilter($snVal) {
/* See https://stackoverflow.com/questions/1127088/mysql-like-in */
$snValAsArray = explode(',', $snVal);
$placeHolders = implode('|', array_fill(0, count($snValAsArray), '?'));
$this->filterClause = "WHERE setup_table_retail_ver 2.sn REGEXP '{$placeHolders}'";
$this->filterValues = $snValAsArray;
}
}

Next, your controller's action method:

<?php
namespace app\controller;
class MyAppCtrl extends \AppController {

    static protected function action_all() {
$request = new \Request();
        $first = $request->first;
        $count = $request->rows;
        $criteria = $request->search_criteria; // Contains for example '1234,876,998'
        $rowsFound = [];
        $response = new \Response();
        $response->total = self::getAll($first, $count, $criteria, $rowsFound);
        $response->rows = $rowsFound;
        $response->success = true;
        return $response;
    }
   
    static protected function getAll($first, $count, $criteria, &$rowsFound) {
    $myDao = new \app\model\MySnDAO();
    if (is_string($criteria)) {
        $myDao->setSnAsFilter($criteria);
        }
$total = $myDao->getCount();
if (!is_null($first) && !is_null($count)) {
$myDao->setLimit($first, $count);
}
while ($row = $myDao->getResult()) {
$rowsFound[] = $row;
}            
        return $total;
    }

}

I hope this answer will be helpful.

Pascal

[MadSmol Qwert]

Pascal, thanks for the clarification, everything worked out!

пятница, 4 апреля 2025 г. в 23:55:20 UTC+3, ZnetDK: