problem displaying data from ajax call to database

lschneiderman · May 2018

I'm having trouble displaying data using an ajax call to a database. The data is coming back as json - that part is working. But I'm getting this error:

Uncaught TypeError: Cannot read property 'length' of undefined
at ub (jquery.dataTables.min.js:39)
at jquery.dataTables.min.js:37
at i (jquery.dataTables.min.js:35)
at Object.success (jquery.dataTables.min.js:35)
at l (jquery.min.js:4)
at Object.fireWith [as resolveWith] (jquery.min.js:4)
at k (jquery.min.js:6)
at XMLHttpRequest.<anonymous> (jquery.min.js:6)

My html looks like this:

<table id="dataTable" class="display" style="width:100%; background-color:white;">
             <thead>
                <tr>
                    <th>Lot ID</th>
                    <th>Lot, Block</th>
                    <th>Parcel number</th>
                    <th>Street number</th>
                    <th>Street</th>
                    <th>Secondary address</th>
                    <th>Owner</th>
                    <th>Date bought</th>
                    <th>Assessed value</th>
                    <th>Buyout paid</th>
                    <th>Date of URA buyout</th>
                    <th>Description</th>
                    <th>Rent</th>
                    <th>Improvements</th>
                    <th>Condition</th>
                    <th>Notes</th>
                    <th>Date built</th>
                    <th>Images</th>
                </tr>
            </thead>
            </table>

my jquery looks like this:

$('#dataTable').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "php/getAllLots.php",
            "type": "POST"
        },
        "columns": [
            { "data": "LotID" },
            { "data": "lot_block" },
            { "data": "parcel_num" },
            { "data": "street_num" },
            { "data": "street" },
            { "data": "secondary_address" },
            { "data": "owner" },
            { "data": "date_bought" },
            { "data": "assessed_value" },
            { "data": "date_URA_paid" },
            { "data": "site_description" },
            { "data": "rent" },
            { "data": "improvements" },
            { "data": "building_condition" },
            { "data": "elaborations" },
            { "data": "date_built" },
            { "data": "images" }
            
        ]
    } );

The json returned from my php page looks like this:

[{"LotID":"1","lot_block":"2-F, 83","parcel_num":"200","street_num":"98","street":"Washington Place","secondary_address":"","owner":"Anna Raffaele, 283 Conewanta St., Pittsburgh","date_bought":"Mar 16, 1923","assessed_value":"10300","URA_buyout":"11600","date_URA_paid":"Dec 12, 1955","site_description":"Two- and three-story brick apartment and dwelling in rear, space heaters, electric lighting, copper and galvanized iron plumbing, cement cellar, stone foundation. ","rent":"First, second and third floor - $40\/month each. Rear building - $25\/month","improvements":"\"1900 front building.\"","building_condition":"Fair to poor","elaborations":"\"Interior condition does not justify income. Frame extensions rear for baths bad condition.\"","date_built":"1880","images":"DSC_3989,DSC_3989b"},{"LotID":"2","lot_block":"2-F, 82","parcel_num":"201","street_num":"100","street":"Washington Place ","secondary_address":"100 Yuba Way","owner":"Owners\/occupants P. and G. Sacco","date_bought":"Apr 5, 1924","assessed_value":"5500","URA_buyout":"8600","date_URA_paid":"Dec 12, 1955","site_description":"One-story brick store and two-story brick dwelling, gas-fired heat, no lighting or plumbing, cement cellar, stone foundation. ","rent":"Rear dwelling - $52\/month","improvements":"\"Minimum.\" Installed gas-fired heat in the store.","building_condition":"Fair","elaborations":"\"Bad floor plan in rear house, half now vacant. Will never be rented to advantage. Owner-occupied one story brick store for 40 years.\"","date_built":"1880","images":"DSC_3992, DSC_3993"}]

...except there are 300 records returned.

Can anyone tell me what I'm doing wrong please?

EDIT: Updated code to use Markdown code formatting.

kthorngren · May 2018

