Kaizen #98 - Query API using v5 PHP SDK

Kaizen #98 - Query API using v5 PHP SDK

Hello and welcome to another week of Kaizen!

In our previous posts, we have already covered in detail about Query API, when to use them and their supported fields and operators with examples. Please refer to COQL API [Part I] and COQL API [Part II] for more details. In this week's post, we will discuss in detail how to use Query API in Zoho CRM PHP SDK. Please note that this article holds true for PHP SDK supporting version 5 of Zoho CRM APIs.

COQL (CRM Object Query Language) is a powerful query language based on SQL syntax that allows users to write their own queries and fetch records using module API names and field API names. Use Query API when you want to query for a module's data and/or it's look up related data using various comparators, or for records that fall into a custom view without actually creating one, or to query for records from cross-modules (linked via lookup field). 

How to construct a simple Query?

  1. Begin with the SELECT clause
  2. Specify the fields you want to fetch.
  3. Use the FROM clause to specify the module you are querying.
  4. Use the WHERE clause to apply conditions and filter the results based on specific criteria.
  5. Specify the conditions.
  6. Utilize additional clauses such as GROUP BY, ORDER BY, LIMIT, and OFFSET to further customize the query results if needed.

For example,
SELECT Last_Name, First_Name, Mobile FROM Leads WHERE (Lead_Status = 'Not Contacted') ORDER BY Last_Name DESC LIMIT 5 OFFSET 10;

COQL API using PHP SDK

To make a query, use the following code snippet in your code.

// Instantiate QueryOperations class
$queryOperations = new QueryOperations();
// Create a new BodyWrapper instance to set the query
$bodyWrapper = new BodyWrapper();
// Define your COQL select query
$selectQuery = "give_your_select_query_here";
// Set the select query in the body wrapper
$bodyWrapper->setSelectQuery($selectQuery);
// Execute the query and get the response
$response = $queryOperations->getRecords($bodyWrapper);


Let us explore in detail with examples how to use COQL for various field types and operators.

I. Text and Picklist Fields

The supported operators for Text and Picklist fields are =, !=, like, not like, in, not in, is null, is not null.

Sample code using =, like, in operators
<?php

use com\zoho\crm\api\query\BodyWrapper;
use com\zoho\crm\api\query\QueryOperations;
require_once "vendor/autoload.php";

class GetRecords
{
    public static function initialize()
    {
        // add initialisation code
        // refer to this post for details and examples 
    }
    public static function getRecords1()
    {
        $queryOperations = new QueryOperations();
        $bodyWrapper = new BodyWrapper();
        $selectQuery =
            "SELECT First_Name, Last_Name FROM Leads WHERE (((Lead_Status = 'Attempted to Contact') and (Company like '%zylker%')) and Lead_Source in ('Employee Referral', 'Advertisement')) LIMIT 5 OFFSET 10";
        $bodyWrapper->setSelectQuery($selectQuery);
        $response = $queryOperations->getRecords($bodyWrapper);
        //Add your code to handle the response received in $response. To know more, refer to this sample code.
    }
}
GetRecords::initialize();
GetRecords::getRecords1();
?>


Please note that the like operator is used for pattern matching, and is used for starts_with, ends_with, and  contains operators. In this example query, LIKE operator with the pattern %zylker% is used to find records where the Company field contains the substring zylker anywhere within its value. The % symbol represents any number of characters (including none) before or after the pattern zylker. % is the only supported wildcard character for Zoho CRM Query APIs. 

II. Lookup Fields

The supported operators for Lookup fields are =, !=, in, not in, is null, is not null.

Sample Query using !=, is not, not in, is not null

<?php
use com\zoho\crm\api\query\BodyWrapper;
use com\zoho\crm\api\query\QueryOperations;
require_once "vendor/autoload.php";

