$editor->db() with nested Select Query

Ky · July 2017

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 · July 2017

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 · July 2017

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 · July 2017

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 · July 2017

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

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

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 · July 2017

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 · July 2017

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 · July 2017

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 · July 2017

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

Allan

$editor->db() with nested Select Query

$editor->db() with nested Select Query

Answers

Howdy, Stranger!

Categories

DataTables

$editor->db() with nested Select Query

$editor->db() with nested Select Query

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables