Forums

Hi,
I am looking for the correct syntax for "IN" or "NOT IN" with "Read data". Most things I tried give back a « 1064 SQL syntax error ».

For example, let's say I want to do this basic query : 

SELECT id FROM data WHERE country IN ('USA' , 'France');

If I use a "Where conditions > Condition" with ..
Table field name : table.country
Condition : IN
... what can I type into the "Value" field ?

If it is simpler from "Where conditions > Rules list", what would be the syntax ? I tried many things like ...
data.country/in:(USA, FRANCE )
data.country/in:( `USA`, `FRANCE`)
simple quotes with escapement, double quotes, ... .etc. Each time, it seems like CCv6 treats it as a whole string (surrounded by simple quotes).

I managed to use the "IN" operator with a PHP array returned from another function. But I keep on hoping there is a simpler way to use "IN" than to create an extra function that needs to be (re)declared for each Event.
Also, what is the rules syntax for "NOT IN" ( data.id/not_in: ... , data.id/not in: ... ) ?
At last, how to use a subquery into a "Where conditions" ?
Sorry if it looks like topics mix, but I am just trying to migrate a connection from CCv5 in which all I had to enter under "Condition" was something very simple like :

<?php
return array( ": data.country In ('USA', 'FRANCE')  AND 
    data.country Not In (Select table.name From table)
    ");
?>

Thanks for reading & for your help !

Hi,
Here is an example of the condition type from CCv5 again, clearer and without the " ":

<?php
return array( ": data.continent In ('Asia', 'Europe') AND
data.country Not In (Select banned.country_name From banned)");
?>

I am still looking for such a simple way to use "IN", "NOT IN" and a subquery with CCv6.
Thank you in advance!

In the value field you would put what value you're checking against. So obviously you would want it to be an array, which would by the way be square brackets.

Thank you,
I replaced the parenthesis of the MySQL syntax with square brackets, but it still throws back a 1064 SQL syntax error.
I tried values such as [Asia, Europe] ... ['Asia', 'Europe'] ... ["Asia", "Europe"] ...
Is there a page/document somewhere summarizing all the syntax for ChronoConnectivity ?

{val:[1,2,3]}
​
I had forgotten about {val:} until I went back through the shortcodes reference I made available for free on my site at https://skyspider.com.au/store/shortcodes-reference

Indeed. It works with string arrays by using double-quotes, e.g :

data.country/in:{val:["USA","FRANCE"]}

This one saves me a PHP function. Thank you healyhatman. Very neat !