class GetRecords
{
    public static function initialize()
    {
        // add initialisation code
         // refer to this post for details and examples 
    }
    public static function getRecords1()
    {
        $queryOperations = new QueryOperations();
        $bodyWrapper = new BodyWrapper();
       $selectQuery = "SELECT Last_Name, First_Name, Full_Name, Account_Name, Owner from Contacts where (((Account_Name.Account_Name != 'Zylker') and (Reporting_To is not null)) and Vendor_Name.Vendor_Name not in ('Skytech','SR'))";
        $bodyWrapper->setSelectQuery($selectQuery);
        $response = $queryOperations->getRecords($bodyWrapper);
         //Add your code to handle the response received in $response. To know more, refer to this sample code.
    }
}
GetRecords::initialize();
GetRecords::getRecords1();
?>

III. Date, DateTime, Number, Currency Fields

=, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null are the supported operators for these fields.

Sample Query using between, <, >, >=

<?php

use com\zoho\crm\api\query\BodyWrapper;
use com\zoho\crm\api\query\QueryOperations;
require_once "vendor/autoload.php";

class GetRecords
{
    public static function initialize()
    {
        // add initialisation code
         // refer to this post for details and examples 
    }
    public static function getRecords1()
    {
        $queryOperations = new QueryOperations();
        $bodyWrapper = new BodyWrapper();
       $selectQuery = "SELECT Deal_Name, Amount, Stage, Probability FROM Deals WHERE ((Closing_Date between '2023-01-01' and '2023-03-31') and (Probability < 99)) and ((Amount > 10000) OR (Exchange_Rate >= 5))";
        $bodyWrapper->setSelectQuery($selectQuery);
        $response = $queryOperations->getRecords($bodyWrapper);
         //Add your code to handle the response received in $response. To know more, refer to this sample code.
    }
}
GetRecords::initialize();
GetRecords::getRecords1();
?>


Note : 
Use the following formats for Date and DateTime fields.
Date : YYYY-MM-DD (eg: 2023-01-01)
DateTime : YYYY-MMDDTHH:mm:ss+/HH:mm (eg: 2023-05-03T20:39:19+05:30)

IV. Boolean

= is the only supported operator for Boolean Fields. You should provide the value as either true or false.

<?php

use com\zoho\crm\api\query\BodyWrapper;
use com\zoho\crm\api\query\QueryOperations;
require_once "vendor/autoload.php";

class GetRecords
{
    public static function initialize()
    {
        // add initialisation code
         // refer to this post for details and examples 
    }
    public static function getRecords1()
    {
        $queryOperations = new QueryOperations();
        $bodyWrapper = new BodyWrapper();
       $selectQuery = "select Product_Name, Product_Code from Products where Product_Active = 'true'";
        $bodyWrapper->setSelectQuery($selectQuery);
        $response = $queryOperations->getRecords($bodyWrapper);
        //Add your code to handle the response received in $response. To know more, refer to this sample code.
    }
}
GetRecords::initialize();
GetRecords::getRecords1();
?>

Aggregate Function Support 

Aggregate functions in COQL allow you to perform calculations on groups of records in a module. The available aggregate functions are SUM, MIN, MAX, AVG, and COUNT.
  • SUM is used to sum up the values of an aggregate field in a module.
  • MAX is used to find the largest value of a field in a module.
  • MIN is used to find the smallest value of a field in a module.]
  • AVG is used to find the average value of a field in a module.
  • COUNT is used to count the number of records in a module satisfying a certain criteria.
This feature allows users to perform complex queries on their data and get insights that would not be possible with simple SELECT queries. For example, you could use aggregate functions to find the total revenue generated by all leads in a month, or the average age of all customers in a region.

<?php

use com\zoho\crm\api\query\BodyWrapper;
use com\zoho\crm\api\query\QueryOperations;
require_once "vendor/autoload.php";

