Telerik Forums / Kendo UI for jQuery
This is a migrated thread and some comments may be shown as answers.

Grid filter values above 999 returning zero results

3 Answers 61 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Duke
Top achievements
Rank 1
Duke asked on 28 May 2013, 05:22 PM
Hey everyone!

I'm dealing with a problem on my grid filter functions. I'm using the DataSourceResults class for PHP to work with an MySQL table.

Sorting and Filtering is actually working fine exept for values above 999. The array that gets send to my server looks as the following:

Array
(
    [callback] => jQuery110007109804890166557_1369760652926
    [take] => 5
    [skip] => 0
    [page] => 1
    [pageSize] => 5
    [filter] => Array
        (
            [filters] => Array
                (
                    [0] => Array
                        (
                            [field] => cID
                            [operator] => eq
                            [value] => 1000
                        )
 
                )
 
            [logic] => and
        )
 
    [_] => 1369760652934
)
jQuery110007109804890166557_1369760652926({"total":0,"data":[]})
Where cID is defined as an smallint(6) unsigned in my database. If I query the SQL statement:
Select cID from ticketing_index WHERE cID = '1000'
I get the right result. Funny thing, everything between 0 and 999 is also working!

What did I miss?

Thank you very much!

3 Answers, 1 is accepted

Sort by
0
Duke
Top achievements
Rank 1
answered on 29 May 2013, 06:44 AM
Good Morning everybody!

I thought I should take a look deeper in whats going on within this DataSourceResults Object and Found something intressting:

1. Filter the cID Column with 1000 generates the following statement:
PDOStatement Object
(
    [queryString] => SELECT cID, dID, tID, tTitle, tDesc, oDate, eDate, cDate, tKeywords FROM jar_ticketing_index WHERE (date(cID) = date(:filter0)) ORDER BY cID ASC
)
2. Filter the cID Column with 100 generates the following statement:
PDOStatement Object
(
    [queryString] => SELECT cID, dID, tID, tTitle, tDesc, oDate, eDate, cDate, tKeywords FROM jar_ticketing_index WHERE (cID = :filter0) ORDER BY cID ASC
)
Now the Questin is: Why is your DataSourceResults Class Object thinking my value is a date, if the value is higher than 999?

The Column cID is defined as number, not as date!
$("#mainSupportgrid").kendoGrid({
        dataSource: {
            transport: {
                read: {
                    url:"http://labserver01/API/ticketing/list/",
                    dataType: "jsonp",
                    contentType: "application/json; charset=utf-8"
                }
            },
            schema: {
                data: "data",
                total: "total",
                model: {
                    fields: {
                        cID: { type: "number" },
                        dID: { type: "number" },
                        tID: { type: "number" },
                        tTitle: { type: "string" },
                        oDate: { type: "date" },
                        eDate: { type: "date" },
                        cDate: { type: "date" },
                        tKeywords: { type: "string" }
                    }
                }
            },
            pageSize: 5,
            serverPaging: true,
            serverFiltering: true,
            serverSorting: true
        },
        height: 200,
        filterable: true,
        sortable: true,
        selectable: true,
        pageable: true,
        columns: [{
                field:"cID",
                title:"Kunden ID",
                width:"100px",
                filterable: true
            },{
                field:"dID",
                title:"Geräte ID",
                width:"100px",
                filterable: true
            },{
                field:"tID",
                title:"Ticket ID",
                width:"100px",
                filterable: true
            },{
                field: "tTitle",
                title: "Ticket Bezeichnung"
            }, {
                field: "oDate",
                title: "Ticket eröffnet",
                format: "{0:dd.MM.yyyy}",
                width: "160px"
            }, {
                field: "eDate",
                title: "Ticket escaliert",
                format: "{0:dd.MM.yyyy}",
                width: "160px"
            }, {
                field: "cDate",
                title: "Ticket geschlossen",
                format: "{0:dd.MM.yyyy}",
                width: "160px"
 
            }, {
                field: "tKeywords",
                title: "keywords"
            }]
    });
Your help is appreciated.

Greetings
0
Duke
Top achievements
Rank 1
answered on 29 May 2013, 09:26 AM
Ok folks, seems like its a Bug within your DataSourceResults.php Class.

You check the values if these are date's or not, depending on existing error keys here:
private function isDate($value) {
        $result = date_parse($value);
        return $result["error_count"] < 1;
    }
But if you pass as value '1000', the result array looks like the following:
Array
(
    [year] =>
    [month] =>
    [day] =>
    [hour] => 10
    [minute] => 0
    [second] => 0
    [fraction] =>
    [warning_count] => 0
    [warnings] => Array
        (
        )
 
    [error_count] => 0
    [errors] => Array
        (
        )
 
    [is_localtime] =>
)
So the functon you have been using here, date_parse() isn't working as expected. I'm working on a solution for this one. If someone allready figrued this out, please post your solution.
0
Accepted
Duke
Top achievements
Rank 1
answered on 29 May 2013, 11:01 AM
Okay everyone, I got a working solution.

You have to modify two things within the DataSourceResult.php:

I extended the class method isDate, so I'm gona check if its really a valid date:
private function isDate($value) {
        $result = date_parse($value);
        // <-- FIX: False positive strings
        if (checkdate($result["month"], $result["day"], $result["year"])){
            return true;
        }else{
            $result["error_count"] = '1';
            return $result["error_count"] < 1;
        }
        // -->
    }
This was resolving my original problem with the ID filtering above 999. As from what I've seen, also the date values could not be run corretly against my mysql timestamps. To solve this I modifyed the class method where:
if ($this->isDate($filter->value)) {
     // <-- FIX: convert timeformat to mysql timestamp
     $filter->value = date('Y-m-d H:i:s',strtotime($filter->value));
      // -->
 
      $field = "date($field)";
      $value = "date($value)";
}
Hope this one helps somebody out! If there is a better alternative I'm glad to read your feedback.

Greetings

eXe
Tags
Grid
Asked by
Duke
Top achievements
Rank 1
Answers by
Duke
Top achievements
Rank 1
Share this question
or