$editor->db() with nested Select Query

Ky

From the documentation:

select( string|string[] $table, array $field = "*", array $where = null, array $orderBy = null )

And I have this:

...
->on( 'postCreate', function ( $editor, $id, $values, $row ) {
...
$users = $editor->db()->select( 'user', 'id', 'user = '.$row["user"].' OR (level >= 0 AND level <= 2);' );
...
} )
...

Of course, this is wrong. What I want to ask is what is the correct form to place the parameters with this query in. I can't figure this out >.<

Thanks

allan

Its the where condition that is tripping you up - the values given as a string there will be escaped in order to prevent SQL injection attacks. What you need to do is build the query more completely since you want to use a condition that isn't a simple equality operator:

$editor
  ->db()
  ->query( 'select' )
  ->get( 'id' )
  ->table( 'user' )
  ->where( 'user', $row['user'] )
  ->or_where( function ( $q ) {
    $q->where( 'level', 0, '>=' );
    $q->where( 'level', 2, '<=' );
  } )
  ->exec()
  ->fetchAll();

Its a bit more verbose since you need to call query, exec, fetchAll, etc yourself, but it does allow more complete control over the query.

Allan

Ky

Thanks.
Hm... for some strange reason now it's giving me this error (PHP log):

PHP Notice: Undefined offset: 0 in ../dataTables/Database/Query.php on line 561
PHP Notice: Undefined offset: 1 in ../dataTables/Database/Query.php on line 561
PHP Notice: Undefined offset: 2 in ../dataTables/Database/Query.php on line 561
PHP Notice: Undefined offset: 3 in ../dataTables/Database/Query.php on line 561
PHP Notice: Undefined offset: 4 in ../dataTables/Database/Query.php on line 561
PHP Notice: Undefined offset: 5 in ../dataTables/Database/Query.php on line 561
PHP Notice: Undefined offset: 6 in ../dataTables/Database/Query.php on line 561
PHP Notice: Undefined offset: 7 in ../dataTables/Database/Query.php on line 561
PHP Notice: Undefined offset: 8 in ../dataTables/Database/Query.php on line 561
PHP Notice: Undefined offset: 9 in ../dataTables/Database/Query.php on line 561
PHP Notice: Undefined offset: 10 in ../dataTables/Database/Query.php on line 561

I'm positive the query is correct this time. Any clues what that might be? Sorry

allan

What is the value of $row['user']?

Could you enable the debug mode (add ->debug( true ) immediately before the ->process(...) method) and then show me the JSON response from the server? It should include the SQL that is executed, which will give us a clue as to what is going wrong.

Thanks,
Allan

Ky

Here it is the JSON response from the server after debug( true ):

<br />
<b>Notice</b>: Undefined offset: 0 in <b>.../dataTables/Database/Query.php</b> on line <b>561</b><br />
<br />
<b>Notice</b>: Undefined offset: 1 in <b>.../dataTables/Database/Query.php</b> on line <b>561</b><br />
<br />
<b>Notice</b>: Undefined offset: 2 in <b>.../dataTables/Database/Query.php</b> on line <b>561</b><br />
<br />
<b>Notice</b>: Undefined offset: 3 in <b>.../dataTables/Database/Query.php</b> on line <b>561</b><br />
<br />
<b>Notice</b>: Undefined offset: 4 in <b>.../dataTables/Database/Query.php</b> on line <b>561</b><br />
<br />
<b>Notice</b>: Undefined offset: 5 in <b>.../dataTables/Database/Query.php</b> on line <b>561</b><br />
<br />
<b>Notice</b>: Undefined offset: 6 in <b>.../dataTables/Database/Query.php</b> on line <b>561</b><br />
<br />
<b>Notice</b>: Undefined offset: 7 in <b>.../dataTables/Database/Query.php</b> on line <b>561</b><br />
<br />
<b>Notice</b>: Undefined offset: 8 in <b>.../dataTables/Database/Query.php</b> on line <b>561</b><br />
<br />
<b>Notice</b>: Undefined offset: 9 in <b>.../dataTables/Database/Query.php</b> on line <b>561</b><br />
<br />
<b>Notice</b>: Undefined offset: 10 in <b>.../dataTables/Database/Query.php</b> on line <b>561</b><br />

