SQL WHERE Clause

wuestl

Hi i am working on an intranet side an i have a big problem to realize the where clause in my data table.
I have read all i have found, but it still not work.

my php file looks like

$pid = $_GET['pid'];

// DataTables PHP library
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'taetigkeiten' )
->fields(
Field::inst( 'art' ),
Field::inst( 'datum' ),
Field::inst( 'stunden' ),
Field::inst( 'minuten' ),
Field::inst( 'beschreibung' ),
Field::inst( 'mandant' ),
Field::inst( 'projekt' ),
Field::inst( 'erstelltvon' )
)
->where( $key = 'projekt', $value = $pid, $op = '=' )
->process( $_POST )
->json();

if i set ->where( $key = 'projekt', $value = $pid, $op = '=' ) to ->where( $key = 'projekt', $value = 1, $op = '=' )
it shows all entrys in the database with 1. But with a variable it did not work :(


and my js file

$(document).ready(function() {
var editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "assets/plugins/data-tables/table-editor/php/table.taetigkeiten.php",
"domTable": "#sample_5",
"fields": [
{
"label": "Art",
"name": "art",
"type": "select",
"ipOpts": [
{ "label": "Zeit", "value": "Zeit" },
{ "label": "Auslagen", "value": "Auslagen" }
]
},
{
"label": "Beschreibung",
"name": "beschreibung",
"type": "text"
},
{
"label": "Stunden",
"name": "stunden",
"type": "text"
},
{
"label": "Minuten",
"name": "minuten",
"type": "text"
},
{
"label": "Datum",
"name": "datum",
"type": "date",
"dateFormat": 'dd. M yy'
},
]
} );

editor.on( 'onPreSubmit', function ( e, data ) {
data.pid = "<?php echo $pid; ?>";
} );

// New record
$('a.editor_create').on('click', function (e) {
e.preventDefault();

editor.create(
'Neue Tätigkeit',
{ "label": "Speichern", "fn": function () { editor.submit() } }
);
} );

// Edit record
$('#sample_5').on('click', 'a.editor_edit', function (e) {
e.preventDefault();

editor.edit(
$(this).parents('tr')[0],
'Tätigkeit ändern',
{ "label": "Ändern", "fn": function () { editor.submit() } }
);
} );

// Delete a record (without asking a user for confirmation)
$('#sample_5').on('click', 'a.editor_remove', function (e) {
e.preventDefault();

editor.remove( $(this).parents('tr')[0], '123', false, false );
editor.submit();
} );

// DataTables init
$('#sample_5').dataTable( {
"sDom": "Tfrtip",
"sAjaxSource": "assets/plugins/data-tables/table-editor/php/table.taetigkeiten.php",
"fnServerParams": function ( aoData ) {
aoData.push( { "name": "pid", "value": "<?php echo $pid; ?>" } );
},
"aoColumns": [
{
"mData": "art"
},
{
"mData": "beschreibung"
},
{
"mData": "stunden"
},
{
"mData": "datum"
},

{
"mData": null,
"sClass": "center",
"sDefaultContent": ' '
}
],
"aLengthMenu": [
[15, 20, -1],
[15, 20, "Alle"] // change per page values here
],
// set the initial value
"iDisplayLength": 15,
"sDom": "<'row-fluid'<'span6'l><'span6'f>r>t<'row-fluid'<'span6'i><'span6'p>>",
"sPaginationType": "bootstrap",
"oLanguage": {
"sLengthMenu": "_MENU_ pro Seite",
"oPaginate": {
"sPrevious": "zurück",
"sNext": "vor"
}
},
"aoColumnDefs": [{
'bSortable': false,
'aTargets': [0]
}
]

} );
} );