Looks like your JSON response does not match the format required by Datatables. You have "serverSide": true, which has the requirements described in this page:
https://datatables.net/manual/server-side

If you have only 300 records then you shouldn't need server side processing. You can remove that config option and use the ajax.dataSrc to tell Datatables how to access your JSON data. The second example is what you want to use.

Kevin

colin · May 2018

Yep, as Kevin said, the data isn't matching. There's 18 columns defined in the HTML, but only 17 in the JS column definitions.

C

lschneiderman · May 2018

@colin: I've simplified the problem to return one field, but I'm still getting the same error message.
$('#dataTable').DataTable( {
"processing": true,
"serverSide": true,
"ajax": {
"url": "php/getAllLots.php",
"type": "POST"
},
"columns": [
{ "data": "LotID" }
]
} );

html:

lschneiderman · May 2018

@kthorngren: I'm confused. I don't see -option ajax.dataSrc listed on that page or with the examples. If I'm accessing a database, don't I need server-side processing?

lschneiderman · May 2018

@colin: The HTML is:

lschneiderman · May 2018

<table id="dataTable" class="display"></table>

lschneiderman · May 2018

@kthorngren: I've also tried this, which throws no error, but also gives no results:

$('#dataTable').DataTable( { "processing": true, "serverSide": true, "ajax": "php/getAllLots.php", "dataSrc": function(json){ return {"data": json }; }, "columns": [ { "data": "LotID" } ] } );

kthorngren · May 2018

I'm confused. I don't see -option ajax.dataSrc listed
Sorry, fFat fingered my response. Should have looked like this ajax.dataSrc.

The first questions to answer is do you need server side processing and is your server script setup to handle the requirements of server side processing?

If you have 300 records and based on the JSON response you provided the answer to both would be no. I suggest trying this:

$('#dataTable').DataTable( {
        //"processing": true,
        //"serverSide": true,
        "ajax": {
            "url": "php/getAllLots.php",
            "type": "POST",
            "dataSrc": ""
        },
        "columns": [
            { "data": "LotID" },
            { "data": "lot_block" },
            { "data": "parcel_num" },
            { "data": "street_num" },
            { "data": "street" },
            { "data": "secondary_address" },
            { "data": "owner" },
            { "data": "date_bought" },
            { "data": "assessed_value" },
            { "data": "URA_buyout" },   //looks like this field is missing
            { "data": "date_URA_paid" },
            { "data": "site_description" },
            { "data": "rent" },
            { "data": "improvements" },
            { "data": "building_condition" },
            { "data": "elaborations" },
            { "data": "date_built" },
            { "data": "images" }
             
        ]
    } );

Remove the serverSide and processing options. Also as Colin mentioned you have 18 columns defined in your table but only 17 in your Datatable. Looks like you are missing URA_buyout.

Kevbin

lschneiderman · May 2018

Hi, Kevin,

I took out the serverSide and processing options and narrowed the fields to one, but I'm still getting the error: Uncaught TypeError: Cannot read property 'length' of undefined

$('#dataTable').DataTable( {
        //"processing": true,
        //"serverSide": true,
        "ajax": "php/getAllLots.php",
        "dataSrc":"",
        "columns": [
            { "data": "LotID" }
        ]
    } );

html:
<table id="dataTable" class="dataTable" style="width: 100%; background-color:white;"> <thead> <tr> <th>Lot ID</th> </tr> </thead> <tbody> </tbody> </table>

It works fine when I include the data as a line of javascript:

