where statement with a page variable

koniahin

I have an editor table which is associated with an id that gets passed to the page from another page like

edit-page?id=1

edit page loads the datatables editor and I want it to list only items associated with the id. On the dt page I have added a simple $REQUEST to obtain the id.

$id=$_REQUEST['id'];

On the page I checked that the id is good by doing a simple echo statement. In the php script/controller file I added a where clause like:

->where( 'parent_id',"$id",'=' )
also tried
->where( 'parent_id','$id','=' )

They return no results; when I opt to add a new item it adds successfully and the id is good. I checked in mysql. If I change the where and hardcode the id it works, but it needs to use the variable:

->where( 'parent_id','11','=' )

The question is how do I get the where statement to read a page variable?

allan

Just ->where( 'parent_id', $id ) should do it. If that doesn't help, can you show me the full PHP file please?

Allan

koniahin

Seems simple enough, but no dice. Also tried:

->where( 'parent_id','$id' ) // quoted

The php file

Editor::inst( $db, 'menu_items_children' )
  ->fields(
    Field::inst( 'menu_items_children.title' )->validator( 'Validate::notEmpty' ),
    Field::inst( 'menu_items_children.parent_id' ),
    Field::inst( 'menu_items_children.publish' ),
    Field::inst( 'menu_items_children.access' ),
    Field::inst( 'menu_items_children.split' ),
    Field::inst( 'menu_items_children.menu_id' ),
    Field::inst( 'menu_items_children.type' ),
    Field::inst( 'menu_items_children.url' ),
    Field::inst( 'menu_items_children.id' ),
    Field::inst( 'rowOrder' )->validator( 'Validate::numeric' )
  )

  // ->where( 'id',"$parent_id",'=' )
  // ->where( 'menu_items_children.parent_id',$id )
 //  ->where( 'parent_id','$id' )
  ->where( 'parent_id',$id )

  ->on( 'preCreate', function ( $editor, $values ) {
    $editor->db()
      ->query( 'update', 'menu_items_children' )
      ->set( 'rowOrder', 'rowOrder+1', false )
      ->where( 'rowOrder', $values['rowOrder'], '>=' )
      ->exec();
  } )

I am getting the $_REQUEST from the page:

$website/bin/menu-items-children.php?id=11

It is good if I print an echo.

From the db:

MariaDB > select id, title, parent_id, menu_id from menu_items_children;
+----+----------+-----------+---------+
| id | title    | parent_id | menu_id |
+----+----------+-----------+---------+
|  1 | asdfasdf |        11 |       1 |
|  2 | adfasdf  |        11 |       1 |
|  3 | asdf     |        11 |       1 |
+----+----------+-----------+---------+
3 rows in set (0.00 sec)

It works only if I hardcode the parent ID in the query.

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

->where( 'parent_id','$id' ) // quoted

That one wont work since PHP won't expand the variable inside single quoted strings.

I'm not sure I can explain why that isn't working! Could you try:

->where( 'parent_id',$_GET['id'] )

please?

Also add:

error_reporting(E_ALL);
ini_set('display_errors', '1');

at the top of the file to make sure that all errors and warnings are shown.

Thanks,
Allan

koniahin

When I add the _GET or even _REQUEST the page errors:

->where( 'parent_id', $_GET['id'] )

// Errors with: DataTables warning: table id=example - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

Adding error reporting at the top of the controller file returns the same invalid JSON error. Moving this to the page file returns some undefined variable errors but nothing related to the $id.

allan

What is the response from the server that is not valid JSON? Likely (hopefully - particularly with my error reporting suggestion above) it will show an error message.

Allan

koniahin

If I add the error reporting lines to the head of the controller file:

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');

It errors: Errors with: DataTables warning: table id=example - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

If added to the top of the datatable creation file I get no errors.

->where( 'id',11,'=' ) // works

->where( 'id',$id ) // fail
->where( 'id',"$id" ) // fail
->where( 'id',$id,'=' ) // fail

If in the controller file I add

$id = 11; // just to see
->where( 'id',$id,'=')

I get the same JSON error noted above.

kthorngren

