Render_table VS render_sql

Suite and Optimus Suite 5
#1

Could use another set of eye to review this. The render_table line works but the render_sql does not. What I’m looking to do is only select those records where the user is equal to john. I just can’t see what’s wrong.

Thank you,
John

$tree->render_table(“bulk_update”,“taskId”,“product,item,value”,“”,“parentId”);

$tree->render_sql(“SELECT * FROM bulk_update bu WHERE bu.user=‘john’”, “bu.taskId”,“bu.product,bu.item,bu.value”);

This is the table structure:

CREATE TABLE bulk_update (
taskId int(11) NOT NULL,
parentId int(11) DEFAULT NULL,
product varchar(100) DEFAULT NULL,
item varchar(45) DEFAULT NULL,
value varchar(45) DEFAULT NULL,
user varchar(15) DEFAULT NULL,
type char(1) DEFAULT NULL,
PRIMARY KEY (taskId)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

#2

This is the error I get:

PHP Fatal error: Uncaught exception ‘Exception’ with message ‘MySQL operation failed
Unknown column ‘’ in ‘where clause’’ in /provision/htdocs/api/dhtmlxConnector/codebase/db_common.php:965
Stack trace:
#0 /provision/htdocs/api/dhtmlxConnector/codebase/db_common.php(650): MySQLDBDataWrapper->query(‘SELECT * FROM …’)
#1 /provision/htdocs/api/dhtmlxConnector/codebase/base_connector.php(513): DBDataWrapper->select(Object(DataRequestConfig))
#2 /provision/htdocs/api/dhtmlxConnector/codebase/base_connector.php(496): Connector->get_resource()
#3 /provision/htdocs/api/dhtmlxConnector/codebase/base_connector.php(432): Connector->render()
#4 /provision/htdocs/api/dhtmlxConnector/samples/treegrid/02_dynamic_loading_connector.php(20): Connector->render_sql(‘SELECT * FROM b…’, ‘bu.taskId’, ‘bu.product,bu.i…’)
#5 {main}
thrown in /provision/htdocs/api/dhtmlxConnector/codebase/db_common.php on line 965

#3

Change you code as

$tree->render_sql("SELECT * FROM bulk_update bu WHERE bu.user='john'", "taskId","product,item,value");

or like next

$tree->render_sql("SELECT * FROM bulk_update bu WHERE bu.user='john'", "bu.taskId(taskId)","bu.product(product),bu.item(item),bu.value(value)");

The ID and FIELDS parameters must not have table name attached.

#4

#1 fails with:

PHP Fatal error: Uncaught exception ‘Exception’ with message ‘MySQL operation failed
Unknown column ‘’ in ‘where clause’’ in /provision/htdocs/api/dhtmlxConnector/codebase/db_common.php:965
Stack trace:
#0 /provision/htdocs/api/dhtmlxConnector/codebase/db_common.php(650): MySQLDBDataWrapper->query(‘SELECT * FROM …’)
#1 /provision/htdocs/api/dhtmlxConnector/codebase/base_connector.php(513): DBDataWrapper->select(Object(DataRequestConfig))
#2 /provision/htdocs/api/dhtmlxConnector/codebase/base_connector.php(496): Connector->get_resource()
#3 /provision/htdocs/api/dhtmlxConnector/codebase/base_connector.php(432): Connector->render()
#4 /provision/htdocs/api/dhtmlxConnector/samples/treegrid/02_dynamic_loading_connector.php(24): Connector->render_sql(‘SELECT * FROM b…’, ‘taskId’, ‘product,item,va…’)
#5 {main}
thrown in /provision/htdocs/api/dhtmlxConnector/codebase/db_common.php on line 965

#2 fails with:

PHP Fatal error: Uncaught exception ‘Exception’ with message ‘MySQL operation failed
Unknown column ‘’ in ‘where clause’’ in /provision/htdocs/api/dhtmlxConnector/codebase/db_common.php:965
Stack trace:
#0 /provision/htdocs/api/dhtmlxConnector/codebase/db_common.php(650): MySQLDBDataWrapper->query(‘SELECT * FROM …’)
#1 /provision/htdocs/api/dhtmlxConnector/codebase/base_connector.php(513): DBDataWrapper->select(Object(DataRequestConfig))
#2 /provision/htdocs/api/dhtmlxConnector/codebase/base_connector.php(496): Connector->get_resource()
#3 /provision/htdocs/api/dhtmlxConnector/codebase/base_connector.php(432): Connector->render()
#4 /provision/htdocs/api/dhtmlxConnector/samples/treegrid/02_dynamic_loading_connector.php(22): Connector->render_sql(‘SELECT * FROM d…’, ‘bu.taskId(taskI…’, ‘bu.product(prod…’)
#5 {main}
thrown in /provision/htdocs/api/dhtmlxConnector/codebase/db_common.php on line 965

#5

It seems I need to use the render_complex_sql instead. This one works:

$tree->render_complex_sql(“SELECT * FROM bulk_update bu WHERE user=‘john’”, “taskId”,“product,item,value”);

Thank you for taking the time to help out.

John

#6

Yikes, the render now works but I lose the tree hierarchy with the aforementioned render statement. Within the GUI all entries are listed sequentially and not in a tree format. Did I make a mistake in the render_complex_sql statement?

#7

render_complex_sql will not work for tree like data ( it will not work for data updates as well )

If you need such functionality and in same time are using complex sql - you can create a view in mysql DB based on your SQL and use it with render_table command

Also, if issue still actual - can you please enable debug mode for connectors and provide the log for the problematic operation.

docs.dhtmlx.com/doku.php?id=dhtm … tor:errors

#8

I ran the two render statements and provided the logged errors for each:

$tree->render_sql(“SELECT * FROM bulk_update bu WHERE bu.user=‘john’”, “taskId”,“product,item,value”);

====================================
Log started, 10/09/2013 09:09:25

SELECT * FROM bulk_update bu WHERE ( bu.user=‘john’) AND `` = ‘0’

!!!Uncaught Exception
Code: 0
Message: MySQL operation failed
Unknown column ‘’ in ‘where clause’

$tree->render_sql(“SELECT * FROM bulk_update bu WHERE bu.user=‘john’”, “bu.taskId(taskId)”,“bu.product(product),bu.item(item),bu.value(value)”);

====================================
Log started, 10/09/2013 09:09:52

SELECT * FROM bulk_update bu WHERE ( bu.user=‘john’) AND `` = ‘0’

!!!Uncaught Exception
Code: 0
Message: MySQL operation failed
Unknown column ‘’ in ‘where clause’

#9

After tinkering with this for a while I got it to work by using the following:

$tree->render_sql(“SELECT * FROM bulk_update WHERE user=‘john’”, “taskId”,“product,item,value”,’’,‘parentId’);

Thanks again for your interest in helping me out.
John

#10

When I save data there is token mismatch proble.how I resolve it

#11

Could you please, clarify the details of your problem.