render_sql to select but sql->attach('Update', ... ) fails


I’ve read this … dates.html and made sure I have the $conn->attach(“Update”, "Update … ); before the $conn->render_sql("SELECT …); and I have made sure the render_sql statement has the second @param id field set.

I have tried various versions of: dp.setTransactionMode('POST [or JSON] ', false); with no success. Even:

dp.setTransactionMode({ mode:"JSON", headers:{ "Accept-Language": "en-EN" }, payload: { id: '24' } }, true);

Is the setTransactionMode applicable to the Scheduler or only the Grid; and if so, is this where I should be looking for the solution?

On the server I am using scheduler_connector.php with db_pdo.php

When swapping out the render_sql query for the basic render_table I get a nice POST of all the variables and the system uses the ones that are in the 1st @param ‘table’ of the render_table(). Everything updates both on the back and front end. Nice!

Am I missing an obvious step? I need to SELECT the elements from other databases and I really only need to UPDATE, INSERT, DELETE from the main table that dhtmlx uses to store the ‘start_date,end_date,text,plus-my-own-vars’ ?

In Summary:
How can I use a SELECT in either a render_sql() or render_complex_sql() and maintain the ability to update/insert/delete just the one table.

BTW, I thought this solution was a perfectly suggested format for me to use but I couldn’t get it to work either:

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

I can’t help but feel I am missing something obvious. (Or, is the use of PDO the issue?)


but sql->attach(‘Update’, … ) fails

Can clarify how exactly does it fail? Do you have a runtime php error, or is there a database error (btw, please make sure to enable connector logs if you haven’t done it ), or something else?

Also, please show the code you currently have, db tables schema, or anything else that would help us to check the issue.

Usually such approach should work:

if ($grid->is_select_mode())//code for loading data $grid->render_sql("Select * from tableA, tableB where", "","name,price,other"); else //code for other operations - i.e. update/insert/delete $grid->render_table("tableA","id","name,price");
You can specify a complex select query for a select request, and use a different configuration for updates.
But since it doesn’t seem to work for you - we need some more details to understand what exactly you doing and what goes wrong.
Regarding dataprocessor:

dp.setTransactionMode({ mode:"JSON", headers:{ "Accept-Language": "en-EN" }, payload: { id: '24' } }, true);
Currently, dataprocessor bundled with dhtmlxScheduler does not support JSON mode or headers/payload properties of transaction mode.

Thanks for the reply. I appreciate it. And I appreciate the DHX products you have made available to the community. You have excellent documentation too! But my struggle was due to (what I believe to be) an undocumented procedure on your system that I discovered in a DHX moderator’s helpful response, found here.

[code]Normally after inserting new row, client side code changes id to the value which was set by database ( works only for insert, will not work for update )

So if you are using default connector saving functionality, new id value will be automatically sent to client side and updated in grid ( assuming that you have auto-increment id column )

If you are using custom saving logic, you can use onAfterInsert event and from it call $action->set_new_id($value);[/code]

Once I discovered that info (even though specific to Grid it proved true for Scheduler as well) I simply used another column to base my queries off of.

I solved my problem and have since moved on, but I would be curious to know if this is truly something you have not made clear in your docs or whether my understanding is off somewhere.

Thank you.

seems like $action->set_new_id is missing from DataAction API page, sorry for the inconvenience.
We’ll update that article shortly … thods.html

Taking another look at this, could you add an example on how this is suppose to work?

I have tried a few different combinations with the goal of trying to get the client side event id to match what is in the database under the id column for the inserted event row. Presently, I am showing a db ‘id’ column of something like ‘20’ and a client side ‘event_id’ of something like ‘1497277345189’. My ‘id’ column is auto-incremented.

My insert statements are pretty basic:

$conn->sql->attach("Insert", "INSERT INTO my_table (start_date, end_date, text, ...) VALUES ('{start_date}', '{end_date}', '{text}, ...')", "id", "start_date,end_date,text,...");

