尝试从pdo查询构建服务器端数据表

Question

I have to build a data table from server side because I have more than 200k rows, and I don't have the choice I need to make it from an execute statement like:

$result['draw']=1;
$temp = BD::query('SELECT * FROM `ersmessages` WHERE 1 LIMIT 10');
$result['recordsTotal'] = sizeof($temp);
$result['recordsFiltered'] = sizeof($temp);
$result['data'] = $temp;

But when I'm doing this that does not page my results and for 200k rows it's giving an error. If I limit the query that works but on one page.

My JS looks like:

 $('#tableAllMessages').DataTable( {
        "processing": true,
        "serverSide": true,
        "paging": true,
        "searching": { "regex": true },
        "lengthMenu": [ [10, 25, 50, 100, -1], [10, 25, 50, 100, "All"] ],
        "pageLength": 10,
        "ajax": "req/getAllMessages.php"
    } );

How should I proceed to have a paged data table with 200k rows? I can't obviously use the ssp.

Answer 1

ok the problem has been solved, i needed to put the recordsTotal to my maximum(200k rows) size and i needed to add an offset to the query, that way i am able to paginate my table.

$offset = $_REQUEST['start'];
$result['recordsTotal'] = 200000;
$query = BD::query('SELECT * FROM `ersmessages` WHERE 1 ORDER BY `idERSMessage` ASC LIMIT 10 OFFSET '.intval($offset))
$result['data'] = $query;
$result['draw']=$_REQUEST['draw'];