Problem loading grid from PHP data source

0 Answers 264 Views
Data Source Grid
Glenn
Top achievements
Rank 1
Veteran
Glenn asked on 19 Mar 2022, 10:53 PM

I'm trying the Kendo grid for the first time and am having a problem loading the data from a query done in PHP. My PHP query returns a data set in JSON format and I've confirmed that it's returning the correct data. But the grid comes up empty, and there's no error message. If I run the query from the PHP file, save the JSON to a file and use that file instead as the "transport/read" parameter of the data source, then the grid is populated correctly.  Is there some setting I need when reading from a PHP script instead of from a data file? Here is my code.


$(document).ready(function() {

    DataSource1 = new kendo.data.DataSource({
      transport: {
        //read: "comfit.php?proc=get_customers",  // This results in an empty grid
        read: 'customers.json',  // This is the same JSON as returned by the PHP, but it displays correctly
        type: 'get',
        dataType: 'json'
      },
      schema: {
        model: {
          fields: {
            full_name: { type: "string" },
            company: { type: "string" },
            id_no: { type: "string" }
          }
       }
      },
      pageSize: 20,
      serverPaging: true,
      serverFiltering: true,
      serverSorting: true
    }),
    $("#grid").kendoGrid({
        dataSource: DataSource1,
        height: 550,
        filterable: true,
        sortable: true,
        pageable: false,
        columns: [
          {
            field: "full_name",
            title: "Name"
          }, {
            field: "company",
            title: "Company"
            }, {
            field: "id_no",
            title: "ID #"
            }
        ]
    });
    //DataSource1.read();
  });

Georgi Denchev
Telerik team
commented on 23 Mar 2022, 10:28 AM

Hi, Glenn,

Thank you for the provided code snippet.

A common problem that can occur when the data is retrieved from the server, is that the casing of the property names may change due to some settings.

Could you please share a small sample(without sensitive data) of the customers.json file, as well as as screenshot from the Network tab when the request is made?

We can then compare the two results and see if there is any noticable difference. If there isn't any, we can investigate for other causes of the problem.

Example of a request from the Network tab in Chrome:

Additionally, could you try turning off the serverPaging/Filtering/Sorting options and see if that makes a difference?

Best Regards,

Georgi

Glenn
Top achievements
Rank 1
Veteran
commented on 23 Mar 2022, 03:08 PM

I'm not sure what you mean by the casing of the property names. Do you mean the field names? That hasn't happened here. I also turned of the server options, which made no difference.

This is a piece of the JSON file.

[{"full_name":"BLOM CHIROPRACTIC ","company":"STEVEN BLOM D.C.'S","id_no":"10003"},
{"full_name":"FAMILY PODIATRY CENTER","company":"DR KAPLAN","id_no":"10007"}]

And here's the screenshot from the network tab. For some reason I wasn't able to insert it into this message so I've posted it on Dropbox. As you can see, there's no difference.

https://www.dropbox.com/s/f1fgapb806hwohs/networkdata.jpg?dl=0

Georgi Denchev
Telerik team
commented on 28 Mar 2022, 11:08 AM

Hi, Glenn,

Thank you for the additional details.

By default, the dataSource expects the data to arrive in a field called "data". This can be configured through the schema.data configuration.

So the correct response from the server should be similar to the following one:

// CURRENT RESPONSE
[{...},{...},{...}]

// EXPECTED RESPONSE
{
 data:[{...},{...},{...}]
}

You could change the schema field to something else, here's an example:

schema: {
 data: "records"
}

In this case the response from the server should look like this:

{
 records:[{...},{...},{...}]
}

Could you try making the above modification and let me know in case the problem continues to persist?

Best Regards,

Glenn
Top achievements
Rank 1
Veteran
commented on 28 Mar 2022, 04:31 PM | edited

I tried that and it made no difference, the grid was still empty. The header with the field names shows correctly, as it did before (sorry, I didn't mention this earlier and don't know if it matters). Is this default documented anywhere? I couldn't find it. Not only that, but I have previously tested the ListView and the data source did not have any field name, and it worked with no problem.
Georgi Denchev
Telerik team
commented on 31 Mar 2022, 11:35 AM