class GetRecords
{
    public static function initialize()
    {
        // add initialisation code
         // refer to this post for details and examples 
    }
    public static function getRecords1()
    {
        $queryOperations = new QueryOperations();
        $bodyWrapper = new BodyWrapper();
       $selectQuery = "SELECT MIN(Amount), MAX(Amount) FROM Deals WHERE Stage = 'Closed Won'";
        $bodyWrapper->setSelectQuery($selectQuery);
        $response = $queryOperations->getRecords($bodyWrapper);
        //Add your code to handle the response received in $response. To know more, refer to this sample code.
    }
}
GetRecords::initialize();
GetRecords::getRecords1();
?>

This query will return the minimum and maximum deal amounts for all deals that have been closed won.

In this guide, we've explored how to use the Query API within Zoho CRM using PHP SDK effectively. By employing COQL, you can create intricate queries to extract specific data from modules, apply various operators, and even utilize aggregate functions for deeper insights. Whether you're seeking lead details, complex filters, or aggregate calculations, Query API empowers you to interact with your CRM data seamlessly. 

If you have any queries, let us know the comments below, or send an email to support@zohocrm.com. We would love to hear from you. Your commitment to Kaizen drives us forward!!

Stay tuned for more useful posts in the series! 


    • Recent Topics

    • Quotes in Commerce?

      In Zoho Ecommerce, I need to be able to generate quotes, negotiate with customers, and then generate invoices. Currently, I plan to integrate Zoho CRM to generate quotes. After negotiation and confirmation, I will push the details to Zoho Ecommerce to

    • Zoho Commerce - Mobile Application

      Does Zoho Commerce have a mobile application for customers to place an order?

    • Register user through Phone Number by Generating OTP

      In zoho commerce , I am developing website on online food store Inilialy the user get verification code to their email for registering there account for login. But I need to login using phone number by generating OTP automatically rather than verification

    • Unable to change sales_order status form "not_invoiced" to "invoiced"

      I am automating process of creating of invoice from sales_orders by consolidated sales_orders of each customer and creating a single invoice per customer every month. I am doing this in workflow schedule custom function where i create invoice by getting

    • Custom Buttons for Mass Actions

      Hello everyone, We’ve just made Custom Buttons in Zoho Recruit even more powerful! You can now create Bulk Action Buttons that let you perform actions on multiple records at once, directly from the List View. What’s new? Until now, custom buttons were

    • Zoho Vault Passwords

      Is there a way to consume Zoho Vault Manager passwords using the API? Thanks in advance.

    • Is the ChatGPT Assistant integration capable of recognizing WhatsApp voice messages?

      I was wondering: if a visitor sends me a voice message on WhatsApp, would the assistant be able to transcribe it and reply to them?

    • Zoho Creator to Zoho CRM Images

      Right now, I am trying to setup a Notes form within Zoho Creator. This Notes will note the Note section under Accounts > Selected Account. Right now, I use Zoho Flow to push the notes and it works just fine, with text only. Images do not get sent (there

    • 【Zoho CRM】レポート機能のアップデート

      ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 今回は「Zoho CRM アップデート情報」の中から、レポート機能のアップデートをご紹介します。 目次 1. レポートのエクスポート時のレコードIDの表示について 2. 通貨項目の表示について 3. レポートの削除の監査ログへの反映について 1. レポートのエクスポート時のレコードIDの表示について これまで、レポートをエクスポートするとファイルにレコードIDが必ず含まれていました。レコードIDが識別子として役立つ場合もありますが、実際には多くの企業で参照されることはありません。

    • Translation in zoho bookings

      We cant translate zoho booking emails. The general text we can change. But what about text like: ""Here a link to join the meeting online:"" and "Add to Zoho Calendar" and "Add to Google Calendar"? No professional business have mixed languages. Its looking

    • Is there any way to bill one client in different currencies on different invoices?

      I have some customers who have their currency set as USD and most of their billing is done in USD.   However, from time to time I have a need to bill them in my base currency GBP for some specific invoices, but there seems to be no way of doing this that I can see. The only workaround that I can see is to create two client records for the same client, one for USD billing and one for GBP billing, but this is not an ideal situation. Is it likely that the (hopefully!) soon to arrive multi-currency support

    • API name for all fields in Zoho Project (Standard or custom)

      Hi! I struggle to find easily all API name of all field in Zoho Project to build my API rest with other services. We can find them very fast in CRM but not in PRoject.   Could you share a function to get the list of all API Name of any field of an App

    • Disappearing Mouse cursor in Zoho Mail / Windows 11 (Chrome + Edge)

      I'm seeing an issue when writing mails with the light theme with the mouse cursor being white and the document area also being white - making it nearly impossible to see the mouse cursor. I see the problem on Windows 11 under Chrome and Edge. (Yet to

    • Zoho Assist not rendering NinjaTrader chart properly

      Hi everyone. Just installed and testing Zoho Assist. I want to display my laptop' screen (Windows 11) on a monitor connected to my Mac mini. The laptop is running a stock trading program called NinjaTrader. Basically, when running, this program displays

    • Link(s) between Notes

      Hello Everyone, It would be great if links could be created between notes. Let's say we have 5 Notes A, B, C , D, E. I would like to be able to link Note A to Note B but not in other way, so no link appears in Note B linking to Note A. An so on, linking

    • Zoho Books | Product updates | August 2025

      Hello users, We’ve rolled out new features and enhancements in Zoho Books. From the right sidebar where you can manage all your widgets, to integrating Zoho Payments feeds in Zoho Books, explore the updates designed to enhance your bookkeeping experience.

    • Feature Request: Assign Documents to Already Entered Bills, Expenses, Invoices, etc.

      Hi Zoho Team, We are regular users of the Documents module in Zoho Books and appreciate its ability to keep financial records well-organized. However, we’ve noticed a limitation: There is no way to attach a document from the "Documents > Files" section

    • I don't see any WITHDRAWL transaction at all

      Hi I manually imported my bank statement to Zoho books today and I am a complete newbie. I have been reading the knowledgebase but unable to fix this. I only see "Uncategorized 91 DEPOSIT transactions". I don't see any WITHDRAWL transaction at all. Also,

    • Shared inbox unable to see replies

      Hi we are a small company me and someone else, we have a shared inbox for our sale@ and contact@ however we have this issue where by if i reply to an email or the other person reply to the email, it does not show it to them and therefore we end up replying

    • Kaizen #136 - Zoho CRM Widgets using ReactJS

      Hey there! Welcome back to yet another insightful post in our Kaizen series! In this post, let's explore how to use ReactJS for Zoho CRM widgets. We will utilize the sample widget from one of our previous posts - Geocoding Leads' Addresses in ZOHO CRM

    • 404 error at checkout

      Our customers are getting a 404 error at checkout. Anyone else with the same problem?

    • FONT Sizing in Notebook

      Hi Kishore - What is the status of adding font sizing to the application? I have several things that I have pasted directly into Notebook and the fonts are HUGE! I would like the ability to highlight them and reduce the font to a legible size. Nothing

    • Can managers Upload documents to their direct rapports?

      Admin employees have the ability to upload documents to employees' files; however, managers do not have add/manage button - is it possible for managers to upload their direct reports' documents, such as absence documents or 121 documents. Is there something

    • Leave balance display for next year

      Is there a way to not have a rollover or not limit the leave balance depending on the date. For example an employee has 10 days leave balance and wants to apply for January leave in December. They cant because the rollover doesnt show the leave balance

    • Please add an “Auto-Apply Unused Credits” toggle

      Hello — please add a simple org-level option to automatically apply unused credits (credit notes, excess payments, retainers) to new invoices and/or bills. An ON/OFF toggle with choices “invoices”, “bills”, or “both” would save lots of manual work for

    • Zoho Books not working/loading

      Hi! I haven't been able to access/load Zoho Books for the past hours. I get a time out (and it is not due to my internet connection). Could you please check this asap? Thank you!

    • Custom Fields with Data Types for Expense and Payments Received in Zoho Books

      Hi all, We are glad to present to you, the option to create Custom Fields for the Expense and Payments received modules in Zoho Books. This also comes with an icing on top of it - Yes, the custom fields can now be created with different data types. Types like Text, Number, Decimal, Amount, Auto Number and Check Box are supported as of now. Rush to the gear icon at the top right corner, select 'More Settings', choose 'Preferences' in the left pane. Click the Expense/Payment preferences where you can

    • Zoho Cliq - Incident alert (Server outage - IN DC) | August 28

      We've received server down alerts and are currently investigating the issue (IN DC) to find the root cause. Our team is actively working to restore normal operations at the earliest. Status: Under investigation Start time: 09:44:21 AM IST Affected location:

    • Claude + MCP Server + Zoho CRM Integration – AI-Powered Sales Automation

      Hello Zoho Community 👋 I’m excited to share a recent integration we’ve worked on at OfficehubTech: ✅ Claude + MCP Server + Zoho CRM This integration connects Zoho CRM with Claude AI through our custom MCP Server, enabling intelligent AI-driven responses

    • How can I see content of system generated mails from zBooks?

      System generated mails for offers or invices appear in the mail tab of the designated customer. How can I view the content? It also doesn't appear in zMail sent folder.

    • Limitations on editing a message in Cliq

      Hi I've checked the documentations and there's no mention of how many times a message can be edited. When trying with code, I get various numbers such as ~1000 edits or so. Please mention if there's a limit on how many times one can change a message via

    • Narrative 10: Assignment Rules - Streamlining Ticket Management

      Behind the scenes of a successful ticketing system: BTS Series Narrative 10: Assignment Rules - Streamlining Ticket Management In the complex world of customer support, a flood of incoming tickets can hit the help desk in seconds. Businesses must do more

    • Free webinar! Digitize recruitment and onboarding with Zoho Sign and Zoho Recruit

      Hello, Tired of being buried in onboarding paperwork? With the integration between Zoho Sign and Zoho Recruit, a powerful applicant tracking system, you can digitize and streamline the entire recruitment and onboarding process, all from one platform.

    • Custom form - Duplicate Value Present

      I am new to Zoho People. I create a new form on Zoho People quite simple. A date (by default the current date) and a radio button with 3 options (Yes, No, Not applicable) I defined the date as ‘No duplicate’ as only one entry by date is allowed. I added:

    • Zoho API connection issues

      Hi, Today at around 1930 GMT our application started experiencing intermittent timeouts from the Zoho API. These intermittent timeouts are not enabling our app to work properly. The API connection was working just fine before. HTTPSConnectionPool(host='www.zohoapis.com',

    • Query Regarding our Partnership between AIC-JKLU and Zoho

      Dear Zoho Team, I am writing to raise a concern on behalf of AIC-JKLU, one of Zoho’s incubator partners. Recently, our startups have been facing difficulties while trying to get themselves onboarded on Zoho through our dedicated partner link. Unfortunately,

    • Disputed Purchase Invoices

      We have recently moved to Zoho Books from Sage. In Sage we were able to post a purchase/vendor invoice but mark it as on dispute. This would usually be a pricing query or if something was damaged. It would show in their ledger, so we could agree their

    • Getting events in the future

      Hi I am trying to get events in the future by calling this API Endpoint https://www.zohoapis.eu/crm/v8/Events?fields=Event_Title,Created_By,Created_Time,Start_DateTime But that gives me all events in the database. How do I make a query that returns all

    • Created Date/Invalid Fields

      Since Saturday we have suddenly had issues with our webhooks and data retrieval from CRM. Specifically how Created Date is handled. It appears there was some sort of change within CRM that broke a lot of our code that has been in place for several years.

    • add employees and customiz

      I want a Zoho software to track employees record. I logged in a demo so now I tried adding employee, first thing when I click on add employee it takes me to a page where it says add user maximum 10 users, my first doubt why users I don't want employees

    • Next Page