browsing selections of a multiselect input

[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.

[admin]

omid020,

I don't understand your question.

Steven

[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();

[omid020]

To remove the empty item:

Code: Select all

$('#elmentid').find('option[value=""]').remove();

Hi,
Thank you! it works :)

Hi,

Do you want to show the items in one column, separated by commas?

Yes, I do. How is it possible?

[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    

[omid020]

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 

[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

[omid020]

Yes, I have process_name field in process table.
Unfortunately new code does not return any value too!

Thanks.

[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:

[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.