Hi, Glenn,

Could you try specifying the schema.data as a function and then console.log the response to see if it appears:

schema: {
 data: function(response) {
  console.log(response); // This should display the array of data(a javascript array, not json string) in the console(F12)
  return response;
 }
}

If the response is properly logged on the console, could you take a screenshot of it and share it here? You can hide any senstive information.

As for the "data" question, the information does seem to be missing, I'll find an appropriate place in the documentation to mention it.

Best Regards,

Georgi

Glenn
Top achievements
Rank 1
Veteran
commented on 31 Mar 2022, 03:29 PM

It is displaying as a JSON string. Is that a problem? Here's a piece of it

https://www.dropbox.com/s/kdh2wo3v05menft/data.jpg?dl=0

Re the "data" question, as I mentioned this was not required when I used the ListView. And in this case it made no difference. Can you explain that?

 

Georgi Denchev
Telerik team
commented on 05 Apr 2022, 01:03 PM

Hi, Glenn,

The DataSource needs to receive a JavaScript array in order to render the Grid rows, so you'll have to parse the data before you return it:

parse: function(data) {
  return JSON.parse(data);
}

By default if nothing is specified for the schema.data configuration, it will default to "data" or nothing at all. The widgets should work as expected without having to specify it, however I wanted to make sure that the problem wasn't coming from there so I wouldn't provide you with incorrect solution.

Let me know in case the problem continues to persist.

Best Regards,

Georgi

Glenn
Top achievements
Rank 1
Veteran
commented on 05 Apr 2022, 02:13 PM

That worked but where is it ever mentioned in the documentation that it requires a JS array instead of JSON? Look at the section headed "Binding to Remote Data Services" on this page: https://docs.telerik.com/kendo-ui/framework/datasource/overview. There is no JSON.parse here. Not only that but in order to get it to work I had to remove the "data" field name, because that causes the parse to fail. And why did you not mention this much earlier after you had initially seen my snippet of JSON? And finally, as I asked before, why was none of this necessary when I used the DataSource with the ListView? I am very frustrated with the level of support I've gotten on this issue because it is something so basic that should work easily. I have been looking at other grids and will probably not be using Kendo.
Georgi Denchev
Telerik team
commented on 08 Apr 2022, 11:54 AM

Hi, Glenn,

I apologize for the unsatisfactory experience that you had in this thread.

The initially shared snippet showcased a JSON array of objects, unfortunately there was no way to determine that it was an unparsed JSON string. The DataSource can be bound directly to a JSON array, however if the data comes in a different format(in this case as a JSON string) it needs to be parsed manually. The example showcases an array of objects instead of a string, that is why no JSON.parse is present in it.

The reason the last screenshot was helpful, was because if the response was an array of objects, it would appear as one in the console, however if was a string it would appear as text:

In a code block, the following snippet could be both:

// This looks like a regular array
[{"full_name":"BLOM CHIROPRACTIC ","company":"STEVEN BLOM D.C.'S","id_no":"10003"},
{"full_name":"FAMILY PODIATRY CENTER","company":"DR KAPLAN","id_no":"10007"}]

All of the Databound widgets(such as Grid, ListView, etc.) operate in the same manner. I cannot say why the ListView worked and the Grid didn't, without knowing what the server does exactly. My only guess would be that in one case the JSON was returned directly and in the other(the current one) it was stringified before it was returned, hence the need for a parse method.

You can take a look at the following SO thread which showcases two examples, one with data that needs to be parsed and one that will be automatically parsed by the browser:

https://stackoverflow.com/a/16837196 

We are also in the process of revamping certain Docs articles to make the more clear and to the point. I understand that the documentation you followed has most likely been confusing and I apologize about that. Custom feedback is always very important to us as it helps us determine what needs to be changed/updated.

If you're currently in an evaluation process, we encourage you to begin a free trial which gives you access to the private ticket system. Not only is the response time much lower, but you'll also be able to share samples that may contain sensitive information without the fear of them becoming public. The more information we have, the faster the issue can be resolved.