I have a select statement like:

$conn->render_sql("a select statement with a series of joins to pull in info for display in lightBox sections", "id", "start_date,end_date,text, ...");

And then some attempts to solve this id discrepancy with something like:

$conn->event->attach("afterProcessing", $action->set_new_id(240));

where ‘set_new_id($value)’ has also been tried as ‘get_new_id()’ or even ‘get_id()’
and ‘240’ has also been tried as ‘last_insert_id()’ and ‘mysql_insert_id()’
and ‘afterProcessing’ has also been tried as ‘onAfterInsert’

My goal:
A way to find the scheduler’s event row in my Delete and Update queries.

I have tried
All of your server side and client side code to simply replace what appears to be a timestamp event_id on the client with the db assigned id taken from the auto-incremented id column. I see no documentation on this process or any mention of it as a requirement when using custom SQL. Can you link me to some documentation or give me a hint on how to make this work?

it may look something like this:

include (’…/…/common/config.php’);

$scheduler = new schedulerConnector($res, $dbtype);

$scheduler->sql->attach(“Insert”, “INSERT INTO events (start_date, end_date, event_name, details) VALUES (’{start_date}’, ‘{end_date}’, ‘{event_name}’, ‘’)”);

function setId($action){
global $res;
$scheduler->event->attach(“afterInsert”, “setId”);

I took this example scheduler/samples/01_initialization_loading/data/events.php and switched insert action to a custom query.
$res - PDO connection
$dbtype - “PDO”

Your code example DOES properly render the client-side event_id’s to match the db’s auto-incremented ‘id’ column BUT ONLY because your use of render_table to display it. That has never been the problem as I have always known that avoiding both ‘render_sql’ and ‘render_complex_sql’ and using ONLY render_table has worked from the beginning.

There appears to be no reason to use your specified ‘common_db’ file or even the attach(‘afterInsert’…) and setId() routine because the DHX system still does not rewrite the event_id’s to match the db ‘id’ column whenever render_table is NOT used and either of the ‘render_sql’ or ‘render_complex_sql’ statement are use instead - which makes any subsequent Delete or Update sql statement impossible to implement.

I sure would like to know that I am wrong and simply ‘brain dead’ when it comes to understanding your code. Please forward a solution if you have it.


I just wanted to add some thoughts for those who might also be struggling with DHX.

There is a LOT or work done on this code base. I haven’t dealt with it long but historical posts (both here and elsewhere) mention poor documentation. To me - at least ‘today’ - it appears to be pretty detailed at the micro level, but still lacks good structure at the macro level.

Example 1: Why is the detailed description of DHTMLX Connector buried in the php connector documentation? I think it’s because DHX wants to promote the ease of use of their tools, in their words:

But for people who know SQL - which I think many who choose to use this library do - this needs to be highlighted and made to be a big part of why to choose DHX. The DHX website has almost a ‘simplistic’ imagery to promote it’s ‘ease of use’ but this leads people like me to run into the powerful under-current of DHX’s system and struggle with it more than I should as my knowledge of how SQL works starts to break their system because I never saw how much was going on under the surface until I was well into debugging it.

Example 2: To me, the side navigation panes are broken into sections (probably to try and make the documentation look less immense?) and I find this to be confusing and difficult to track. A good example of great documentation on complex issues is Amazon’s AWS - that side pane navigation helps to keep the mind at ease by knowing where it is in the big picture while it focuses in on the micro issue at hand.

Example 3: A better description of the DHX custom server-side events is needed. I find the DHX server-client communication to be incredibly good, except when I need to interact and customize it. In this thread, I am being advised to use:


but I have since read - but cannot find it because it is buried in a forum comment and not in the documentation where it needs to be - that when using ->sql->attach(“insert | delete | update”, …) you override the DHTMLX Connector - essentially breaking the system. But you could use one of DHX’s customized server-side events, like ->event->attach(‘afterInsert’, …) to add my custom need to any insert (for example) without breaking the DHX Connector system - instead working with it.

In summary:
I can see the brain power behind DHX and it is admirable. It appears the documentation is coming along but needs more macro attention to pull the micro documentation effort into a comprehensive understanding for those who come at this project with more than a ‘simple’ need and with a full head of steam to ‘customize it’ from the get-go. Which, I would image, is going to be your core customer base. Right now, I am burning money and time learning this system and feel I need to recover before spending $1000 on some of your products. In that regard, your documentation and help has cost you. But I like that your system is extremely advanced and I do see myself as a future customer of yours. But right now I am huddled in the corner, weeping and do not want to come outside and play. :slight_smile:


I’m not sure I completely understand this part. I’ll try to give more details on the overal idea and differences in inner work of render_table, render_sql and render_complex_sql - and hopefully it’ll make clearer where I was going with my previous reply.



you specify table name, primary key and columns to use. From this, connector is able to create INSERT, UPDATE and DELETE sql queries so they don’t have to write them manually.
It also assumes that primary key column is auto increment, so each time item is added on the client firstly it gets temporary id, then after inserting connector passes database id back to the client where it’s applied.
So, in the simplest case everything should be working by itself, without writing any sql. Even if events have references to different tables (i.e. event.statusId -> statuses table, event.ownerId -> users table, etc.) - it still may be a viable approach to do simple selects on a backend, selecting related tables into separate lists, and then join records on the client using scheduler templates - that way, if you modify foreign key on the client - you’ll be able to display correct label (status name/user name) right away, without reloading data from backend to do another sql join.


$scheduler->render_sql("SELECT * FROM events WHERE ....","id","start_date,end_date,event_name,details"); connector will attempt to parse SELECT query and create INSERT/UPDATE/DELETE sql using tablename and provided columns. In case simple queries which works with a single table this method works the same way as render_sql.
So if I’d use render_sql in my previous example everything should have worked as expected with or without sql->attach(“Insert” command.

However, if you select from multiple table or use JOIN (which I now realize you do, just re-read you post from 12th June) - connector won’t be able to parse such sql, which is probably what happens in your case.


$scheduler->render_complex_sql("SELECT * FROM events WHERE ....","id","start_date,end_date,event_name,details"); connector won’t attempt to parse provided sql at all and won’t generate update queries

So, what is usually done when auto generated queries are not available

  1. If you select data from multiple tables, but write only to one, you can use complex query for selects, provide a simple config from which actions can be generated for updates, i.e.

if($scheduler->is_select_mode()){ $scheduler->render_complex_sql("a select statement with a series of joins to pull in info for display in lightBox sections", "id", "start_date,end_date,text, ..."); }else{ $scheduler->render_table("events","id","start_date,end_date,text,..."); }with such setting, connector will select data using your sql, and will insert/update/delete using table name and columns you provide into render_table.

  1. Or, you specify all actions manually. Returning to my previous example, it could look following:

[code]$scheduler = new schedulerConnector($res, $dbtype);

$scheduler->sql->attach(“Insert”, “INSERT INTO events (start_date, end_date, event_name, details) VALUES (’{start_date}’, ‘{end_date}’, ‘{event_name}’, ‘’)”);
$scheduler->sql->attach(“Update”, “UPDATE events SET start_date=’{start_date}’, end_date=’{end_date}’, event_name=’{event_name}’, details =’’ WHERE event_id = ‘{event_id}’”);
$scheduler->sql->attach(“Delete”, “DELETE FROM events WHERE event_id = ‘{event_id}’”);

function setId($action){
global $res;
$scheduler->event->attach(“afterInsert”, “setId”);
$scheduler->render_complex_sql(“big sql query”,“event_id”,“start_date,end_date,event_name,details”);[/code]

Important points are:

  • table is assumed to have primary key column and this column is set auto increment.
  • if you select from multiple tables, or if you use render_complex_sql - custom sqls or simplified connector config for update operations should be provided.
  • if you use a custom “Insert” query, you need to make sure you return new record database id to the client, it can be done using ‘afterInsert’ event. Alternatively, you could make the client-side to reload all data after ajax is done and get new id that way. Which would also work but is usually a bit overkill.
  • no postprocessing (i.e. $connector->event->attach) is needed for connector->sql->attach(“update|delete” commands. Although, it’ll depends on the configuration, e.g. if during the update you actually delete old record and insert a new one - you’ll need to pass new id back to the client, similarly as with ‘insert’ query, or if want to write additional info to the response. But otherwise they should work without affecting anything around.

One thing you may want to do, is to enable connector logging … idelogging

  • it will write all actions, sql commands and database errors into text file, which usually helps a lot.

My previous examples seemed to work correctly for select/insert/update/delete actions (both sample from my previous reply using render_table and render_sql, and a sample from (2) of this reply), I’ve checked them agains ‘events’ table from samples/common/dump.sql
If the similar code didn’t work for you - I’ll need some more info, code samples, log errors, etc. to understand what’s wrong.

So, hope it helps or at least sheds some light on how everything is expected to work.
Please let me know if there are still issues, or if I misunderstood any of your points, or if I’ve lost my thought somewhere in the middle of this long read.

Regarding documentation - I have to agree with your points and I appriciate the feedback.
The good news are, we’re going to update docs on server-side integration - firstly on dhtmlx suite, and gantt and sheduler shortly after, hopefully it will improve things.
meanwhile, we’ll add some info to php connector docs (probably with the details from this thread, if we’ll clear that I understood your issue correctly and the info had been of any use)

I wanted to send a quick ‘thank you’ for taking the time to write such a in-depth reply. I believe I understand all of what you just outlined, but with a feeling that it took me two days to learn through frustration what I could have gathered more quickly with better documentation from the outset.

Clearly, your code base can work out of the box with no SQL, something which is quite amazing. And I do have a very advanced usage of it which caused me to jump into the deep end really quick and that’s where I felt like I was doing more damage than good - raising the frustration level on my end. Even after reading your last post, I can say I have yet to see anything nearly as descriptive of how your system works in any of your documentation. Clearly, you have a product that programmers with a simple need can use. But you also have one of the most sophisticated product offerings that programmers with advanced needs would gravitate to as well. When I scour an API for use I am looking for ‘payback’ time where my time invested into learning it can meet many future needs I may have. The DHTMLX offerings have that appeal.

I would ask for a ‘Heads Up’ sign on the front of your documentation for those running into your code base looking to ‘do more’ with it. Much of what you outlined in your code base ability is not normal in my experience. I eventually started to get the gist but only after perusing many of the helpful comments others gained through hard experience - not the best/happiest way to learn and develop code. The ‘ease of use’ lured me in (so that’s good) and I intuitively knew there was a lot of work in the DHX engine as I dabbled in it to start, but I lost more than a couple of fingers when trying to implement my custom needs (ouch).

When contemplating how to document this stuff you might want to try something ‘negative’ before throwing the DHX bible at them. Try some ‘reverse psychology’ since programmers are already into parsing things apart and you might find that articulating a few ‘wipeouts’ might help them to see the need for contemplative study and consideration before proceeding - giving them a reason to read yet ‘another’ programmer’s cookbook.

I do have a couple of questions about your last post, but let me experiment a little bit more before I ask. Again. ‘Thank You’ for the response.

Without question, but often it doesn’t explain why something is failing, just that it is.

Really? Are you suggesting something like:

$conn->render_sql("SELECT (SELECT  start_date,end_date,text,...,...,etc FROM a_table WHERE something=something) as a_list, (SELECT itemb FROM b_table WHERE somethingelse=somethingelse) as b_list", "id", "start_date,end_date,text,...,...,etc");

There is no example in your docs about doing anything like this. Are you suggesting untested ideas or am I not understanding you?

... - that way, if you modify foreign key on the client - you'll be able to display correct label (status name/user name) right away, without reloading data from backend to do another sql join.

What? Foreign key on the client?? This sounds like gibberish to me. Can you give an example of your thought?

Because I needed to update 2 tables I was falling into your example 2 scenario. I believe I discovered that you cannot place those ->sql->attach( inside the else of an if (is_select_mode). Like:

if($scheduler->is_select_mode()) { ... } else { // no ... $scheduler->sql->attach("Insert", ... in here }

I also struggled with:

function setId($action){ global $res; $action->set_new_id($res->lastInsertId()); } $scheduler->event->attach("afterInsert", "setId");
which always produces this error:

exception 'Exception' with message 'Incorrect function assigned to event: setId ' in /var/www/clients/client_num/water_resource/web/application/js/scheduler/connector/tools.php:100 Stack trace: #0 ..... scheduler/connector/dataprocessor.php(225): EventMaster->trigger('afterinsert', Object(DataAction)) #1 ....... scheduler/connector/dataprocessor.php(168): DataProcessor->check_exts(Object(DataAction), 'insert') #2 ....... scheduler/connector/dataprocessor.php(97): DataProcessor->inner_process(Object(DataAction)) #3 ....... scheduler/connector/base_connector.php(492): DataProcessor->process(Object(DataConfig), Object(DataRequestConfig)) #4 ....... scheduler/connector/base_connector.php(448): Connector->render() #5 ....... customers.php(controller file, class): Connector->render_sql('SELECT', 'id', 'start_date,end_...')

And line 100 of tools.php reads as:

public function trigger($name,$data){
        $arg_list = func_get_args();

        if (isset($this->events[$name]))
            foreach($this->events[$name] as $method){
                if (is_array($method) && !method_exists($method[0],$method[1]))
                    throw new Exception("Incorrect method assigned to event: ".$method[0].":".$method[1]);
                if (!is_array($method) && !function_exists($method))
                    throw new Exception("Incorrect function assigned to event: ".$method);
                call_user_func_array($method, $arg_list);

        if ($this->master !== false)
            if (method_exists($this->master, $name))
                call_user_func_array(array($this->master, $name), $arg_list);

        return true;

This function is being called within a php class method. I have tried all the obvious signatures to get this function to be recognized in order to fire. Do you have a solution for this? I am running version 4.4.0 of scheduler.

Beyond that problem above, there is the abnormalities of what you (and other forum help moderators) use in terms of your code suggestions. For example, compare your example item 2 function signature which is the one I really need to work (note the: WHERE event_id = ‘event_id’ ) with what is in your documentation:

You can define your own SQL code for a specific action (INSERT, UPDATE or DELETE) as follows:
$grid->sql->attach("Update","Update tableA set name='{name}',
    price={price} where id={id}");
$grid->render_complex_sql(" .. ","id","price,name");

Yes, everything works nicely if I just stay with the one render_table for all things insert | update | delete. I appreciate your description and help.

If you could focus on the last two points first, to enable me a working attach(insert | delete | update queries that get their id’s set with your ‘afterInsert’ setId example I would greatly appreciate it.

Thanks for the help!

maybe a poor choice of words from my end, what I meant is following
please consider this demo - … l5-js-php/
there are three tables related to calendar entities - events(bookings) themselves, states of bookings (arrived,checked out, etc) and room :

[code]CREATE TABLE bookings (
start_date date DEFAULT NULL,
end_date date DEFAULT NULL,
text text,
room int(11) DEFAULT NULL,
status int(11) DEFAULT NULL,
id int(11) NOT NULL,
is_paid tinyint(1) DEFAULT NULL

CREATE TABLE booking_statuses (
id int(11) NOT NULL,
name text

id int(11) NOT NULL,
label text NOT NULL,
type int(11) NOT NULL,
status int(11) NOT NULL
haven’t used foreign keys here, but the relations are following: -> booking.status ->
On the client, we need to rooms.label and to display the event (booking)

Such setting is usually when sql JOIN is considered to be used, and this sample hopefully has enough parallels to what you’re doing to serve as an illustration.

So, there are two alternative ways to go for displaying such calendar

  • join tables on a backend using connector configuration, thus send formatted entries to the client.
  • send tables to the client as individual lists, and format them on the client - which I meant in my previous reply.

How it’s done in … l5-js-php/

that way, if I change values of relation columns (e.g. change status of the booking from “arrived” to “checked out”) - client side have all required info to display updated event properly, while if we’d used JOIN on the backend - we’d either had to reload modified entry from a backend to get updated labels or to duplicate part of the formatting logic both on the client and on the server, or figure something else.
Overall with this approach, backend stays relatively simple, more logic is shifted to the client-side, and everything works properly from an end user perspective.

Such approach may be not always worthwhile, and I’m not saying it will work for your setup - since I don’t exactly know what your setup is.
Overall, it can be a viable option.

I’m still not getting the bigger picture of what you’re doing, so this may be not very useful for your specific setup.

Well, I don’t have any obvious clue on what wrong. From I can tell, it’s not a common issue.
I’m attaching an example that is working for me … (again, it’s modified scheduler/samples/01_initialization_loading/data/events.php )
seems working correctly in php 5.6.x and 7.0.x

You can try also using the latest build of php connector , although I doubt that’s the case.

sorry, my mistake, I’ll correct my previous reply

I still fail to see the difference in work of sql->attach between render_table and render_sql/render_complex_sql, since at least in the samples I just checked it works identically.
So, if php I attach doesn’t do any help - please send me some code, preferably complete php scripts, not isolated lines, so I could understand what’s going on there and test the solution in work if I’ll be able to find it. Since scripts would probably require database - sql schemas of required tables and some test data would also help.
You can send it with private messages if you don’t want to upload them publicly

The reason your simple example ( works for you is that you are not having to work within a namespace environment as a class method. But looking at the signature of the DHX ‘trigger’ method that is throwing the error, it allows for the second parameter to be an array where you can give it the instantiated object in which you are accessing the db as the first parameter and the string name of its class method you want to call as the second. I thought it needed to be a static method but that was wrong because while calling a ‘public static function setId()’ works it leaves you outside the instantiated object with no way to retrieve the ‘lastInsertId()’ (from the db resource) to complete the routine.

So the answer is - and there is no mention of this in your docs and it should be since almost everyone nowadays is working within these frameworks - to simply pass the instantiated object $this as the first param of the array which is itself the second param of the DHX ‘attach’ method so that the DHX ‘trigger’ method can handle it.

$scheduler->event->attach("afterInsert", array($this, "setId"));

I see Obi-Wan-Kenobi
I shall keep, study and harbor such knowledge until the force be with me! :smiley:

Can you tell me why an event - after inserting it using the code we have been discussing - looks good; meaning, it has the same event_id in the console as it is in the database in the ‘id’ column (not ‘event_id’, just ‘id’ as the auto-incremented value your docs state). But when the page refreshes it comes back as the original timestamp value? What’s up with that?

No errors in the dhtmlx log.

I tried switching out ‘id’ with ‘event_id’ in both the database (as the auto-increment key and as just a normal column name) as well as in the render_sql query’s second parameter; from ‘id’ to ‘event_id’. No success. It always comes back wrong when the page refreshes.

Aliaksandar, we get all the way to here - the ‘afterInsert’ updating the id on inserts - and now the render_sql() and render_complex_sql() select statements generate only temporary timestamp values, not the db ‘id’ values. No errors in the dhtmlx error log. A render_table works as you would expect, the db ‘id’ values matching the ‘event_id’ values in the dom elements. But this will not work for my needs, neither will the example you gave about the Hotel scheduler.

Is there a solution to this problem? It seems rather simple and there should be something easy to make it work. What am I missing? I’ve googled to the end of the Net, can’t find it.