I got an sql query:
$vtiger->query('*', 'Products', "(productname LIKE '%$name%' OR description LIKE '%$name%') AND cf_1007 = 'Ready'", null, 50, null);
(using https://github.com/sumocoders/vtiger-api). It has an WHERE clause. How to make it right? (current query throws exception). I need to take all rows where productname or description contains %name% and where cell cf_1007 = "Ready".
UPD: answer from vTiger API (forgot to add it here)
{"success":false,"error":{"code":"QUERY_SYNTAX_ERROR","message":"Syntax Error on line 1: token '(' Unexpected PARENOPEN((), expected one of: COLUMNNAME"}}
There's a syntax error due to the first (.
Try removing it, changing
(A OR B) AND C
for
A AND C OR B AND C
I dug deeper regarding this issue and found the following: The API you're using sends a request to the VTIGER web service. There's nothing wrong in the API and your usage seems to be fine. So, next station would be to check the webservice documentations.
Then I came across this page and found the following:
SELECT * | <column_list> | <count(*)> FROM <object> [WHERE
<conditionals>] [ORDER BY <column_list>] [LIMIT [<m>, ] <n>]
Notice the description of the part:
<condition_operations>or<in_clauses>or<like_clauses>separated by ‘and’ or ‘or’ operators these are processed from left to right. The are no grouping that is bracket operators.
So it seems that you can't use the (field1=val1 OR field2=val2) AND field3=val3 format. You should use field1=val1 OR field2=val AND field3=val3.