and then this:

{"data":[{"DT_RowId":"row_28","ticket":{"id":"28","status":"0","date":"2017-07-11 4:06 PM","date2":"2017-07-11 4:07 PM","user":"1","branch":"0","department":"0","category":"0","request":"0","details":"","staff":"0","staff_note":"","solution":"","created_by":"1","last_updated_by":"0"},"user":{"login":"admin","level":"0","name":"Site Admin","email":"admin@example.com","email2":"someone@example.com","address":"","city":"","state":"","zip":"","country":"","phone":""},"branch":{"name":null},"department":{"name":null},"category":{"name":null},"request":{"name":null},"staff":{"name":null},"upload":[],"note":[]}],"debugSql":[{"query":"SELECT  `id` as 'id' FROM  `user` WHERE `id` = :where_0 ","bindings":[{"name":":where_0","value":"1","type":null}]},{"query":"INSERT INTO  `ticket`  ( `status`, `date`, `date2`, `user`, `request`, `details`, `staff`, `staff_note`, `solution`, `created_by` ) VALUES (  :status,  :date,  :date2,  :user,  :request,  :details,  :staff,  :staff_note,  :solution,  :created_by )","bindings":[{"name":":status","value":"0","type":null},{"name":":date","value":"2017-07-11 16:06:57","type":null},{"name":":date2","value":"2017-07-11 16:07:00","type":null},{"name":":user","value":"1","type":null},{"name":":request","value":"0","type":null},{"name":":details","value":"","type":null},{"name":":staff","value":"0","type":null},{"name":":staff_note","value":"","type":null},{"name":":solution","value":"","type":null},{"name":":created_by","value":"1","type":null}]},{"query":"SELECT  `ticket`.`id` as 'ticket.id', `ticket`.`status` as 'ticket.status', `ticket`.`date` as 'ticket.date', `ticket`.`date2` as 'ticket.date2', `ticket`.`user` as 'ticket.user', `user`.`login` as 'user.login', `user`.`level` as 'user.level', `user`.`name` as 'user.name', `user`.`email` as 'user.email', `user`.`email2` as 'user.email2', `user`.`address` as 'user.address', `user`.`city` as 'user.city', `user`.`state` as 'user.state', `user`.`zip` as 'user.zip', `user`.`country` as 'user.country', `user`.`phone` as 'user.phone', `ticket`.`branch` as 'ticket.branch', `branch`.`name` as 'branch.name', `ticket`.`department` as 'ticket.department', `department`.`name` as 'department.name', `ticket`.`category` as 'ticket.category', `category`.`name` as 'category.name', `ticket`.`request` as 'ticket.request', `request`.`name` as 'request.name', `ticket`.`details` as 'ticket.details', `ticket`.`staff` as 'ticket.staff', `staff`.`name` as 'staff.name', `ticket`.`staff_note` as 'ticket.staff_note', `ticket`.`solution` as 'ticket.solution', `ticket`.`created_by` as 'ticket.created_by', `ticket`.`last_updated_by` as 'ticket.last_updated_by' FROM  `ticket` LEFT JOIN `user` ON `user`.`id` = `ticket`.`user`  LEFT JOIN user as staff ON `staff`.`id` = `ticket`.`staff`  LEFT JOIN `branch` ON `branch`.`id` = `ticket`.`branch`  LEFT JOIN `department` ON `department`.`id` = `ticket`.`department`  LEFT JOIN `category` ON `category`.`id` = `ticket`.`category`  LEFT JOIN `request` ON `request`.`id` = `ticket`.`request` WHERE `ticket`.`id` = :where_0 ","bindings":[{"name":":where_0","value":"28","type":null}]},{"query":"SELECT DISTINCT  `ticket`.`id` as 'dteditor_pkey', `upload`.`id` as 'id' FROM  ticket as ticket  JOIN `tickets_uploads` ON `ticket`.`id` = `tickets_uploads`.`ticket_id`   JOIN `upload` ON `upload`.`id` = `tickets_uploads`.`upload_id` ","bindings":[]},{"query":"SELECT DISTINCT  `ticket`.`id` as 'dteditor_pkey', `note`.`id` as 'id', `note`.`title` as 'title', `note`.`body` as 'body', `note`.`date` as 'date', `note`.`created_by` as 'created_by', `note`.`last_updated_by` as 'last_updated_by' FROM  ticket as ticket  JOIN `tickets_notes` ON `ticket`.`id` = `tickets_notes`.`ticket_id`   JOIN `note` ON `note`.`id` = `tickets_notes`.`note_id` ","bindings":[]},{"query":"SELECT  `id` as 'id', `file_name` as 'file_name', `file_size` as 'file_size', `file_extn` as 'file_extn', `web_path` as 'web_path', `system_path` as 'system_path' FROM  `upload` ","bindings":[]},**{"query":"SELECT  `id` as 'id' FROM  `user` WHERE `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL OR(`level` >= :where_12 AND `level` <= :where_13 )**","bindings":[{"name":":where_12","value":0,"type":null},{"name":":where_13","value":2,"type":null}]}]}

