Hi,
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.
render_table:
$scheduler->render_table("events","id","start_date,end_date,event_name,details");
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.
render_sql:
$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.
render_complex_sql
$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
- 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.
- 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;
$action->set_new_id($res->lastInsertId());
}
$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 docs.dhtmlx.com/connector__php_ … idelogging
$connector->enable_log("log.txt");
- 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)
