Linking DataTables with sql database using Nodejs

Question

I hope you can help me because I'm driving mad about this question. I'm not experienced with Node, Ajax and DataTables, so this is likely a my fault. I have an html table linked to Datatables. On load document I send a POST request to get data from an MySQL database. If I popolate DataTable with row.add() method all works fine, but if the table grows I could have some slowdowns with this system, so I would like to link the DataTable table with the database so that the program loads only the records to show. Now problem starts. In my Node file, if I return sql data like so:

res.json(result);

my DataTable shows all the data, but table hasn't right number of record (below the table shows "Showing 0 to 0 of 0 entries (filtered from NaN total entries)" and, even worst, the table has infinite pages, all displaying the same data (the first 10 records). If instead I return an ajax:

ret = JSON.stringify({
    "draw": req.body.draw,
    "recordsTotal": result[0]['COUNT(cf)'],
    "recordsFiltered": result[0]['COUNT(cf)'],
    "data": dati
});
res.send(ret);

DataTables shows the correct number of records in the info line at bottom of table, but no data is displayed. In console I show the ret object and it seems all right. I'm no idea what to do now.

Mario Vitale · Accepted Answer · 2018-10-10 20:30:34Z

1

I resolved!!! It's incredible after near 2 days of attempts. In html file I put in DataTables ajax :

[...]
dataSrc: 'dati',
[...]

Then in my Nodejs file:

ret = JSON.stringify({
    "draw": req.body.draw,
    "recordsTotal": result[0]['COUNT(cf)'],
    "recordsFiltered": result[0]['COUNT(cf)'],
    "dati": dati
});
res.send(ret);

and all works fine.

answered Oct 10, 2018 at 20:30

Mario Vitale

214 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Linking DataTables with sql database using Nodejs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related