Syntax does not work

Dear Support Team,

We have created two visforms which we called A and B.

When I input some new contents in form A, the newly input contents could be found on the server,
But in form B, the newly input contents could not be recorgnized by "Select SQL".
The SQL-Select-Statement was used so far is as following:

SELECT DISTINCT `F85` as value,`F85` as label FROM `jos4_visforms_10` WHERE `F81` in OR `F82` in ORDER BY `F81`ASC, `F82`ASC

When I click "Test", there is an error message shows as following:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OR `F82` in ORDER BY `F81`ASC, `F82`ASC' at line 1

What we did is to update the Joomla to a MariaDB10.4 server.
Therefore could you please help us solve this problem at your earliest convenience as we need to provide these two visforms on a daily basis to our users.
Thank you so much and we are looking forward to your professional suggestions.

Hello umif,

quote from above: Important information for almost every question:

V1: Which Visforms version is running?
V2: Which Joomla version is running?
V3: Which PHP version is running?

The SQL statement mentioned is incorrect and contains two errors.

To quickly obtain more detailed information about possible errors, you can execute the SQL statement in a dedicated SQL editor.
Such as phpMyAdmin or another SQL development environment.
Visforms, of course, cannot perform this precise analysis.

SQL statement:


Error 1:
The list of values ​​is missing after the keyword 'in'.


Error 2:
The list of values ​​is missing after the keyword 'in'.


The SQL IN Operator:
www.w3schools.com/sql/sql_in.asp


Question:

It's possible that you're using a Visforms parameter in the SQL statement and something's wrong.
Does the SQL statement exactly match the SQL statement from the field configuration?

Best regards, Ingmar

Hi Ingmar, schrieb:

Hi Ingmar,
Thank you so much for your prompt feedback. Yesterday when I copied the  Syntax there was an issue, the complete SQL-Select-Statement is as followings:

When I clicked "Test", the error shows as below:

SELECT DISTINCT `F85` as value,`F85` as label FROM `jos4_visforms_10` WHERE `F81` in ${input:project-leader-list} OR `F82` in ${input:project-leader-list} ORDER BY `F81`ASC, `F82`ASC
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near
'OR `F82` in ORDER BY `F81`ASC, `F82`ASC' at line 1

The Version of Visform we use is: 4.4.3,
the Version of Joomla is: 5.1.4,
the PhpMyAdmin is: MariaDB-10.4

Looking forward to hearing from you for further support and help. Many thanks in advance.

Best,
Shuting

Hello Shuting,

Thanks for the version information and the original SQL statement, which, as expected, contains two Visforms placeholders.

Re: Error after pressing the test button.

The error after pressing the test button occurs because the SQL statement contains Visforms placeholders.
For simple testing with the test button directly in the form configuration, no Visforms placeholders for form fields must be included.

Everything else is described in the documentation.

The test button:
docs.joomla-5.visforms.vi-solutions.de/e...ist/#the-test-button

I can't say at this point whether your SQL statement works in the running form or not.
This is solely about the error when using the test button.

Kind regards, Ingmar