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! 


    Access your files securely from anywhere






                    Zoho Campaigns Resources




                          Zoho Developer Community



                              Bootcamp
                              Community
                              Pricing
                              Webinars
                              What's New
                              Integrations
                              Contact Support
                              Contact Sales
                                Zoho Survey Resources
                                  Features
                                  Survey Templates
                                  Integrations
                                  Community
                                  iOS Mobile Surveys
                                  Pricing
                                  Android Mobile Surveys
                                  Insurvey Blogs
                                  Insight Blogs
                                  Tips & Tricks





                                              Latest Feature Updates

                                                • Desk Community Learning Series


                                                • Digest


                                                • Functions


                                                • Meetups


                                                • Kbase


                                                • Resources


                                                • Glossary


                                                • Desk Marketplace


                                                • MVP Corner


                                                • Word of the Day


                                                • Ask the Experts




                                                    Zoho SalesIQ Resources


                                                          Manage your brands on social media
                                                          Access Zoho Social

                                                            Zoho Pagesense Resources


                                                                Zoho TeamInbox Resources

                                                                  Zoho DataPrep Resources


                                                                    Zoho CRM Plus Resources

                                                                      Zoho Books Resources


                                                                        Zoho Subscriptions Resources

                                                                          Zoho Projects Resources


                                                                            Zoho Sprints Resources


                                                                              Qntrl Resources


                                                                                Zoho Creator Resources



                                                                                    Zoho CRM Resources

                                                                                    • CRM Community Learning Series

                                                                                      CRM Community Learning Series


                                                                                    • Kaizen

                                                                                      Kaizen

                                                                                    • Functions

                                                                                      Functions

                                                                                    • Meetups

                                                                                      Meetups

                                                                                    • Kbase

                                                                                      Kbase

                                                                                    • Resources

                                                                                      Resources

                                                                                    • Digest

                                                                                      Digest

                                                                                    • CRM Marketplace

                                                                                      CRM Marketplace

                                                                                    • MVP Corner

                                                                                      MVP Corner







                                                                                        Design. Discuss. Deliver.

                                                                                        Create visually engaging stories with Zoho Show.

                                                                                        Get Started Now


                                                                                          Zoho Show Resources

                                                                                            Zoho Writer

                                                                                            Get Started. Write Away!

                                                                                            Writer is a powerful online word processor, designed for collaborative work.

                                                                                              Zoho CRM コンテンツ






                                                                                                Nederlandse Hulpbronnen


                                                                                                    ご検討中の方




                                                                                                          • Recent Topics

                                                                                                          • Putting Watermark on Zoho Sheet

                                                                                                            Can this be done?

                                                                                                          • Validation function not preventing candidates under 18 or over 30 from submitting the web form

                                                                                                            Hello everyone, I’m trying to create a validation rule for the Candidate Webform in Zoho Recruit. I added a custom field called “Date of Birth”, and I want to make sure that candidates cannot submit the form unless their age is between 18 and 30 years.

                                                                                                          • Report to know the history of certain Tickets on Desk

                                                                                                            Hi there guys, As the title implies we're wondering if there's any way to get some kind of Report that allows us to check the History of various Tickets at the same time since as of today if we want to know that we have to check them 1 by 1 which is not

                                                                                                          • Advanced Usage Billing: Prepaid with Drawdown

                                                                                                            Picture yourself at your favourite coffee shop, Bean & Brew. You come by every morning for your usual cappuccino, and occasionally you get an extra cold brew and a muffin or two in the afternoon. Interestingly, Bean & Brew has a new idea of offering a

                                                                                                          • Zoho Commerce in multiple languages

                                                                                                            When will you be able to offer Zoho Commerce in more languages? We sell in multiple markets and want to be able to offer a local version of our webshop. What does the roadmap look like?

                                                                                                          • Urgent Zoho Creator down!!!???

                                                                                                            Now my zoho creator faced this issue. Anyone has idea? Urgent!!!

                                                                                                          • The Social Wall: September 2025

                                                                                                            Hello everyone, As we step into the fall season, some major updates are on the horizon. Meanwhile, here are the exciting updates we rolled out this September. Approvals in iOS Managing approvals just got more seamless on mobile. With this update, the

                                                                                                          • Introducing Detailed View for Candidates in Vendor Portal

                                                                                                            We’ve added a new Details sub-tab inside the Vendor Portal to help vendors easily view complete candidate information after submission. With this update, vendors can now access all candidate details, from personal information to associated job openings,

                                                                                                          • Zoho One Down

                                                                                                            Zoho Team, Checking if when the services up - currently Zoho One is down

                                                                                                          • How can I track which zoho users are actively using Zoho CRM

                                                                                                            I have several licenses of Zoho CRM. We now need to add a new user. I could purchase a new license, but before I do, I would like to see if any of our existing users are not actively using the license assigned to them. How can I determine the activity

                                                                                                          • Access to Detail View From HTML Snippet

                                                                                                            Zoho Creator displays a detail view that slides out from the right onClick of a record in a report. Am I able to access that detail view from an html snippet, e.g. click a record in a list and display the detail view? The zc_LoadIn dialog is a bit clunky,

                                                                                                          • Billing Management: #10 Solving Common Mistakes in Billing

                                                                                                            Over the past few weeks, we have explored different facets of billing, from the simplicity of traditional one-time billing to the evolving landscape of subscriptions, retainers, and usage-based models. We've unpacked how billing isn't just about sending

                                                                                                          • 【開催間近 - 10/17】東京 ユーザー交流会 Vol.3 参加登録 受付中！(参加無料)

                                                                                                            ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 10/17(金)に、東京・新橋で「東京 ユーザー交流会 Vol.3」を開催します！ ZOHOLICSよりも小規模なイベントですので、「リアル開催はちょっと緊張する…」という方も、安心してご参加いただけます✨ 当日は、初公開の事例を2つご紹介予定です！ なお、セッション映像のアーカイブ配信は予定していないため、会場にお越しいただいた方だけが、登壇者へ直接質問したり、リアルな声を聞いたりできる貴重な機会となっています。 ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

                                                                                                          • Loading CSS Stylesheets into HTML Snippet

                                                                                                            Combining html/css into a single snippet can grow quite large for a UI that has a lot of functionality/styling. To keep things tidy, are we able to pull files into an html snippet using a <link> tag? If so, what are some best practices?

                                                                                                          • Notes Attachments

                                                                                                            Two things it would be nice to have the attachment size the same as the attachments sections and it would be nice to be able to attach links like you can in the attachments section. Thank you

                                                                                                          • Canvas: empty images

                                                                                                            Hello. If I add an image field like contact photo in a Canvas design, and the field is empty, there is an ugly placeholder in its place. This doesn't happen in the standard view. In the standard view, if the contact photo is empty, nothing appears in

                                                                                                          • Google enhanced conversions not working

                                                                                                            Hi guys, I've connected Zoho CRM through Google Ads interface with the goal to setup the enhanced conversion tracking in Google Ads. I have to Zoho related conversion goals which you can see in the images below: For the conversion goal above I've setup

                                                                                                          • MS Teams Meeting to Zoho CRM

                                                                                                            Has anyone figured out a good way to push MS Teams meeting info on a trigger of "meeting end" to Zoho CRM? We're looking for a way to take attendees of a meeting and meeting duration and push it into Zoho CRM after the meeting has ended. If I can just

                                                                                                          • Font Size 11 - Zoho CRM Email Templates

                                                                                                            Our company communicates with our vendors exclusively using Calibri Font Size 11, as this is the standard formatting for professional emails. Since the CRM only allows for the selection of font sizes 10 & 12, we have been unable to utilize the CRM email

                                                                                                          • Subforms in stateless forms

                                                                                                            I think the title says it all. We need to be able to add subforms to stateless forms. Currently the only workaround is to create a Form and delete each record upon submission of the form. I need to build an interface to update our inventory. Basically

                                                                                                          • Calling Function via REST API with API Key gives 401 using Zoho Developer

                                                                                                            Hi, I created a couple of functions using the one month trial of Enterprise edition, which I was able to call using the API Key method from Postman and from an external site. Now that my trial has expired, I have created the same functions in the Developer

                                                                                                          • How do I move a section or element from one page to another in the NEW Zoho Sites UI

                                                                                                            I have a section on my home page with numerous elements within it and I'd like to move the entire section to a different page on my site so I don't have to recreate it from scratch.  Is there a way for me to do that easily? I could use a quick answer on this please.

                                                                                                          • Zoho Projects app update: Global Web Tabs support

                                                                                                            Hello everyone! In the latest version(v3.10.10) of the Zoho Projects app update, we have brought in support for Global Web Tabs. You can now access the web tabs across all the projects from the Home module of the app. Please update the app to the latest

                                                                                                          • Export as MP4 or GIF

                                                                                                            Hi, Just wondering if there's a way to export/convert a presentation to an MP4 video file or even a GIF. One use case would be to use the animation functionality to create social media graphics/charts/gifs/videos. Thanks for a great tool... Rgds Jon

                                                                                                          • Page Layout- Horizontal Rule

                                                                                                            When editing the layout of, for instance, the Potentials page, is there a way to insert a horizontal rule or white space in between fields?  I'd like to keep a group of fields in the same Section, but would like to create some seperation in order to further group together certain fields within the Section.  If this is not possible, does anybody have any other suggestions on how to create this same effect?   Thank you!

                                                                                                          • Zoho Books will discontinue support for older browser versions soon

                                                                                                            Hello users, Starting from May 15, 2024, Zoho Books will no longer support the following browser versions: Browsers Version Restrictions Firefox Browser Versions older than 100 Google Chrome Versions older than 100 Microsoft Edge Versions older than 100

                                                                                                          • Zoho Projects - Q3 Updates | 2025

                                                                                                            Hello Users, The final quarter of the year 2025 has begun, and we at Zoho Projects are all set with a plan. New targets to achieve and new milestones to reach, influenced by the lasting imprint of the past quarter. 2025's Q3 saw some new features and

                                                                                                          • Zoho Sheet - Printing - Page Breaks and Printing Customization

                                                                                                            I think the title is descriptive enough in that I cannot find help documentation on a simple task of adding in page brakes for separating pages on print. Thanks

                                                                                                          • Issue with Trident exe file

                                                                                                            Hello Team, Exe Setup file It's showing harmful for user pc please check and do needful. this message for developer team. Thanks Bhargav Purohit

                                                                                                          • Different languages for users

                                                                                                            Hello, Do you plan to enable individual users to select their languages for interface? Currently language can be changed for everyone - it looks like a settings for a whole portal, which is not good when you are working internationally. Best regards,

                                                                                                          • Transaction Locking with the dynamic date

                                                                                                            Is it possible to dynamically update dates on transaction locking. We want to lock transaction x days from today

                                                                                                          • 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

                                                                                                          • Apply Vendor Credits Automatically

                                                                                                            We are bulk importing Vendor credits in Zoho Books!!! Is there a way to apply vendor credits automatically to the first UNPAID bill of the Vendor?

                                                                                                          • Apply Vendor Credit Automatically

                                                                                                            Hello!!! Is there a way where in we can apply vendor credits automatically on the FIRST OUTSTANDING BILL of the vendor?? We have lots of VENDOR CREDITS ISSUES mostly!!! Applying it manually is a pain for us. Would be great if we have a way to apply the

                                                                                                          • Need Easy Way to Update Item Prices in Bulk

                                                                                                            Hello Everyone, In Zoho Books, updating selling prices is taking too much time. Right now we have to either edit items one by one or do Excel export/import. It will be very useful if Zoho gives a simple option to: Select multiple items and update prices

                                                                                                          • Creator problem: Edit form View not displaying whole form

                                                                                                            I'm having problems with the form in my database. The edit view is not showing the whole form: This is how it looks in the normal view: As you can see, there is a whole section in the bottom right of the form which is not displaying in the Edit View. This means that I can't change or delete any of these fields... Anybody had a similar problem or know a solution? Any help would be appreciated.   Cheers,

                                                                                                          • Vendor Master Enhancements for Faster Purchase Entry

                                                                                                            I’d like to suggest a few features that will improve accuracy and speed during purchase voucher entry: Automated Item Tax Preference in Vendor Master Add an option to define item tax preference in the vendor master. Once set, this preference should automatically

                                                                                                          • Quick Item Search & Auto-suggestion for Invoices

                                                                                                            Hi Team, I am facing an issue while creating invoices in Zoho Books. Currently, I have to type the full item name in the correct sequence and spelling for it to appear. For example, my item name is: "Distemper Acri Silk Special White 10kg" If I type something

                                                                                                          • Function #53: Transaction Level Profitability for Invoices

                                                                                                            Hello everyone, and welcome back to our series! We have previously provided custom functions for calculating the profitability of a quote and a sales order. There may be instances where the invoice may differ from its corresponding quote or sales order.

                                                                                                          • Integrating Chatbot with Zoho Creator Application

                                                                                                            Is it possible to integrate a chatbot with a Zoho Creator application?

                                                                                                          • Next Page