Editor Not Updating MySQL DB

gforster

https://debug.datatables.net/ajeqil

I can't figure this one out. In Editor, I have this field:

{
     label: "Volume:",
     name: "VOLUMES[].id",
     type: "select",
     placeholder: "Select The Volume"
 },

The call looks like this:

     ->join(
         Mjoin::inst( 'VOLUMES' )
             ->link( 'SERVERS.id', 'VOL_SERVER.server_id' )
             ->link( 'VOLUMES.id', 'VOL_SERVER.vol_id' )
             ->fields(
                 Field::inst( 'id' )
                     ->options( Options::inst()
                         ->table( 'VOLUMES' )
                         ->value( 'id' )
                         ->label( 'name' )
                     ),
                 Field::inst( 'name' )
             )
         )

It displays fine in the DataTables view. In the Editor view, if the field is already populated, it displays properly. However, if you make a change, nothing actually gets updated.

I've done this same type of thing in other tables without issue. I feel like I'm missing something very obvious. thanks!

allan

Could you add the ->debug( true ) option into your Editor PHP initialisation (just before the ->process() method call would be fine) and then show me what the JSON that is being returned from the server is when you make an edit?

Thanks,
Allan

gforster

Here is the JSON (obviously more thorough than the above when including the full request):

{"data":[{"DT_RowId":"row_3","SERVERS":{"id":"3","name":"D-IDM-BOT1","model_id":"1","os_id":"5","ip_id":null,"location_id":"2","devlvl_id":"1","patching_id":"2"},"MODEL":{"name":"VM"},"OS":{"name":"SLES 12.2"},"LOGICALIP":{"address":null},"LOCATION":{"name":"NCASB"},"DEVLVL":{"name":"dev"},"PATCHING":{"name":"2","timing":"Tuesday 1pm"},"VOLUMES":[]}],"debugSql":[{"query":"SELECT  `id` as 'id' FROM  `OS` WHERE `id` = :where_0 ","bindings":[{"name":":where_0","value":"5","type":null}]},{"query":"SELECT  `id` as 'id' FROM  `LOCATION` WHERE `id` = :where_0 ","bindings":[{"name":":where_0","value":"2","type":null}]},{"query":"SELECT  `id` as 'id' FROM  `DEVLVL` WHERE `id` = :where_0 ","bindings":[{"name":":where_0","value":"1","type":null}]},{"query":"SELECT  `id` as 'id' FROM  `PATCHING` WHERE `id` = :where_0 ","bindings":[{"name":":where_0","value":"2","type":null}]},{"query":"SELECT  * FROM  `SERVERS` WHERE `SERVERS`.`id` = :where_0 ","bindings":[{"name":":where_0","value":"3","type":null}]},{"query":"UPDATE  `SERVERS` SET  `name` = :name, `model_id` = :model_id, `os_id` = :os_id, `location_id` = :location_id, `devlvl_id` = :devlvl_id, `patching_id` = :patching_id WHERE `SERVERS`.`id` = :where_0 ","bindings":[{"name":":name","value":"D-IDM-BOT1","type":null},{"name":":model_id","value":"1","type":null},{"name":":os_id","value":"5","type":null},{"name":":location_id","value":"2","type":null},{"name":":devlvl_id","value":"1","type":null},{"name":":patching_id","value":"2","type":null},{"name":":where_0","value":"3","type":null}]},{"query":"SELECT  `SERVERS`.`id` as 'SERVERS.id', `SERVERS`.`id` as 'SERVERS.id', `SERVERS`.`name` as 'SERVERS.name', `SERVERS`.`model_id` as 'SERVERS.model_id', `MODEL`.`name` as 'MODEL.name', `SERVERS`.`os_id` as 'SERVERS.os_id', `OS`.`name` as 'OS.name', `SERVERS`.`ip_id` as 'SERVERS.ip_id', `LOGICALIP`.`address` as 'LOGICALIP.address', `SERVERS`.`location_id` as 'SERVERS.location_id', `LOCATION`.`name` as 'LOCATION.name', `SERVERS`.`devlvl_id` as 'SERVERS.devlvl_id', `DEVLVL`.`name` as 'DEVLVL.name', `SERVERS`.`patching_id` as 'SERVERS.patching_id', `PATCHING`.`name` as 'PATCHING.name', `PATCHING`.`timing` as 'PATCHING.timing' FROM  `SERVERS` LEFT JOIN `MODEL` ON `SERVERS`.`model_id` = `MODEL`.`id`  LEFT JOIN `OS` ON `SERVERS`.`os_id` = `OS`.`id`  LEFT JOIN `LOGICALIP` ON `SERVERS`.`ip_id` = `LOGICALIP`.`id`  LEFT JOIN `LOCATION` ON `SERVERS`.`location_id` = `LOCATION`.`id`  LEFT JOIN `DEVLVL` ON `SERVERS`.`devlvl_id` = `DEVLVL`.`id`  LEFT JOIN `PATCHING` ON `SERVERS`.`patching_id` = `PATCHING`.`id` WHERE `SERVERS`.`id` = :where_0 ","bindings":[{"name":":where_0","value":"3","type":null}]},{"query":"SELECT DISTINCT  `id` as 'id', `name` as 'name' FROM  `MODEL` ","bindings":[]},{"query":"SELECT DISTINCT  `id` as 'id', `name` as 'name' FROM  `OS` ","bindings":[]},{"query":"SELECT DISTINCT  `id` as 'id', `address` as 'address' FROM  `LOGICALIP` ","bindings":[]},{"query":"SELECT DISTINCT  `id` as 'id', `name` as 'name' FROM  `LOCATION` ","bindings":[]},{"query":"SELECT DISTINCT  `id` as 'id', `name` as 'name' FROM  `DEVLVL` ","bindings":[]},{"query":"SELECT DISTINCT  `id` as 'id', `name` as 'name' FROM  `PATCHING` ","bindings":[]},{"query":"SELECT DISTINCT  `SERVERS`.`id` as 'dteditor_pkey', `VOLUMES`.`id` as 'id', `VOLUMES`.`name` as 'name' FROM  SERVERS as SERVERS  JOIN `VOL_SERVER` ON `SERVERS`.`id` = `VOL_SERVER`.`server_id`   JOIN `VOLUMES` ON `VOLUMES`.`id` = `VOL_SERVER`.`vol_id` ","bindings":[]},{"query":"SELECT DISTINCT  `id` as 'id', `name` as 'name' FROM  `VOLUMES` ","bindings":[]}]}

