Page 1 of 2
browsing selections of a multiselect input
Posted: Sun Apr 22, 2018 5:38 am
by omid020
I tried select:Multiselect input in my form. The selections come from a table of database (for example: product):
Code: Select all
SELECT
product.product_id,
product.product_name
FROM
product
Question: the first item of Multiselect is an empty row (value), how can I omit it?
Then I view these output in my browsed form (for example for 2 selecttions)as process_product_id field:
"5adb065f8879469","5adb0673b1fd06f"
To inquery name of products which have above product_id I've used this query and changed display field from
process_product_id to
product_name but it returns an empty browsed table:
Code: Select all
SELECT
process.*,
product.*
FROM
process
JOIN product ON process.process_product_id = product.product_id
It seems above query does not work for Multiselect; How can I return correspond items of Multiselect in a browsing table?
Thanks in advance.
Re: browsing selections of a multiselect input
Posted: Sun Apr 22, 2018 6:35 am
by admin
omid020,
I don't understand your question.
Steven
Re: browsing selections of a multiselect input
Posted: Sun Apr 22, 2018 8:23 am
by toms
Hi,
Do you want to show the items in one column, separated by commas?
To remove the empty item:
Code: Select all
$('#elmentid').find('option[value=""]').remove();
Re: browsing selections of a multiselect input
Posted: Sun Apr 22, 2018 8:16 pm
by omid020
toms wrote:
To remove the empty item:
Code: Select all
$('#elmentid').find('option[value=""]').remove();
Hi,
Thank you! it works :)
toms wrote:Hi,
Do you want to show the items in one column, separated by commas?
Yes, I do. How is it possible?
Re: browsing selections of a multiselect input
Posted: Sun Apr 22, 2018 9:17 pm
by toms
Try this:
Code: Select all
SELECT a.field1, a.field2 -- replace with fields from table product
GROUP_CONCAT(b.product_name ORDER BY b.product_id SEPARATOR ', ') AS product_name
FROM process a
INNER JOIN product b ON
FIND_IN_SET(b.product_id, REPLACE(REPLACE(REPLACE(a.process_product_id, '[', ''), ']', ''), '"', '')) > 0
GROUP BY a.field1, a.field2 -- replace with fields from table product
Re: browsing selections of a multiselect input
Posted: Mon Apr 23, 2018 4:52 am
by omid020
toms wrote:Try this:
Code: Select all
SELECT a.field1, a.field2 -- replace with fields from table product
GROUP_CONCAT(b.product_name ORDER BY b.product_id SEPARATOR ', ') AS product_name
FROM process a
INNER JOIN product b ON
FIND_IN_SET(b.product_id, REPLACE(REPLACE(REPLACE(a.process_product_id, '[', ''), ']', ''), '"', '')) > 0
GROUP BY a.field1, a.field2 -- replace with fields from table product
Confusing a little; I tried this but does not work:
Code: Select all
SELECT a.product_id, a.product_name -- replace with fields from table product
GROUP_CONCAT(b.product_name ORDER BY b.product_id SEPARATOR ', ') AS product_name
FROM process a
INNER JOIN product b ON
FIND_IN_SET(b.product_id, REPLACE(REPLACE(REPLACE(a.process_product_id, '[', ''), ']', ''), '"', '')) > 0
GROUP BY a.product_id, a.product_name -- replace with fields from table product
Re: browsing selections of a multiselect input
Posted: Mon Apr 23, 2018 7:47 am
by toms
There is at least 1 syntax error: no comma after a.product_name.
This works for me. Assuming that a field process_name exists. Otherwise, replace it:
Code: Select all
SELECT a.process_id,
a.process_name,
GROUP_CONCAT(b.product_name ORDER BY b.product_id SEPARATOR ', ') AS product_name
FROM process a
INNER JOIN product b ON FIND_IN_SET(b.product_id, REPLACE(REPLACE(REPLACE(a.process_product_id, '[', ''), ']', ''), '"', '')) > 0
GROUP BY a.process_id,
a.process_name
Re: browsing selections of a multiselect input
Posted: Mon Apr 23, 2018 5:01 pm
by omid020
Yes, I have process_name field in process table.
Unfortunately new code does not return any value too!
Thanks.
Re: browsing selections of a multiselect input
Posted: Mon Apr 23, 2018 6:47 pm
by toms
The underlying problem is that nuBuilder cannot process complex SQL queries. A workaround is to create a temporary table with PHP (or to create a VIEW)
The temporary table can be created in the "Before Browse" event:
Before_Browse_PHP.png
Code: Select all
$s = "CREATE TABLE #TABLE_ID# ";
$w = "
SELECT a.process_id as proc_id,
GROUP_CONCAT(b.product_name ORDER BY b.product_id SEPARATOR ', ') AS product
FROM process a
INNER JOIN product b ON FIND_IN_SET(b.product_id, REPLACE(REPLACE(REPLACE(a.process_product_id, '[', ''), ']', ''), '\"', '')) > 0
GROUP BY a.process_id
";
$t = nuRunQuery("$s$w");
The Browse SQL ist going to look like this:
browse_sql.png
Code: Select all
SELECT * FROM process JOIN #TABLE_ID# on proc_id = process.process_id
The Browse Form output then looks something like this:
Re: browsing selections of a multiselect input
Posted: Mon Apr 23, 2018 7:12 pm
by toms
#TABLE_ID# simply gets replaced by the name of the temporary table that is created in the PHP code.
Can you run nuCurrentProperties().browse_sql in the console while being in the Browse View?
Can you also take 2 screenshots, one of your PHP code and one of the browse SQL and fields.