problem displaying data from ajax call to database

lschneiderman

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

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

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

@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

@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

@colin: The HTML is:

lschneiderman

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

lschneiderman

@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

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

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

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

@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

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

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

kthorngren

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

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