Buy Now
Sign in

Syntax for "Read dat" (IN, NOT IN, subquery ...)

shiluba , November 30 2019, 05:06
S
shiluba 39
November 30 2019, 05:06 #390179
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')&nbsp; AND
&nbsp;&nbsp;&nbsp; data.country Not In (Select table.name From table)
&nbsp;&nbsp;&nbsp; ");
?>
Thanks for reading & for your help !
S
shiluba 39
November 30 2019, 15:02 #390186
Hi,
Here is an example of the condition type from CCv5 again, clearer and without the "&nbsp;":
<?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!
healyhatman 9
December 01 2019, 13:13 #390194
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.
Web developer at SkySpider.com.au - I DO NOT work for ChronoEngine!
Donations: paypal.me/healyhatman
Professional Work: kamron@skyspider.com.au
Custom CF / CC Plugins and Extensions: skyspider.com.au/store
S
shiluba 39
December 01 2019, 14:10 #390196
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 ?
healyhatman 9
December 03 2019, 11:56 #390215
 1
{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
Web developer at SkySpider.com.au - I DO NOT work for ChronoEngine!
Donations: paypal.me/healyhatman
Professional Work: kamron@skyspider.com.au
Custom CF / CC Plugins and Extensions: skyspider.com.au/store
S
shiluba 39
December 03 2019, 14:14 #390216
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 !