allan

Thank you! Next question (sorry about this, I should have thought to ask it initially) - can you send me the data that is being sent to the server please? If you click on the "Headers" section of the request in the network inspector for the submit Ajax request, it should show it there.

The debug trace there shows that there isn't any write happening to the VOL_SERVER table, which it looks like there should be happening. So we move on to why that isn't happening!

Thanks,
Allan

allan

Oh - actually one thing. The debug trace shows you are using Editor 1.6.0. Could you update to 1.6.5 (the current release) please? Both on the client-side and the server-side. I've got a feeling this was an issue in 1.6.0 or 1.6.1.

Thanks,
Allan

gforster

Sure thing. In reverse order...I did upgrade to 1.6.5 (but for some reason debugger is still showing 1.6.0, so I assume there is a specific file that didn't get overwritten)
https://debug.datatables.net/aruzoy

the headers (sanitized sensitive data):

Request URL:https://.../php/tables/servers_table.php
Request Method:POST
Status Code:200 OK
Remote Address:127.0.0.1:443
Referrer Policy:no-referrer-when-downgrade

Response Headers

Connection:Keep-Alive
Content-Length:3001
Content-Type:text/html; charset=UTF-8
Date:Fri, 06 Oct 2017 11:44:21 GMT
Keep-Alive:timeout=15, max=100
Server:Apache
X-Powered-By:PHP/7.0.7

Request Headers

POST /php/tables/servers_table.php HTTP/1.1
Host: mywebsite
Connection: keep-alive
Content-Length: 322
Origin: https://mywebsite
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.109 Safari/537.36
Content-Type: application/x-www-form-urlencoded; charset=UTF-8
Accept: application/json, text/javascript, */*; q=0.01
X-Requested-With: XMLHttpRequest
Referer: https://mywebsite/stats/
Accept-Encoding: gzip, deflate, br
Accept-Language: en-US,en;q=0.8
Cookie: ZNPCQ003-32333500=8ced55b1; ZNPCQ003-33323900=01e63202; __utma=238157596.963402886.1485790093.1502721002.1502721002.1; __utmc=238157596; __utmz=238157596.1502721002.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); _ceg.s=ouoiy2; _ceg.u=ouoiy2; gwlob=on; _ga=GA1.2.963402886.1485790093; SimpleSAMLAuthToken=_RandomString

Form Data

action:edit
data[row_3][SERVERS][name]:D-BOT1
data[row_3][SERVERS][model_id]:1
data[row_3][SERVERS][os_id]:5
data[row_3][SERVERS][location_id]:2
data[row_3][SERVERS][devlvl_id]:1
data[row_3][SERVERS][patching_id]:2
data[row_3][VOLUMES]:29

allan

Got it - add multiple: true into your select field's configuration.

Without it is just submitting a single value as a string. You need it to be submitted as an array if it is going to an Mjoin.

Allan

gforster

That was it. Thank you!