Buy Now
Sign in

Read data WHERE condition with "NOT IN"

shahabpk , January 12 2020, 16:41
S
shahabpk 49
January 12 2020, 16:41 #390685
Chronoform v.6.1.4
I need a chronoform dropdown field which populates data from a DB table with read_data2 action.
I tried to put a condition like
username  NOT IN  {var:read_data1.Data1.username}  
in WHERE condition fields of second read_data ( read_data2) where read_data1 is another array with already booked usernames which is place above read_data2. But it throws an error 1064.
What is the correct syntax to put a WHERE NOT In condition . ?
healyhatman 9
January 12 2020, 21:46 #390690
{var:read_data1.Data1.username} will output a string, and NOT IN looks for an array.
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
shahabpk 49
January 13 2020, 04:56 #390697
Then how can we pass an 1D array of result from another read data action to NOT IN?
healyhatman 9
January 13 2020, 05:04 #390698
EDITED
{var:read_data1.[n].Data1.username}
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
healyhatman 9
January 13 2020, 05:08 #390699
ALSO: if you're only returning one entry to filter against, why are you using NOT IN instead of != ?
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
shahabpk 49
January 13 2020, 05:32 #390700
Thank you healyhatman
But this is also not working. My read_data debug is given below. I want to use this result in the next read_data action as
username NOT IN read_data4.Data4.empid array
Array
(
[read_data4] => Array
(
[log] => Array
(
[0] => SELECT `Data4`.`empid` AS `Data4.empid` FROM `elibrary_leaves` AS `Data4` LIMIT 100;
)

[var] => Array
(
[0] => Array
(
[Data4] => Array
(
[empid] => 10000877
)

)

[1] => Array
(
[Data4] => Array
(
[empid] => 10007774
)

)

[2] => Array
(
[Data4] => Array
(
[empid] => 10002748
)

)

[3] => Array
(
[Data4] => Array
(
[empid] => 10003383
)

)

[4] => Array
(
[Data4] => Array
(
[empid] => 10003395
)

)

)

)

)
both {var:read_data4.0.Data4.empid} and {var:read_data4.[0].Data4.empid} in WHERE NOT IN clause shows error.
I was able to use php code action with array_coloun() function to return single column array and use it as {var:php1} in NOT IN field. can't we produce same result through read_data action?
healyhatman 9
January 13 2020, 11:33 #390703
Not [a number] but literally [n] as in the letter n
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
shahabpk 49
January 17 2020, 14:20 #390759
Thank you healyhatman,
It worked. Sorry for a late reply.
It works in my case but I noticed that it behaves differently when DISTINCT phrase is used to retrieve fields for the NOT IN array.