Follow the instructions in the link provided in the error:
http://datatables.net/tn/1

Post the JSON response you see in the developer tools.

Kevin

koniahin

{"data":[],"options":[],"files":[]}

Before getting this far I had checked in developer and saw the above. On a page returning results, like where I hardcode the ID it's quite a different story. If I hardcode the ID as 11,

{"data":[{"DT_RowId":"row_1","menu_items_children":{"title":"asdfasdf","parent_id":"11","publish":"0","access":"Public","split":"Left","menu_id":"1","type":"","url":"89","id":"1"},"rowOrder":"3"},{"DT_RowId":"row_2","menu_items_children":{"title":"adfasdf","parent_id":"11","publish":"0","access":"Public","split":"Left","menu_id":"1","type":"","url":"88","id":"2"},"rowOrder":"2"},

allan

There must be something else as well. What you show above it valid JSON. Likely there is an error message being returned by the server which will explain what the problem is.

Allan

koniahin

->where( 'id',11,'=' ) // hardcoded works

->where( 'id',$id ) // $variable regardless of syntax, quotes, = sign - fails

I don't see anything in the developer tabs more informational. If I add a line to echo back the value of the variable to the screen it works.

echo "id: $id<br />";

What else to test? The page is .htaccess protected to keep out prying eyes.

koniahin

The datatables 'where' statement does not seem to allow for the variable to convert to its value. Does not make sense to me. Is there another variable of the where to try?

koniahin

Oops - is there another variation of the where to test?

allan

With your ->where( 'id',$id ) line ine, can you use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

The datatables 'where' statement does not seem to allow for the variable to convert to its value. Does not make sense to me. Is there another variable of the where to try?

It should make no difference if its in a variable or in a static value.

As well as the debug information requested above, could you post your full PHP file please?

Allan

koniahin

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');

include ("../../config.php");

include( "../assets/datatables-editor/php/DataTables.php" );

use
  DataTables\Editor,
  DataTables\Editor\Field,
  DataTables\Editor\Format,
  DataTables\Editor\Mjoin,
  DataTables\Editor\Options,
  DataTables\Editor\Upload,
  DataTables\Editor\Validate;

Editor::inst( $db, 'menu_items_children' )
  ->fields(
    Field::inst( 'menu_items_children.title' )->validator( 'Validate::notEmpty' ),
    Field::inst( 'menu_items_children.parent_id' ),
    Field::inst( 'menu_items_children.publish' ),
    Field::inst( 'menu_items_children.access' ),
    Field::inst( 'menu_items_children.split' ),
    Field::inst( 'menu_items_children.menu_id' ),
    Field::inst( 'menu_items_children.type' ),
    Field::inst( 'menu_items_children.url' ),
    Field::inst( 'menu_items_children.id' ),
    Field::inst( 'rowOrder' )->validator( 'Validate::numeric' )
  )

  ->where( 'parent_id','11','=' ) // Good
  ->where( 'parent_id','11' ) // Good

  /* All fail, returning no results

  ->where( 'parent_id',$parent_id ); // Fails with Generic error popup
  ->where( 'menu_items_children.parent_id',$parent_id ) // Fails with Generic error popup
  ->where( 'parent_id','$parent_id','=' ) // Fails no results or error

  ->where( 'parent_id',$_REQUEST['parent_id'] ) // Fails with Generic error popup
  ->where( 'parent_id',$_GET['parent_id'] ) // Fails with Generic error popup

  */

  ->on( 'preCreate', function ( $editor, $values ) {
    $editor->db()
      ->query( 'update', 'menu_items_children' )
      ->set( 'rowOrder', 'rowOrder+1', false )
      ->where( 'rowOrder', $values['rowOrder'], '>=' )
      ->exec();
  } )

  ->on( 'preRemove', function ( $editor, $id, $values ) {
    $order = $editor->db()
      ->select( 'menu_items_children', 'rowOrder', array('id' => $id) )
      ->fetch();
    $editor->db()
      ->query( 'update', 'menu_items_children' )
      ->set( 'rowOrder', 'rowOrder-1', false )
      ->where( 'rowOrder', $order['rowOrder'], '>' )
      ->exec();
  } )
  ->process( $_POST )
  ->json();

allan

I don't see the $parent_id variable used in your commented out WHERE condition being defined anywhere. Perhaps you could add:

print_r($_POST);
print_r($_GET);

To your code please? You'll get a JSON error from DataTables, but that's fine, just use the browser's network inspector to view the response from the server and post it in here please.

Allan

koniahin

I have moved this stuff around so many times that I have lost track at times. However ... If you look at the commented section, All fail, you'll see where I earlier tested the parent id from $_REQUEST and GET.

I have also placed it in the parent file that includes the controller php file:

$parent_id = $_REQUEST['parent_id'];

It makes no difference; it refuses to substitute the value for the variable. I have another application/script where I need to do the same thing with a variable; it also fails. Yes adding the two lines results in the error: DataTables warning: table id=example - Ajax error. For more information about this error, please see http://datatables.net/tn/7

But also I see:

Array
(
[parent_id] => 11
)

That seems right. Anyway, the amount of time it is taking to get this simple thing to work is substantial. In 15 minutes I created a hacky, cheesey workaround. I copied the controller file to a template, .tpl and added some substitute text in place of the variable then in the parent file added the following lines:

/* replace replace-with-id with $parent_id in menu-item-children.php-tpl */

$data = file_get_contents("controllers/menu-items-children.php-tpl");
$data = str_replace ("replace-with-id","$parent_id",$data);
file_put_contents( "controllers/menu-items-children.php","$data");

This works for both scripts, pending a true solution.

allan

I believe the following should work. If it doesn't can you show me the data returned from the server please?

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
 
include ("../../config.php");
include( "../assets/datatables-editor/php/DataTables.php" );
 
use
  DataTables\Editor,
  DataTables\Editor\Field,
  DataTables\Editor\Format,
  DataTables\Editor\Mjoin,
  DataTables\Editor\Options,
  DataTables\Editor\Upload,
  DataTables\Editor\Validate;
 
Editor::inst( $db, 'menu_items_children' )
  ->fields(
    Field::inst( 'menu_items_children.title' )->validator( 'Validate::notEmpty' ),
    Field::inst( 'menu_items_children.parent_id' ),
    Field::inst( 'menu_items_children.publish' ),
    Field::inst( 'menu_items_children.access' ),
    Field::inst( 'menu_items_children.split' ),
    Field::inst( 'menu_items_children.menu_id' ),
    Field::inst( 'menu_items_children.type' ),
    Field::inst( 'menu_items_children.url' ),
    Field::inst( 'menu_items_children.id' ),
    Field::inst( 'rowOrder' )->validator( 'Validate::numeric' )
  )
  ->where( 'parent_id',$_REQUEST['parent_id'] ) 
  ->on( 'preCreate', function ( $editor, $values ) {
    $editor->db()
      ->query( 'update', 'menu_items_children' )
      ->set( 'rowOrder', 'rowOrder+1', false )
      ->where( 'rowOrder', $values['rowOrder'], '>=' )
      ->exec();
  } )
 
  ->on( 'preRemove', function ( $editor, $id, $values ) {
    $order = $editor->db()
      ->select( 'menu_items_children', 'rowOrder', array('id' => $id) )
      ->fetch();
    $editor->db()
      ->query( 'update', 'menu_items_children' )
      ->set( 'rowOrder', 'rowOrder-1', false )
      ->where( 'rowOrder', $order['rowOrder'], '>' )
      ->exec();
  } )
  ->process( $_POST )
  ->json();

Allan

koniahin

Thanks but no, it ignores the where clause or rather the _REQUEST['parent_id'] and returns all results. I'm not excited about the hack but it works and I'm not too concerned since I have a solution.

koniahin

Correct that. It returns a popup error and no results.

allan

I genuinely don't understand why that wouldn't work I'm afraid. But good to hear you've got a workaround!

Allan

silverstngll

$editor->where( function ( $q ) use ( $userId ) {
$q->where( 'id', $userId );
} );

https://editor.datatables.net/manual/php/conditions