I apologize once again for the poor experience that you had in this thread.

Let me know if there's anything else.

Best Regards,

Georgi

Glenn
Top achievements
Rank 1
Veteran
commented on 08 Apr 2022, 04:19 PM

Thanks for the apology and the additional details. At this point I've decided to use a grid from a different vendor (not only because of this issue but due to some other features as well). Regarding the listview, I was trying it because I may need that type of interface for another project later. My initial test with the listview was with different data, but I decided to try it with the same data I'm using in the grid. In response to what you said about JSON arrays vs. strings, I created a test page for the listview using exactly the same data source as in the grid test. In the listview test, the data was returned as an array, as opposed to the grid where as you saw it was returned as a string. I confirmed this by putting a breakpoint in the parse function and looking at the data at that point. Here's the data source code. How can you explain this difference?

DataSource1 = new kendo.data.DataSource({
  transport: {
    read: {
      url: 'comfit.php?proc=get_customers',
      dataType: "json"
    }
  },
  schema: {
      parse: function(data) {
        // In the grid test I had to use this. In the listview test, this was unnecessary.

        return JSON.parse(data);
      }
  }
 });

Georgi Denchev
Telerik team
commented on 13 Apr 2022, 08:45 AM

Hi, Glenn,

I can't provide an accurate guess without debugging the problematic code/sample/project myself. If the exact same dataSource with the exact same configurations is used, there shouldn't be a difference in the requests. The only way for me to provide you with concrete information is to debug the entire process, from the initial request to the server method which handles the request and to the response.

Could you provide me with the logic for the comfit.php file? In particular the get_customers method. I can try to configure a sample on my end and test both a grid and a listview to see if the problem is reproducable.

Best Regards,

Georgi

Glenn
Top achievements
Rank 1
Veteran
commented on 13 Apr 2022, 01:29 PM

Here's the code for get_customers:

switch ($proc) {

  case 'get_customers' :
    $s = '[';
    $SQL = "SELECT COMPANY, FULL_NAME, ID_NO FROM CUSTOMERS ORDER BY FULL_NAME";
    $result1 = sqlsrv_query($conn, $SQL);
    while($row = sqlsrv_fetch_array($result1, SQLSRV_FETCH_BOTH)) {
      $s = $s . '{"full_name":"' . $row['FULL_NAME'] . '","company":"' . $row['COMPANY'] . '","id_no":"' . $row['ID_NO'] . '"},';
    }
    $s = substr($s, 0, strlen($s)-1) . ']';  // strip trailing comma
    echo $s;
    break;

}

Georgi Denchev
Telerik team
commented on 18 Apr 2022, 10:20 AM

Hi, Glenn,

Thank you for the provided code snippet.

Based on what I can see, the result is always of string type which means that the server never returns a JSON array. I am not sure if there is any extra logic which parses the string before the server returns it, but based on the provided information so far I would guess not.

Neither the ListView, nor the Grid can be bound to a JSON string, you can check the following Dojo:

https://dojo.telerik.com/@gdenchev/aWIYEZuv 

The Grid and ListView are bound to the exact same dataSource which has its data configuration set to a string. If you don't parse the response, neither of the widgets will display the data:

            data: function(response) {
              return response; // Not parsed
              //return JSON.parse(response);
            },

 

Here's an example of the logic from the PHP wrappers which build and return an array of data instead of a string:

$result = array();

$sql = 'SELECT field FROM table'; // shortened
$statement = $this->db->prepare($sql);
$statement->execute();
$data = $statement->fetchAll(PDO::FETCH_ASSOC);
$result['data'] = $data;

return $result; // Return an array instead of a string

And then you would echo the above function:

echo json_encode(myFetchFunction());

Using this approach, there would be no need to parse the response inside the schema.data function. However if a string is returned, you'll have to parse it no matter what widget you're using.

Best Regards,

Georgi

No answers yet. Maybe you can help?

Tags
Data Source Grid
Asked by
Glenn
Top achievements
Rank 1
Veteran
Share this question
or