Requete SQL

Hello Aicha I have a field: first name (F166) and a field: Last name (F167)
I would like to combine these 2 fields in a "Select sql" type field but I can't find the right syntax the query only displays the first name
Here is the SQL-Select-Statement query :
SELECT id as value,  F166 as label, F166 as value,  F167 as label, F167 as value FROM `aspt_visforms_6`  ORDER BY F166

Can you help me ?
cordially
Alain

Hello Alain,

please try this:
select id as value, concat(F166, ' ', F167) as label from aspt_visforms_6 order by F166;

and listen to:
www.w3schools.com/sql/func_mysql_concat.asp

Remarks:
The back ticks ` are only necessary when using reserved words.
The characters upper case is optional and good for readability in more or less large statements;

Best regards, Ingmar

Hello Ingmar
Here is a new request :SELECT CONCAT_WS('F166','F167') as 'F283' FROM aspt_visforms_6

When I click on the test button the message success: found 28 entries appears.
But when I go to the list field of my form, it is empty
Avez vous une solution ?

Best regards
Alain

Hello Alain,

The function CONCAT_WS() fits your needs perfectly as to join two or more strings with a separator!
The message 'success: found 28 entries' does tell you that you have a valid select statement which returns a record set having 28 entries.

The test is not looking for this:
But the SQL statement SQL statement must return a value and a label (Visforms Documentation).
This is why the list remains empty.
www.vi-solutions.de/en/documentations/do...m-database-using-sql

Please re-try something like this:
SELECT id as value, CONCAT_WS('F166','F167') as label FROM aspt_visforms_6

This SQL statement gives you a listbox filled with options, which have the recordset id as option values and the 'string-concatenation' (names) as option labels (or visible text).
The option value (the recordset id of the selected name) get saved to the database into the SQL listbox field.

Or re-try something like this:
SELECT CONCAT_WS('F166','F167') as value, CONCAT_WS('F166','F167') as label FROM aspt_visforms_6

This SQL statement gives you a listbox filled with options, which have the  'string-concatenation' (names) as option values and as option labels (or visible text).
The option value (the 'string-concatenation' of the two names) get saved to the database in the SQL listbox field.

As a general note I have to tell you, that some decent experience in writing/understanding SQL select statement is quite helpful.
Nothing else or special (apart from must return a value and a label) goes on here.
Must return a value and a label means, that the statement as to declare the two resulting parts:
'something' as value
'something' as label

Best regards, Ingmar

select id as value, concat(F166, ' ', F167) as label from aspt_visforms_6 order by F166;

it works perfectly Thank you
Best regards