on the sample 02_customization - 18 Combo select from db, is it possible to display multiple fields in the combo-box. i’m going to have situation where the display values will be the same, so the user wont be able to distinguish which is which.
can we do this instead:
you can either specify sql query in connector constructor, or format output labels manually
docs.dhtmlx.com/connector__php__ … #rendersql
e.g. (SQLite syntax)
$combo->render_sql("SELECT item_id, (item_nm || ', ' || item_id) as label FROM Countries","item_id","label");
thanks for the advice, so I tried with mySQL and it seems it does not like the AS object in the where clause…
$combo->render_sql("select concat(FirstName,', ',LastName) as label from clients", "Num", "label");
In DB Common the sql statment created:
"SELECT concat(FirstName,', ',LastName) as mylabel FROM clients WHERE ( Num=1) AND `mylabel` LIKE 'b%' AND `UserId` = '-1' LIMIT 0,50"
"unknown column ‘mylabel’ in where clause.
any insight appreciated…
edit… yes it should read mylabel in first the statement… i changed the name to see if that was the issue…
ok… so according to mySQL…
column_alias can be used in an ORDER BY clause, but it cannot be used in a WHERE, GROUP BY, or HAVING clause.
… crap is there a workaround to display multiple fields in the lightbox combobox?
changed statement to:
$combo->render_sql("select concat(LastName,', ',Firstname) AS mylabel FROM clients", "Num", "LastName", "FirstName", "mylabel");
now there is no error and it appears the combo box gets filled… but there’s no text displayed, you can see a blue line and actually scroll down and select something… it returns blank in the display field, but looks like a value was selected.???
render_sql expects following params:
$combo->render_sql("sql query", "value column", "label column");
In your sql you select only the ‘mylabel’ value, the columns listed in parameters are not reteived by your query.
Probably this code should work (suppose the value column named ‘Num’)
$combo->render_sql("SELECT `Num` as `key`, CONCAT(LastName,', ',Firstname) as label FROM clients","key","label");
thanks for the help.
the corrected syntax still didn’t work, I looked up the mySQL select syntax and apparently, it does not support a column alias in a WHERE statement.
so, i just added a “displayName” field that i concat the display info and use that in the render_table statement.
thanks for you help,
This code realy did’t work!!! can you fix this and give good decition for us?)
The syntax of the mentioned code is correct. Could you please, clarify your problem.