Here is the code that is giving me troubles:

->on( 'postCreate', function ( $editor, $id, $values, $row ) {
        $users = $editor
          ->db()
          ->query( 'select' )
          ->get( 'id' )
          ->table( 'user' )
          ->where( 'id', $row['user'] )
          ->or_where( function ( $q ) {
            $q->where( 'level', 0, '>=' );
            $q->where( 'level', 2, '<=' );
          } )
          ->exec()
          ->fetchAll();
    } )

If I change the $row['user'] for another integer, it works fine but I need to get the created row user id >.< Sorry for the mess haha!

allan

Formatted JSON:

{
    "data": [{
        "DT_RowId": "row_28",
        "ticket": {
            "id": "28",
            "status": "0",
            "date": "2017-07-11 4:06 PM",
            "date2": "2017-07-11 4:07 PM",
            "user": "1",
            "branch": "0",
            "department": "0",
            "category": "0",
            "request": "0",
            "details": "",
            "staff": "0",
            "staff_note": "",
            "solution": "",
            "created_by": "1",
            "last_updated_by": "0"
        },
        "user": {
            "login": "admin",
            "level": "0",
            "name": "Site Admin",
            "email": "admin@example.com",
            "email2": "someone@example.com",
            "address": "",
            "city": "",
            "state": "",
            "zip": "",
            "country": "",
            "phone": ""
        },
        "branch": {
            "name": null
        },
        "department": {
            "name": null
        },
        "category": {
            "name": null
        },
        "request": {
            "name": null
        },
        "staff": {
            "name": null
        },
        "upload": [],
        "note": []
    }],
    "debugSql": [{
        "query": "SELECT  `id` as 'id' FROM  `user` WHERE `id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "1",
            "type": null
        }]
    }, {
        "query": "INSERT INTO  `ticket`  ( `status`, `date`, `date2`, `user`, `request`, `details`, `staff`, `staff_note`, `solution`, `created_by` ) VALUES (  :status,  :date,  :date2,  :user,  :request,  :details,  :staff,  :staff_note,  :solution,  :created_by )",
        "bindings": [{
            "name": ":status",
            "value": "0",
            "type": null
        }, {
            "name": ":date",
            "value": "2017-07-11 16:06:57",
            "type": null
        }, {
            "name": ":date2",
            "value": "2017-07-11 16:07:00",
            "type": null
        }, {
            "name": ":user",
            "value": "1",
            "type": null
        }, {
            "name": ":request",
            "value": "0",
            "type": null
        }, {
            "name": ":details",
            "value": "",
            "type": null
        }, {
            "name": ":staff",
            "value": "0",
            "type": null
        }, {
            "name": ":staff_note",
            "value": "",
            "type": null
        }, {
            "name": ":solution",
            "value": "",
            "type": null
        }, {
            "name": ":created_by",
            "value": "1",
            "type": null
        }]
    }, {
        "query": "SELECT  `ticket`.`id` as 'ticket.id', `ticket`.`status` as 'ticket.status', `ticket`.`date` as 'ticket.date', `ticket`.`date2` as 'ticket.date2', `ticket`.`user` as 'ticket.user', `user`.`login` as 'user.login', `user`.`level` as 'user.level', `user`.`name` as 'user.name', `user`.`email` as 'user.email', `user`.`email2` as 'user.email2', `user`.`address` as 'user.address', `user`.`city` as 'user.city', `user`.`state` as 'user.state', `user`.`zip` as 'user.zip', `user`.`country` as 'user.country', `user`.`phone` as 'user.phone', `ticket`.`branch` as 'ticket.branch', `branch`.`name` as 'branch.name', `ticket`.`department` as 'ticket.department', `department`.`name` as 'department.name', `ticket`.`category` as 'ticket.category', `category`.`name` as 'category.name', `ticket`.`request` as 'ticket.request', `request`.`name` as 'request.name', `ticket`.`details` as 'ticket.details', `ticket`.`staff` as 'ticket.staff', `staff`.`name` as 'staff.name', `ticket`.`staff_note` as 'ticket.staff_note', `ticket`.`solution` as 'ticket.solution', `ticket`.`created_by` as 'ticket.created_by', `ticket`.`last_updated_by` as 'ticket.last_updated_by' FROM  `ticket` LEFT JOIN `user` ON `user`.`id` = `ticket`.`user`  LEFT JOIN user as staff ON `staff`.`id` = `ticket`.`staff`  LEFT JOIN `branch` ON `branch`.`id` = `ticket`.`branch`  LEFT JOIN `department` ON `department`.`id` = `ticket`.`department`  LEFT JOIN `category` ON `category`.`id` = `ticket`.`category`  LEFT JOIN `request` ON `request`.`id` = `ticket`.`request` WHERE `ticket`.`id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "28",
            "type": null
        }]
    }, {
        "query": "SELECT DISTINCT  `ticket`.`id` as 'dteditor_pkey', `upload`.`id` as 'id' FROM  ticket as ticket  JOIN `tickets_uploads` ON `ticket`.`id` = `tickets_uploads`.`ticket_id`   JOIN `upload` ON `upload`.`id` = `tickets_uploads`.`upload_id` ",
        "bindings": []
    }, {
        "query": "SELECT DISTINCT  `ticket`.`id` as 'dteditor_pkey', `note`.`id` as 'id', `note`.`title` as 'title', `note`.`body` as 'body', `note`.`date` as 'date', `note`.`created_by` as 'created_by', `note`.`last_updated_by` as 'last_updated_by' FROM  ticket as ticket  JOIN `tickets_notes` ON `ticket`.`id` = `tickets_notes`.`ticket_id`   JOIN `note` ON `note`.`id` = `tickets_notes`.`note_id` ",
        "bindings": []
    }, {
        "query": "SELECT  `id` as 'id', `file_name` as 'file_name', `file_size` as 'file_size', `file_extn` as 'file_extn', `web_path` as 'web_path', `system_path` as 'system_path' FROM  `upload` ",
        "bindings": []
    }, ** {
        "query": "SELECT  `id` as 'id' FROM  `user` WHERE `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL AND `id` IS NULL OR(`level` >= :where_12 AND `level` <= :where_13 )**",
        "bindings": [{
            "name": ":where_12",
            "value": 0,
            "type": null
        }, {
            "name": ":where_13",
            "value": 2,
            "type": null
        }]
    }]
}

There is something very odd about the ** - that makes it invalid JSON...

allan

There is something odd happening with the $row['user'] parameter.

Could you show me what the result of: var_dump( $row['user'] ); is? It will make the JSON return invalid, but it will show what it is. I don't think its an integer, but rather its an object, and that is what is causing the issue.

Allan

Ky

There is something very odd about the ** - that makes it invalid JSON...

Don't worry about it. It was probably me messing up the copy/paste/"formatting" of the JSON into the forum

And thank you very much! $row['user'] is indeed an object (well... more like an array). So replacing it with $row['user']['id'] solved the issue!

Thanks again! I learned a few tools for debugging for next time ! Sorry for the bothers xD

allan

Perfect - great to hear you've got it working now.

Allan