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?
- Begin with the SELECT clause
- Specify the fields you want to fetch.
- Use the FROM clause to specify the module you are querying.
- Use the WHERE clause to apply conditions and filter the results based on specific criteria.
- Specify the conditions.
- 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
New to Zoho Recruit?
Zoho Developer Community
New to Zoho LandingPage?
Zoho LandingPage Resources
New to Zoho Survey?
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Bigin?
Topic Participants
Anu Abraham
Sticky Posts
Kaizen #152 - Client Script Support for the new Canvas Record Forms
Hello everyone! Have you ever wanted to trigger actions on click of a canvas button, icon, or text mandatory forms in Create/Edit and Clone Pages? Have you ever wanted to control how elements behave on the new Canvas Record Forms? This can be achievedKaizen #142: How to Navigate to Another Page in Zoho CRM using Client Script
Hello everyone! Welcome back to another exciting Kaizen post. In this post, let us see how you can you navigate to different Pages using Client Script. In this Kaizen post, Need to Navigate to different Pages Client Script ZDKs related to navigation A.Kaizen #210 - Answering your Questions | Event Management System using ZDK CLI
Hello Everyone, Welcome back to yet another post in the Kaizen Series! As you already may know, for the Kaizen #200 milestone, we asked for your feedback and many of you suggested topics for us to discuss. We have been writing on these topics over theKaizen #197: Frequently Asked Questions on GraphQL APIs
🎊 Nearing 200th Kaizen Post – We want to hear from you! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.Kaizen #198: Using Client Script for Custom Validation in Blueprint
Nearing 200th Kaizen Post – 1 More to the Big Two-Oh-Oh! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.
New to Zoho TeamInbox?
Zoho TeamInbox 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
Design. Discuss. Deliver.
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.
Zoho CRM コンテンツ
-
オンラインヘルプ
-
Webセミナー
-
機能活用動画
-
よくある質問
-
Ebook
-
-
Zoho Campaigns
- Zoho サービスのWebセミナー
その他のサービス コンテンツ
Nederlandse Hulpbronnen
ご検討中の方
Recent Topics
Display Client Name in Zoho Creator Client Portal Dashboard
Hello Zoho Creator Team, We hope you are doing well. Zoho Creator recently introduced the option to set a client’s display name in the Client Portal settings, which is very helpful for providing a personalized portal experience. However, there is currentlyMeet Canvas' Grid component: Your easiest way to build responsive record templates
Visual design can be exciting—until you're knee-deep in the details. Whether it's aligning text boxes to prevent overlaps, fixing negative space, or simply making sure the right data stands out, just ironing out inconsistencies takes a lot of moving parts.Best way to share/download presentation files in Zoho without losing formatting?
Hello Zoho Community, I often work with PPT/PDF files in Zoho Docs and share them with colleagues. While PDFs usually give a direct download option, I’ve noticed that PPT/PPTX files sometimes only open in the viewer without a clear download link. Is thereWorkflow Failure - Notifications
Good afternoon, I have just experienced an error whereby a Workflow failed, for a reason currently unknown. The problem is that one of my users had to flag this manually (thankfully he's very thorough) and this otherwise would have flown under the radar.Introducing Bin Locations In Zoho Inventory
Hello users, We are excited to let you know that your wait for the Bin Locations feature has now come to an end! Yes, you heard us right! We are here to introduce the much-awaited Bin Locations now in Zoho Inventory. But before we dive into the featureError "Invalid client task found corresponding properties" only when triggered from workflow ?
Hi All, I am facing an error message I never encountered previously: Error in executing On Add - On Load script Error in executing thisapp.get_all_projects_api_call function. Line:(2) Error in executing thisapp.getAccessTokenFromRefreshToken function.How to interpret Campaign report statistics - definitions/explanation
I am trying to make sure I understand the Campaign report correctly Do you have a list of definitions for: Delivered - it has reached the recipient's inbox Campaign reach - is this the number that have opened the campaign email? Unique Opens Clicks/OpenSubforms in Stateless Forms
Stateless Forms are very useful, but we can't build complex data entry pages without Subforms. Can this be implemented? This would make Stateless Forms truly powerful.Feature request - pin or flag note
Hi, It would be great if you could either pin or flag one or more notes so that they remain visible when there are a bunch of notes and some get hidden in the list. Sometimes you are looking for a particular name that gets lost in a bunch of less importantSchedule a Call by Date and Time when a specific lead status is selected
Hi Wanting to create a workflow where a call can be scheduled by date & time when a specific lead status is selected. Can only currently set the date by Due Date - Trigger Date - Plus 'x' day(s) ThanksZoho CRM's Copy Customization functionality now works across DCs, for customized accounts, and more
Availability This feature is available in Standard, Professional, Enterprise, and Ultimate editions. These changes have been rolled out to all users in all DCs. For more information, please refer to these help documents: Overview | Instructions HelloHow can I bold text on Zoho Forms submit buttons?
In the old theme builder, I could bold the text on a form's submit button. With the new theme builder, I can only change the text of the header or fields in the form, and not the button itself.5名限定 課題解決型ワークショップイベント Zoho ワークアウト開催のお知らせ (10/31)
ユーザーの皆さま、こんにちは。Zoho ユーザーコミュニティチームの中野です。 10月開催のZoho ワークアウトについてお知らせします。 今回はZoomにて、オンライン開催します。 参加登録はこちら（無料）：https://us02web.zoom.us/meeting/register/BGYTysOnSqa9LA9eY2IKww ━━━━━━━━━━━━━━━━━━━━━━━━ Zoho ワークアウトとは？ Zoho ユーザー同士で交流しながら、サービスに関する疑問や不明点の解消を目的としたHiding Pre-defined Views
You can enhance Zoho with custom views - but you cannot hide the pre-defined views. Most users focus on 4 or 5 views. Right now for EVERY user EVERY time they want to move to one of their 4 or 5 views - they have to scroll down past a long list of pre-defined views - we don't use ANY of the pre-defined views. Adding the feature to allow a predefined view to be hidden completely or only visible to certain users would be a big improvement in usability for Zoho CRM. This feature is already availableRich-text fields in Zoho CRM
Hello everyone, We're thrilled to announce an important enhancement that will significantly enhance the readability and formatting capabilities of your information: rich text options for multi-line fields. With this update, you can now enjoy a more versatile[Integration Edition] Deluge Learning Series – Third-Party Integrations with Deluge | October 2025
We’re excited to continue this three-month edition of the Deluge Learning Series: Session 1 – Integrating Zoho Apps with Deluge Using Built-In Integration Tasks Session 2 – Integrating Zoho Apps with Deluge Using invokeURL and invokeAPI Session 3 – Third-PartyFeature Request: Tag, Search, and Report on Individual Private Comments
Zoho Desk Team, First, I want to say that we are extensive users of Zoho Desk and it is a core part of our support operations. We rely heavily on the private comments feature for internal communication, knowledge sharing, and maintaining a clear historyZoho CRM Community Digest - August 2025 | Part 2
Hello Everyone! Here's a quick round-up of handy tips, clever workarounds, and product updates from late August. Product Updates: Meeting Enhancements in Zoho CRM! Track whether a meeting is online, in-office, or at the client’s location using the newAssistance Needed: Ticket Status Not Updating and Sorting by Last Customer Reply in Zoho Desk
Hello, I’m facing two issues in Zoho Desk that I’d like your guidance on: Ticket Status Not Updating: When a customer replies to a ticket, the status does not change to Reopened. Instead, it remains in Waiting on Customer, even after the customer’s responseRound robin
Hi, I'm trying to set up a round robin to automatically distribute tickets between agents in my team but only those tickets that are not otherwise distributed by other workflows or direct assignments. Is that possible and if so which criteria should ITime Entry Notifications
Hi All - I have support staff who place notes of their work in the time entry section of Zoho Desk. Is there a specific workflow or setting I need to enable to have the ticket holder updated via email when an entry is saved?Change text in help desk
Hi, Please let me know how can i change the this text, see screenshot.Customer happiness customisation
Hi, I was wondering if it's possible to somehow customise to whom and when customer happiness request is sent? Can you enable it only for selected tickets for example based on workflow or any other criteria (customer name, tag or anything else)? AlsoSign in process is beyond stupid. I'd rather plug my phone into USB and copy files than sign in to this POS.
792 clicks and fields to fill in just to get into a 3rd rate app is too stupid for me.No Zoho Support Response in Months
I want to say that I love Zoho FSM, Books, and CRM, but the support is scary bad. About 2-3 months ago, I emailed Zoho FSM support with two specific issues I needed help with, and I have still not received one single response. I also submitted a supportHas anyone built a custom AI support agent inside Zoho (SalesIQ/Zobot)?
Hi all, I’ve been experimenting with building my own AI support assistant and wanted to see if anyone here has tackled something similar within Zoho. Right now, I’ve set up a Retrieval-Augmented Generation (RAG) pipeline outside of Zoho using FAISS. ItNull
If a result is null how do I make it so that a custom error pops up instead of the default error? Can someone write an example as if I were using the script builder?Format a "return" message
Good day, Is there a way to format the message of the "return" function in CRM deluge script?Change Last Name to not required in Leads
I would like to upload 500 target companies as leads but I don't yet have contact people for them. Can you enable the option for me to turn this requirement off to need a Second Name? Moderation update (10-Jun-23): As we explore potential solutions forHaving Trouble Opening The Candidate Portal
Recently am having trouble opening the Candidate Portal. It keeps loading but cannot display any widgets. Tried Safari, Chrome and Edge. Non of them work. Please solve the problem ASAP.Tip of the Week - Spot Risky Sales with Conditional Formatting
In Zoho Analytics, small tweaks can lead to big insights. One such feature is Conditional formatting based on other columns, your key to instantly spotting where sales success is overshadowed by product returns. Our tip this week shows you how to applyHow do I duplicate / copy a campaign
I am evaluation Zoho Campaigns and sent an email to one of my personal email accounts from Zoho Campaigns. It went into my junk file in Hotmail. I want to duplicate that same (1-page) campaign and send it to another of my personal accounts to try toFor a Bill, can "Pay via Check" - Can we also "Pay via Check" when documenting refund for payment?
We really like when we have a Bill in Zoho Books that we can record payment by selecting "Pay via Check" and that will cut the check for us from Zoho Books. However, when we have to write checks as a result of invoice overpayment, we can not select "PayCreator Change History: Ways to improve
Hi Everyone, Recently been working in developing this change history(an idea from Zoho Forms) - unlike forms that you can this with a click but using Creator, we can use "old" keyword. The concept I come up with is to put the result in a table however,Suitability of Zoho One (Single User License) for Multi-State GST Compliance & Cost Analysis
Hello Zoho Team, I am an e-commerce business owner selling on platforms like Amazon, Flipkart, and Meesho, and I'm currently using their fulfillment warehouses. I have two GSTIN registrations and am planning to register for an additional 2-3 to expandadd 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 employeesInactive User Auto Response
We use Zoho One, and we have a couple employees that are no longer with us, but people are still attempting to email them. I'd like an autoresponder to let them no the person is no longer here, and how they can reach us going forward. I saw a similarZoho People Attendance Module - Split Shifts
Hello, Our business employs a number of college students who often split their workdays between work hours and class schedules, resulting in split shifts. For example: Employee 1 works 9am to 11:30am and then leaves for class, returning to work at 3pmZoho One subscription
we have got Zoho CRM for 3 users and we paid for the app , and we have still 9 months to go , but if we join Zoho one , does it mean the payment for that subscription would be a waste? Please help. Thanks.How Zoho CRM Can Enhance Your Faucets Accessories Business Operations?
In today's competitive market, having a reliable CRM system is crucial for managing customer relationships and streamlining business operations. For businesses in the faucets accessories niche, Zoho CRM offers powerful tools to help manage everythingNext Page