Serverside processing with php and mysql
Serverside processing with php and mysql
Karlog
Posts: 9Questions: 4Answers: 0
Hello
I have for a time tried to convert my project to use server-side processing instead of client-side, but unfortunately without any luck.
The project is almost done, but when I tested with more data, I hit the wall of what the client can handle and therefore I need to convert to server-side.
Do you have any good examples of what changes I need to make to the default PHP files to make server-side processing work?
I am using the newest version of the editor, and have used the generator to as much as possible. (which was very helpful.)
My table in javascript
*
* Editor client script for DB table hours
* Created by http://editor.datatables.net/generator
*/
(function($){
$(document).ready(function() {
var timestamp = (new Date()).toISOString().substring(0, 19).replace('T', ' ');
var editor = new $.fn.dataTable.Editor( {
ajax: '/php/table.hours.php',
table: '#hours',
fields: [
{
"label": "Indsæt kun i normal arbejdstid",
"name": "OnlyInNormalWorkHours",
"type": "checkbox",
"separator": ",",
"options": [
{label: "", value: 1}
],
unselectedValue: 0
},
{
"label": "Bruger:",
"name": "hours.user",
"type": "select",
placeholder: "Vælg bruger"
},
{
"label": "Arbejdstype:",
"name": "hours.work_type",
"type": "select",
placeholder: "Vælg arbejds type"
},
{
"label": "Projekt:",
"name": "hours.project",
"type": "select",
placeholder: "Vælg projekt"
},
{
"label": "Start:",
"name": "hours.start",
"type": "datetime",
"format": "YYYY-MM-DD HH:mm:ss"
},
{
"label": "Slut:",
"name": "hours.stop",
"type": "datetime",
"format": "YYYY-MM-DD HH:mm:ss"
},
{
"label": "Ugyldig:",
"name": "hours.invalid",
"type": "checkbox",
"separator": ",",
"options": [
{label: "", value: 1}
],
unselectedValue: 0
},
{
"label": "Pris pr. time",
"name": "hours.price_per_hour"
}
],
i18n: {
create: {
button: "Ny",
title: "Opret ny",
submit: "Opret"
},
edit: {
button: "Redigér",
title: "Redigér linje",
submit: "Gem ændringer"
},
remove: {
button: "Slet valgte",
title: "Slet valgte",
submit: "Slet",
confirm: {
_: "Er du sikker på at du vil slette %d linjer?",
1: "Er du sikker på at du vil slette 1 linje?"
}
},
error: {
system: "Hov, noget gik galt! Kontakt venligst system administratoren.."
},
multi: {
title: "Flere værdier valgt",
info: "Klik her for at redigére data for de valgte linjer.",
restore: "Annuller"
},
datetime: {
previous: 'Forrige',
next: 'Næste',
months: [ 'Januar', 'Februar', 'Marts', 'April', 'Maj', 'Juni', 'Juli', 'August', 'September', 'October', 'November', 'December' ],
weekdays: [ 'Man', 'Tirs', 'Ons', 'Tors', 'Fre', 'Lør', 'Søn' ]
}
}
} );
var table = $('#hours').DataTable( {
dom: 'Blfrtip',
lengthMenu: [
[25, 50, -1],
[25, 50, "Alle"]
],
lengthChange: true,
scrollY: 200,
deferRender: true,
scroller: true,
ajax: '/php/table.hours.php',
columns: [
{
"data": "users.initials"
},
{
"data": "work_type.name"
},
{
"data": "projects.name"
},
{
"data": "hours.start"
},
{
"data": "hours.stop"
},
{
"data": "hours.invalid"
},
{
"data": "hours.timestamp"
},
{
"data": "hours.price_per_hour"
}
],
select: true,
buttons: [
{ extend: 'create', text: 'Ny', editor: editor },
{ extend: 'edit', text: 'Redigér', editor: editor },
{ extend: 'remove', text: 'Slet', editor: editor },
{
extend: 'collection',
text: 'Export',
buttons: [
'copy',
'excel',
'csv',
'pdf',
'print'
]
}
],
language: {
url: '/Language/Danish.json',
"processing": "<span class='glyphicon glyphicon-refresh glyphicon-refresh-animate'></span>"
},
"order": [[ 6, 'desc']]
} );
My PHP file:
<?php
/*
* Editor server script for DB table hours
* Created by http://editor.datatables.net/generator
*/
// DataTables PHP library and database connection
include( "lib/DataTables.php" );
require_once("../baseconf.php");
....
....Some custom code .......
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'hours', 'id' )
->fields(
Field::inst( 'hours.user' )
->validator( 'Validate::notEmpty' )
->validator( 'Validate::required' )
->options( Options::inst()
->table( 'users' )
->value( 'id' )
->label( 'initials' )
),
Field::inst( 'users.initials' ),
Field::inst( 'hours.work_type' )
->validator( 'Validate::notEmpty' )
->validator( 'Validate::required' )
->options( Options::inst()
->table( 'work_type' )
->value( 'id' )
->label( 'name' )
),
Field::inst( 'work_type.name' ),
Field::inst( 'hours.project' )
->validator( 'Validate::notEmpty' )
->validator( 'Validate::required' )
->options( Options::inst()
->table( 'projects' )
->value( 'id' )
->label( 'name' )
),
Field::inst( 'projects.name' ),
Field::inst( 'hours.start' )
->validator( 'Validate::notEmpty' )
->validator( 'Validate::dateFormat', array( 'format'=>'Y-m-d H:i:s' ) )
->getFormatter( 'Format::datetime', array( 'from'=>'Y-m-d H:i:s', 'to' =>'Y-m-d H:i:s' ) )
->setFormatter( 'Format::datetime', array( 'to' =>'Y-m-d H:i:s', 'from'=>'Y-m-d H:i:s' ) ),
Field::inst( 'hours.stop' )
->validator( 'Validate::notEmpty' )
->validator( 'Validate::required' )
->validator( 'Validate::dateFormat', array( 'format'=>'Y-m-d H:i:s' ) )
->getFormatter( 'Format::datetime', array( 'from'=>'Y-m-d H:i:s', 'to' =>'Y-m-d H:i:s' ) )
->setFormatter( 'Format::datetime', array( 'to' =>'Y-m-d H:i:s', 'from'=>'Y-m-d H:i:s' ) ),
Field::inst( 'hours.invalid' ),
Field::inst( 'hours.timestamp' )
->validator( 'Validate::dateFormat', array( 'format'=>'Y-m-d H:i:s' ) )
->getFormatter( 'Format::datetime', array( 'from'=>'Y-m-d H:i:s', 'to' =>'Y-m-d H:i:s' ) )
->setFormatter( 'Format::datetime', array( 'to' =>'Y-m-d H:i:s', 'from'=>'Y-m-d H:i:s' ) ),
Field::inst( 'hours.price_per_hour' )
->validator( 'Validate::notEmpty' )
->validator( 'Validate::required' )
->validator( 'Validate::numeric' )
)
->leftJoin( 'users', 'hours.user' , '=', 'users.id')
->leftJoin( 'projects', 'hours.project' , '=', 'projects.id')
->leftJoin( 'work_type', 'hours.work_type' , '=', 'work_type.id')
->on( 'preCreate', function ( $editor, $values) {
if($values['OnlyInNormalWorkHours'] == 1) {
checkIfNormalWorkHoursOnly( $editor->db(), 'create', $values);
return false;
} else {
updateFlex($editor->db(), "create", $values, $id);
}
} )
->on( 'preEdit', function ( $editor, $id, $values) {
logChange( $editor->db(), 'edit', $values, $id);
if($values['OnlyInNormalWorkHours'] == "1") {
checkIfNormalWorkHoursOnly( $editor->db(), 'edit', $values, $id);
return false;
} else {
updateFlex($editor->db(), "edit", $values, $id);
}
} )
->on( 'postRemove', function ( $editor, $id, $values ) {
updateFlex($editor->db(), "delete", $values, $id);
logChange( $editor->db(), 'delete', $values, $id );
} )
->process( $_POST )
->json();
If you need to see the page then please PM me, as I use the table behind a login. :-)
This question has an accepted answers - jump to answer
This discussion has been closed.
Answers
Hi,
Change:
to be:
That should be all you need as you are using the Editor PHP libraries. What that will do is send the DataTables request as a POST (so
->process( $_POST )will see the parameters), and enable server-side processing with theserverSideoption.Let me know how you get on with it!
Regards,
Allan
Hi Allan
Thanks! You are a life saver!
First I changed the ajax at the editor instead of the table and that did not work. So for everyone else, it is the ajax at the table you need to change.
It also looks to reduce load-time a fair bit!