Here the same.
If i set aoData.push( { "name": "pid", "value": "<?php echo $pid; ?>" } ); to aoData.push( { "name": "pid", "value": "1" } );
it shows all entrys in the database with 1. But with a variable it did not work :(

I have testet it also with sessions. It also did not work.

Please can help me anybody, i despair ...

allan

Hi,

So it sounds like the issue is with `<?php echo $pid; ?>` . What is the value of `$pid` at that point and where does it come from? It sounds like the `where` is working okay, it just needs to get the correct value into it!

Regards,
Allan

wuestl

Hi allan,

thanks for your answer.

The value $pid comes with the domain look like http://xxx.xxx.xxx.xxx/index.php?page=projekt_bearbeiten&pid=2

On this site the editor is embaded, like this





Art
Beschreibung
Zeit
Datum






I dont know where the error is. I have also testet it with a session.

Best regards
Oliver

allan

Thanks for the information. So should:

> aoData.push( { "name": "pid", "value": "<?php echo $pid; ?>" } );

actually be:

[code]
aoData.push( { "name": "pid", "value": "<?php echo $_GET['pid']; ?>" } );
[/code]

?

I'd also suggest using `intval()` to make sure someone doesn't try to attack the site with an SQL injection attack!

Allan

wuestl

Hi Allan,

thanks for your answer. It still not work.
I dont know where the problem is :(

Best regards
Oliver

allan

Can you link me to the page so I can take a look? It sounds like the parameter just isn't going into the data to be send to the server.

wuestl

i have uploaded it now to an online server.

Where can i tell you the username and password?

Thanks
Oliver

wuestl

Hi allan,

please can you help me?

wuestl

So, my first Problem is fixed.

Now i have a new problem. When i will made a new entry or i update a entry, i get a error message "An error has occurred - Please contact the system administrator". The new or updated entry will shown i the database but not the variable version it still be "0"

Here is the code from my index file

var editor; // use a global for the submit and return data rendering in the examples

$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "php/browsers.php",
"domTable": "#example",
"fields": [ {
"label": "Browser:",
"name": "browser"
}, {
"label": "Rendering engine:",
"name": "engine"
}, {
"label": "Platform:",
"name": "platform"
}, {
"label": "Version:",
"name": "version"
}, {
"label": "CSS grade:",
"name": "grade"
}
]
} );

editor.on( 'onPreSubmit', function ( e, data ) {
data.version = "1";
} );

$('#example').dataTable( {
"sDom": "<'row'<'col-xs-6'T><'col-xs-6'f>r>t<'row'<'col-xs-6'i><'col-xs-6'p>>",
"sAjaxSource": "php/browsers.php",
"fnServerParams": function ( aoData ) {
aoData.push( { "name": "version", "value": "1" } );
},
"aoColumns": [
{ "mData": "browser" },
{ "mData": "engine" },
{ "mData": "platform" },
{ "mData": "version", "sClass": "center" },
{ "mData": "grade", "sClass": "center" }
],
"oTableTools": {
"sRowSelect": "multi",
"aButtons": [
{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit", "editor": editor },
{ "sExtends": "editor_remove", "editor": editor }
]
}
} );
} );

and here the browser.php

use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'browsers' )
->fields(
Field::inst( 'engine' )->validator( 'Validate::required' ),
Field::inst( 'browser' )->validator( 'Validate::required' ),
Field::inst( 'platform' ),
Field::inst( 'version' )->validator( 'Validate::required' ),
Field::inst( 'grade' )->validator( 'Validate::required' )
)
->where( $key = 'version', $value = $_GET['version'], $op = '=' )
->process( $_POST )
->json();


Please, please can you help me here. I dont know whats wrong!!!!

wuestl

Thanks very much for the help ;)

allan

My apologies for the delay in getting back to you!

The general "Please contact..." error that you are seeing at the moment occurs when the server returned invalid JSON for an Editor request (create, edit, remove). The return from the server will usually contain an error message stating what is going wrong, which you will be able to see with your browser's developer tools.

I suspect that this is occurring because of the `where()` method being used - it is always applying the where method, even for an Editor request, even although `version` isn't being sent for the Editor requests.

So what I would suggest (and assuming that I am correct in my guess! :-) ), is this:

[code]
$editor = Editor::inst( $db, 'browsers' )
->fields(
Field::inst( 'engine' )->validator( 'Validate::required' ),
Field::inst( 'browser' )->validator( 'Validate::required' ),
Field::inst( 'platform' ),
Field::inst( 'version' )->validator( 'Validate::required' ),
Field::inst( 'grade' )->validator( 'Validate::required' )
);

if ( ! isset( $_POST['action'] ) ) {
$editor->where( $key = 'version', $value = $_GET['version'], $op = '=' );
}

$editor
->process( $_POST )
->json();
[/code]

i.e. only add the `where()` condition when DataTables is requesting the table's data.

Does that work for you? If not, it would be great if you could drop me an e-mail with the login details that you mentioned - allan @ this domain.net :-)

Regards,
Allan

wuestl

Hi Allan,

thanks for your response.
Now it works.
Is there any way to write a fix value into the table for version?

I mean, the user enter engine, browser, platform and grade. And the system enter the value "1" for version to write into the database?

Sorry my english is not very well ;)

Regards
Oliver

allan

Hi Oliver,

The `hidden` field type is what you want here I think: https://editor.datatables.net/fields/#hidden . Set a default value and the user will not be able to change that value.

Regards,
Allan

wuestl

Hi Allan,

thanks so much. Now its work very fine!!!

Best Regards,
Oliver