```var myJson = [{"LotID":"1"},{"LotID":"2"},{"LotID":"3"},{"LotID":"4"},{"LotID":"5"},{"LotID":"6"},{"LotID":"7"},{"LotID":"8"},{"LotID":"9"},{"LotID":"10"},{"LotID":"11"},{"LotID":"12"},{"LotID":"13"},{"LotID":"14"},{"LotID":"15"},{"LotID":"16"},{"LotID":"17"},{"LotID":"18"},{"LotID":"19"},{"LotID":"20"},{"LotID":"21"},{"LotID":"22"},{"LotID":"23"},{"LotID":"24"},{"LotID":"25"},{"LotID":"26"},{"LotID":"27"},{"LotID":"28"},{"LotID":"29"},{"LotID":"30"},{"LotID":"31"},{"LotID":"32"},{"LotID":"33"},{"LotID":"34"},{"LotID":"35"},{"LotID":"36"},{"LotID":"37"},{"LotID":"38"},{"LotID":"39"},{"LotID":"40"},{"LotID":"41"},{"LotID":"42"},{"LotID":"43"},{"LotID":"44"},{"LotID":"45"},{"LotID":"46"},{"LotID":"47"},{"LotID":"48"},{"LotID":"49"},{"LotID":"50"},{"LotID":"51"},{"LotID":"52"},{"LotID":"53"},{"LotID":"54"},{"LotID":"55"},{"LotID":"56"},{"LotID":"57"},{"LotID":"58"},{"LotID":"59"},{"LotID":"60"},{"LotID":"61"},{"LotID":"62"},{"LotID":"63"},{"LotID":"64"},{"LotID":"65"},{"LotID":"66"},{"LotID":"67"},{"LotID":"68"},{"LotID":"69"},{"LotID":"70"},{"LotID":"71"},{"LotID":"72"},{"LotID":"73"},{"LotID":"74"},{"LotID":"75"},{"LotID":"76"},{"LotID":"78"},{"LotID":"79"},{"LotID":"80"},{"LotID":"81"},{"LotID":"82"},{"LotID":"83"},{"LotID":"84"},{"LotID":"85"},{"LotID":"86"},{"LotID":"87"},{"LotID":"88"},{"LotID":"89"},{"LotID":"90"},{"LotID":"91"},{"LotID":"92"},{"LotID":"93"},{"LotID":"94"},{"LotID":"95"},{"LotID":"96"},{"LotID":"97"},{"LotID":"98"},{"LotID":"99"},{"LotID":"100"},{"LotID":"101"},{"LotID":"102"},{"LotID":"103"},{"LotID":"104"},{"LotID":"105"},{"LotID":"106"},{"LotID":"107"},{"LotID":"108"},{"LotID":"109"},{"LotID":"110"},{"LotID":"111"},{"LotID":"112"},{"LotID":"113"},{"LotID":"114"},{"LotID":"115"},{"LotID":"116"},{"LotID":"117"},{"LotID":"118"},{"LotID":"119"},{"LotID":"120"},{"LotID":"121"},{"LotID":"122"},{"LotID":"123"},{"LotID":"124"},{"LotID":"125"},{"LotID":"126"},{"LotID":"127"},{"LotID":"128"},{"LotID":"129"},{"LotID":"130"},{"LotID":"131"},{"LotID":"132"},{"LotID":"133"},{"LotID":"134"},{"LotID":"135"},{"LotID":"136"},{"LotID":"137"},{"LotID":"138"},{"LotID":"139"},{"LotID":"140"},{"LotID":"141"},{"LotID":"142"},{"LotID":"143"},{"LotID":"144"},{"LotID":"145"},{"LotID":"146"},{"LotID":"147"},{"LotID":"148"},{"LotID":"149"},{"LotID":"150"},{"LotID":"151"},{"LotID":"152"},{"LotID":"153"},{"LotID":"154"},{"LotID":"155"},{"LotID":"156"},{"LotID":"157"},{"LotID":"158"},{"LotID":"159"},{"LotID":"160"},{"LotID":"161"},{"LotID":"162"},{"LotID":"163"},{"LotID":"164"},{"LotID":"165"},{"LotID":"166"},{"LotID":"167"},{"LotID":"168"},{"LotID":"169"},{"LotID":"170"},{"LotID":"171"},{"LotID":"172"},{"LotID":"173"},{"LotID":"174"},{"LotID":"175"},{"LotID":"176"},{"LotID":"177"},{"LotID":"178"},{"LotID":"179"},{"LotID":"180"},{"LotID":"181"},{"LotID":"182"},{"LotID":"183"},{"LotID":"184"},{"LotID":"185"},{"LotID":"186"},{"LotID":"187"},{"LotID":"188"},{"LotID":"189"},{"LotID":"190"},{"LotID":"191"},{"LotID":"192"},{"LotID":"193"},{"LotID":"194"},{"LotID":"195"},{"LotID":"196"},{"LotID":"197"},{"LotID":"198"},{"LotID":"199"},{"LotID":"200"},{"LotID":"201"},{"LotID":"202"},{"LotID":"203"},{"LotID":"204"},{"LotID":"205"},{"LotID":"206"},{"LotID":"207"},{"LotID":"208"},{"LotID":"209"},{"LotID":"210"},{"LotID":"211"},{"LotID":"212"},{"LotID":"213"},{"LotID":"214"},{"LotID":"215"},{"LotID":"216"},{"LotID":"217"},{"LotID":"218"},{"LotID":"219"},{"LotID":"220"},{"LotID":"221"},{"LotID":"222"},{"LotID":"223"},{"LotID":"224"},{"LotID":"225"},{"LotID":"226"},{"LotID":"227"},{"LotID":"228"},{"LotID":"229"},{"LotID":"230"},{"LotID":"231"},{"LotID":"232"},{"LotID":"233"},{"LotID":"234"},{"LotID":"235"},{"LotID":"236"},{"LotID":"237"},{"LotID":"238"},{"LotID":"239"},{"LotID":"240"},{"LotID":"241"},{"LotID":"242"},{"LotID":"243"},{"LotID":"244"},{"LotID":"245"},{"LotID":"246"},{"LotID":"247"},{"LotID":"248"},{"LotID":"249"},{"LotID":"250"},{"LotID":"251"},{"LotID":"252"},{"LotID":"253"},{"LotID":"254"},{"LotID":"255"},{"LotID":"256"},{"LotID":"257"},{"LotID":"258"},{"LotID":"259"},{"LotID":"260"},{"LotID":"261"},{"LotID":"262"},{"LotID":"263"},{"LotID":"264"},{"LotID":"265"},{"LotID":"266"},{"LotID":"267"},{"LotID":"268"},{"LotID":"269"},{"LotID":"270"},{"LotID":"271"},{"LotID":"272"},{"LotID":"273"},{"LotID":"274"},{"LotID":"275"},{"LotID":"276"},{"LotID":"277"},{"LotID":"278"},{"LotID":"279"},{"LotID":"280"},{"LotID":"281"},{"LotID":"282"},{"LotID":"283"},{"LotID":"284"},{"LotID":"285"},{"LotID":"286"},{"LotID":"287"},{"LotID":"288"},{"LotID":"289"},{"LotID":"290"},{"LotID":"291"},{"LotID":"292"},{"LotID":"293"},{"LotID":"294"},{"LotID":"295"},{"LotID":"296"},{"LotID":"297"},{"LotID":"298"},{"LotID":"299"},{"LotID":"300"},{"LotID":"301"}];

$('#dataTable').DataTable( {
    data: myJson,
    columns: [
        { data: "LotID" }
    ]
} );

```

