Connector with multiple tables not updating

Suite and Optimus Suite 5
#1

I’m using the code as provided in the sample provided [url]Start DHTMLX Docs in the Connector section, but the update portion of the code isn’t working - i.e. changes aren’t saved to the database table. The grid renders fine, but updates just don’t get saved.

if ($grid->is_select_mode())//code for loading data
    $grid->render_sql("Select * from tableA, tableB  where  tableA.id=tableB.id", "a.id","name,price,other");
else //code for other operations - i.e. update/insert/delete
    $grid->render_table("tableA","id","name,price");

Each $grid->render… works okay by itself. In other words, if I elminate the if…else structure and only use one of the render statements, then that statement works fine.

So the following works great by itself for viewing/rendering the grid

    $grid->render_sql("Select * from tableA, tableB  where  tableA.id=tableB.id", "a.id","name,price,other");

And the following works great for just listing the columns from tableA and UPDATEing the data

    $grid->render_table("tableA","id","name,price");

But putting both statements together as shown in the example at the very top = no worky.

I’ve also verified the is_select_mode() is being evaluated correctly, based on whether I’m editing a value in the grid or simply viewing, so I know each branch of the “if…else” statement is being handled, but I’m perplexed on what could possibly be preventing the edited information from being updated in the connected table.

Here’s what I’ve tried unsuccessfully so far, and at this point am at a loss for what else to check (the documentation could surely be a bit more explanatory).

[] I’ve searched around the forums and seen everything from "make sure columns are in identical order for both “render” statements (which I’ve done - no success).[/]
[] I tried changing the “render_table” to a “render_sql” and included all the columns from the single table (no success) [/]
[] Now falling into the spiral of looking at sql_attach and event->attach and something about “beforeUpdate” to try and get this to work[/]

Anyone have any clues that might help me solve this issue?

Thanks in advance.

#2

Have dug a bit further and started logging (yes, the newbie is coming along).

Anyone have any idea as to why the statement $grid->render_table returns a valid id value when used alone, but when this same statement is within an if ($grid->is_select_mode()) the id value for the record being edited changes to something like 1394928115x1?

#3

The record line next - 1394928115x1, occurs when connector can’t find the “id” field in the result dataset, so it will autogenerate some random ids. Check the second parameter of render command - it must be a valid field name ( in case of render_sql - it must be selected in the provided sql )

Please try to grab the latest connector codebase from here, it has some fixes, which may be related to your case.

github.com/DHTMLX/connector-php

#4

Try to change

$grid->render_sql("Select * from tableA, tableB  where  tableA.id=tableB.id", "a.id","name,price,other");

as

$grid->render_sql("Select *, a.id as masterid from tableA, tableB  where  tableA.id=tableB.id", "masterid","name,price,other");
#5

The admin should update the manual, because just like Stanislav replied, the original code won’t work upon updating:
It needs to be changed to:

if ($conn->is_select_mode()) $grid->render_sql("Select *, a.id as masterid from tableA, tableB where tableA.id=tableB.id", "masterid","name,price,other"); else $conn->render_table("tableA","id","id, name")

HOWEVER!

When I tried updating the ‘name’ from tableA on my table now, the updates work for tableA but tableB.id get’s incremented and updated as well… So my records are not properly linked anymore.
Does anyone know why?

Also one small question. In the above code it says “a.id” but I wonder where this comes from. Shouldn’t it say “tableA.id” ?

#6

I found out that the order of the fields
at these locations need to be in the same order, or the update process will be messed up!

$grid->render_sql("Select …, “masterid”,“name,price,other”);
↳ Here you’re only showing name,price,other
$conn->render_table(“tableA”,“id”,“id, name”)
↳ But here you’re updating the data in the order of: id,name.

This will cause the upper data that’s showed to get updated to the order of the in the render_table.
name,price,other
↓ ↓
id, name

So the tableA.id will get the name field, and the tableA.name will get the price!!!

Just make sure the order is the same and all will be fixed!
This thread can be marked as resolved.

#7

I tried this on my code and it did not work. can someone give an example that works.
I am using the join command to get information from multiple tables but I only need to update only one of them.
the viewing work but not the updating. I act like it working the grid does not go bold or red but the data is not saved. Can someone please help? thanks