kthorngren · May 2018

Hmm, I would suspect something in the data being returned. Maybe you can collect debugger and post the link provided when you run it.

Kevin

lschneiderman · May 2018

@kthorngren: The data being returned from the php page looks like this:
[{"LotID":"1"},{"LotID":"2"},{"LotID":"3"},{"LotID":"4"},{"LotID":"5"},{"LotID":"6"},{"LotID":"7"},{"LotID":"8"},{"LotID":"9"},{"LotID":"10"},{"LotID":"11"},{"LotID":"12"},{"LotID":"13"},{"LotID":"14"},{"LotID":"15"},{"LotID":"16"},{"LotID":"17"},{"LotID":"18"},{"LotID":"19"},{"LotID":"20"},{"LotID":"21"},{"LotID":"22"},{"LotID":"23"},{"LotID":"24"},{"LotID":"25"},{"LotID":"26"},{"LotID":"27"},{"LotID":"28"},{"LotID":"29"},{"LotID":"30"},{"LotID":"31"},{"LotID":"32"},{"LotID":"33"},{"LotID":"34"},{"LotID":"35"},{"LotID":"36"},{"LotID":"37"},{"LotID":"38"},{"LotID":"39"},{"LotID":"40"},{"LotID":"41"},{"LotID":"42"},{"LotID":"43"},{"LotID":"44"},{"LotID":"45"},{"LotID":"46"},{"LotID":"47"},{"LotID":"48"},{"LotID":"49"},{"LotID":"50"},{"LotID":"51"},{"LotID":"52"},{"LotID":"53"},{"LotID":"54"},{"LotID":"55"},{"LotID":"56"},{"LotID":"57"},{"LotID":"58"},{"LotID":"59"},{"LotID":"60"},{"LotID":"61"},{"LotID":"62"},{"LotID":"63"},{"LotID":"64"},{"LotID":"65"},{"LotID":"66"},{"LotID":"67"},{"LotID":"68"},{"LotID":"69"},{"LotID":"70"},{"LotID":"71"},{"LotID":"72"},{"LotID":"73"},{"LotID":"74"},{"LotID":"75"},{"LotID":"76"},{"LotID":"78"},{"LotID":"79"},{"LotID":"80"},{"LotID":"81"},{"LotID":"82"},{"LotID":"83"},{"LotID":"84"},{"LotID":"85"},{"LotID":"86"},{"LotID":"87"},{"LotID":"88"},{"LotID":"89"},{"LotID":"90"},{"LotID":"91"},{"LotID":"92"},{"LotID":"93"},{"LotID":"94"},{"LotID":"95"},{"LotID":"96"},{"LotID":"97"},{"LotID":"98"},{"LotID":"99"},{"LotID":"100"},{"LotID":"101"},{"LotID":"102"},{"LotID":"103"},{"LotID":"104"},{"LotID":"105"},{"LotID":"106"},{"LotID":"107"},{"LotID":"108"},{"LotID":"109"},{"LotID":"110"},{"LotID":"111"},{"LotID":"112"},{"LotID":"113"},{"LotID":"114"},{"LotID":"115"},{"LotID":"116"},{"LotID":"117"},{"LotID":"118"},{"LotID":"119"},{"LotID":"120"},{"LotID":"121"},{"LotID":"122"},{"LotID":"123"},{"LotID":"124"},{"LotID":"125"},{"LotID":"126"},{"LotID":"127"},{"LotID":"128"},{"LotID":"129"},{"LotID":"130"},{"LotID":"131"},{"LotID":"132"},{"LotID":"133"},{"LotID":"134"},{"LotID":"135"},{"LotID":"136"},{"LotID":"137"},{"LotID":"138"},{"LotID":"139"},{"LotID":"140"},{"LotID":"141"},{"LotID":"142"},{"LotID":"143"},{"LotID":"144"},{"LotID":"145"},{"LotID":"146"},{"LotID":"147"},{"LotID":"148"},{"LotID":"149"},{"LotID":"150"},{"LotID":"151"},{"LotID":"152"},{"LotID":"153"},{"LotID":"154"},{"LotID":"155"},{"LotID":"156"},{"LotID":"157"},{"LotID":"158"},{"LotID":"159"},{"LotID":"160"},{"LotID":"161"},{"LotID":"162"},{"LotID":"163"},{"LotID":"164"},{"LotID":"165"},{"LotID":"166"},{"LotID":"167"},{"LotID":"168"},{"LotID":"169"},{"LotID":"170"},{"LotID":"171"},{"LotID":"172"},{"LotID":"173"},{"LotID":"174"},{"LotID":"175"},{"LotID":"176"},{"LotID":"177"},{"LotID":"178"},{"LotID":"179"},{"LotID":"180"},{"LotID":"181"},{"LotID":"182"},{"LotID":"183"},{"LotID":"184"},{"LotID":"185"},{"LotID":"186"},{"LotID":"187"},{"LotID":"188"},{"LotID":"189"},{"LotID":"190"},{"LotID":"191"},{"LotID":"192"},{"LotID":"193"},{"LotID":"194"},{"LotID":"195"},{"LotID":"196"},{"LotID":"197"},{"LotID":"198"},{"LotID":"199"},{"LotID":"200"},{"LotID":"201"},{"LotID":"202"},{"LotID":"203"},{"LotID":"204"},{"LotID":"205"},{"LotID":"206"},{"LotID":"207"},{"LotID":"208"},{"LotID":"209"},{"LotID":"210"},{"LotID":"211"},{"LotID":"212"},{"LotID":"213"},{"LotID":"214"},{"LotID":"215"},{"LotID":"216"},{"LotID":"217"},{"LotID":"218"},{"LotID":"219"},{"LotID":"220"},{"LotID":"221"},{"LotID":"222"},{"LotID":"223"},{"LotID":"224"},{"LotID":"225"},{"LotID":"226"},{"LotID":"227"},{"LotID":"228"},{"LotID":"229"},{"LotID":"230"},{"LotID":"231"},{"LotID":"232"},{"LotID":"233"},{"LotID":"234"},{"LotID":"235"},{"LotID":"236"},{"LotID":"237"},{"LotID":"238"},{"LotID":"239"},{"LotID":"240"},{"LotID":"241"},{"LotID":"242"},{"LotID":"243"},{"LotID":"244"},{"LotID":"245"},{"LotID":"246"},{"LotID":"247"},{"LotID":"248"},{"LotID":"249"},{"LotID":"250"},{"LotID":"251"},{"LotID":"252"},{"LotID":"253"},{"LotID":"254"},{"LotID":"255"},{"LotID":"256"},{"LotID":"257"},{"LotID":"258"},{"LotID":"259"},{"LotID":"260"},{"LotID":"261"},{"LotID":"262"},{"LotID":"263"},{"LotID":"264"},{"LotID":"265"},{"LotID":"266"},{"LotID":"267"},{"LotID":"268"},{"LotID":"269"},{"LotID":"270"},{"LotID":"271"},{"LotID":"272"},{"LotID":"273"},{"LotID":"274"},{"LotID":"275"},{"LotID":"276"},{"LotID":"277"},{"LotID":"278"},{"LotID":"279"},{"LotID":"280"},{"LotID":"281"},{"LotID":"282"},{"LotID":"283"},{"LotID":"284"},{"LotID":"285"},{"LotID":"286"},{"LotID":"287"},{"LotID":"288"},{"LotID":"289"},{"LotID":"290"},{"LotID":"291"},{"LotID":"292"},{"LotID":"293"},{"LotID":"294"},{"LotID":"295"},{"LotID":"296"},{"LotID":"297"},{"LotID":"298"},{"LotID":"299"},{"LotID":"300"},{"LotID":"301"}]

kthorngren · May 2018

I updated your post to fix the Markdown. You need to put the ticks (```) on a line by themselves. Anyway you need to put the ajax.dataSrc within the ajax option, like this:

        "ajax": {
            "url": "php/getAllLots.php",
            "type": "POST",
            "dataSrc": ""
        },

Kevin

lschneiderman · May 2018

@kthorngren : Do jquery dataTables actually work with a database that's queried with php please?

kthorngren · May 2018

Do jquery dataTables actually work with a database that's queried with php please?

The server code does not matter, I use Python. What matters is the returned data meets the Datatables required structure.

Kevin

colin · May 2018

I just tried that in this live example here, and everything is behaving as expected. It would be worth checking your libraries, comparing them with that example, ensuring they're in the right order.

C

problem displaying data from ajax call to database

problem displaying data from ajax call to database

Answers

Howdy, Stranger!

Categories

DataTables

problem displaying data from ajax call to database

problem displaying data from ajax call to database

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables