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
Centralize Knowledge. Transform Learning.
All-in-one knowledge management and training platform for your employees and customers.
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 #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.Kaizen #226: Using ZRC in Client Script
Hello everyone! Welcome to another week of Kaizen. In today's post, lets see what is ZRC (Zoho Request Client) and how we can use ZRC methods in Client Script to get inputs from a Salesperson and update the Lead status with a single button click. In thisKaizen #222 - Client Script Support for Notes Related List
Hello everyone! Welcome to another week of Kaizen. The final Kaizen post of the year 2025 is here! With the new Client Script support for the Notes Related List, you can validate, enrich, and manage notes across modules. In this post, we’ll explore howKaizen #217 - Actions APIs : Tasks
Welcome to another week of Kaizen! In last week's post we discussed Email Notifications APIs which act as the link between your Workflow automations and you. We have discussed how Zylker Cloud Services uses Email Notifications API in their custom dashboard.Kaizen #216 - Actions APIs : Email Notifications
Welcome to another week of Kaizen! For the last three weeks, we have been discussing Zylker's workflows. We successfully updated a dormant workflow, built a new one from the ground up and more. But our work is not finished—these automated processes are
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
You are not a part of any org. So Campaigns permission(s) will not be granted.
I have an issue while calling the access token by my email "kessam@axisapp.com" please give me suggestions for resolving this problem!Is it possible to use module field filters via URL parameters?
It would be really convenient if I could quickly link to a filter. For reference, this is the filter functionality I'm referring to: https://help.zoho.com/portal/en/kb/crm/customize-crm-account/advanced-filters/articles/advanced-filters For example: MyTransitioning FESCO Bill Project to Zoho Sheets and Integration Options
Hello Zoho Support, I'm considering transitioning my FESCO bill project from Google Sheets to Zoho Sheets and wanted to know if there are integration options to seamlessly migrate our existing work. You can view our platform here, any guidance would beCredit card
Coming from another software, is it possible here to typically have credit card payment off, but independently per customer, click a box (possibly on the invoice when I go to to send) allowing that 1 allowed customer credit card payment, NOT ALL of myLightbox Pop-up form
I would like to embed my form using the lightbox pop up. I don't want it to load automatically. I want it to load when some clicks the button. I can see this option, however when I use the "show pop-up launch button" on the website, the button automaticallyLightbox Pop-up form
I would like to embed my form using the lightbox pop up. I don't want it to load automatically. I want it to load when some clicks the button. I can see this option, however when I use the "show pop-up launch button" on the website, the button automaticallyConnecting Portals from different Zoho apps
Hi, I note that Zoho has functionality for customer portals for several of the Zoho apps, like CRM, Projects, Desk etc. Is there any way to connect these portals? It would be great if we could give our customers access to a portal in which they couldCustomer Management: #5 Never Let the Customer Slip
When Rahul started Knight's Watch Consulting, his focus was simple: deliver good work and keep clients happy. He offered one-time consulting projects, monthly advisory retainers and usage-based support for growing clients. Business was steady, and customersDeluge date time issue
The deluge function info zoho.currentdate.toString("MMM/YYYY") returns Dec 2026 instead of 2025Zoho Projects Android and iOS app update: Mobile device permission based on user profiles
Hello everyone! We have brought in support for mobile device permissions based on the user profiles which are configured in organization level. Administrators can now configure the permissions on the web app(projects.zoho.com) by following the steps mentionedtreatment for rehires
Hello, we are aware of the fact that Zoho People at the moment does not has a provision for rejoining exited employees. But is there any quick fix or hack to this? It will be appreciated a lot.Zoho Projects Android and iOS app update: Timesheet module is now renamed as 'Time Logs', delete option has been renamed to 'Trash'.
Hello everyone! We have now renamed the Timesheet module as Time Logs and the delete option as 'Trash' on the Zoho Projects Android and iOS app. Time Logs Android: Time Logs iOS: Trash option Android: Trash option iOS: Please update the app to the latestZoho Mail app update: Manage profile picture, Chinese (Traditional) language support
Hello everyone! In the latest version (v3.1.9) of the Zoho Mail app update, we have brought in support to manage profile picture. You can now set/ modify the profile picture within the app. To add a new profile picture, please follow the below steps:Reminders for Article Approval
Is there a way to send reminders for approvers to review articles and approve/deny them? I'm not seeing that option anywhere.Add Full-Screen Viewing for Quartz Recordings in the Client Interface
Hi Zoho Team, We would like to request an enhancement to the Zoho Quartz client interface when viewing submitted recordings. Current Limitation: When viewing a Quartz recording from the client (user) interface, there is currently no option to switch the2025 Recap: A Year to Remember | Zoho Inventory
Important Update : Pipedrive deprecated fields no longer supported in Zoho Analytics
Dear Pipedrive users, We would like to inform you about a recent update related to your Pipedrive integration with Zoho Analytics. The Pipedrive team has deprecated certain fields from their application. You can find more details in the official PipedriveProduct Updates in Zoho Workplace applications | November 2025
Hello Workplace Community, Let’s take a look at the new features and enhancements that went live across all Workplace applications this November. Zoho Mail Format comments easily using Slash Commands With Slash commands, you can easily format text, insertRight-Click Pipeline to Open in New Tab
Please add the ability to right-click on a pipeline to open it in a new tabAdjusting Physical Inventory
Not getting very far with support on this one, they say they are going to fix it but nothings happened since November. Please give this a thumbs up if you would like to see this feature or comment if you have some insight. Use Case: Inventory set to besync views to sheet
Im looking to sync my views aka reports in analytics to zoho sheets, when data is updated in analytics it also should be updated in sheets, till now zoho sheets only offer raw data connection and it is not enough as these reports are difficult to re-doHow to update the Status in a custom module?
Hi, I have a custom module "cm_payment_registry" in Billing, I am trying to change the status which is "Draft" with: array = {"custom_status":"Approved"}; zoho.billing.update("cm_payment_registry",organization.get("organization_id"), XXXXXXXXXXXXXX, array,"connectionname");Replace Zoho Invoice with QuickBooks
We are implementing Zoho FSM for a cleaning business in the US with 50+ field workers. This business has been using Quickbooks for accounting for decades and will not migrate to Zoho Books. A major issue in the integration is the US sales tax calculation.2025 Highlights: A Year of Steady Progress and Significant Developments
As we come to the end of 2025, let's take a moment to reflect on the significant progress and developments we've made to improve your travel and expense management. In the Spotlight Introducing Online Booking (US edition only - Early access) Enable onlineFunction #42: Show the actual rate of items on invoices
Hello everyone, and welcome back to our series! In Zoho Books, you have the ability to create Price Lists, wherein you can mark up and mark down the item rates by a specific percentage or set custom rates. Generally, when you apply a price list to anAbility to Set Text Direction for Individual Cells in Zoho Sheet
Dear Zoho Sheet Team, We hope you are doing well. We would like to request an enhancement in Zoho Sheet that allows users to set the text direction (right-to-left or left-to-right) for individual cells, similar to what is available in Google Sheets. UseWarehouse fast processing
Hey guys, would anyone be interested in something like the attached image ? If there's any interest I'd be willing to develop it further for others to use, it's much faster than using Zohos native solutions, it can part pack, pack in full, part ship,Can I create a CODE 128 custom field for my items in Zoho Inventory and then use it for generating Sales Orders?
Can anyone helps me, I don't want to use the SKU code for scanning my products. Because all my products have a CODE-128 label attached.Start/Stop Timmer in Chrome Extension
The chrome extension is great and allows you to do allot however one of the most common things employees working on projects need to do is track their time. Having an easy start/stop timer to track time would be great.Invalid collection string
I haven't changed anything in one of my functions. I'm trying to run it manually and suddenly "Invalid collection string" appears. My code has 6 lines and the error says that the error is on 7th line. Why? What does this error mean? Nothing has been changedZoho Directory 2025: New Features | Security Enhancements | Enriched UI
Hello everyone, Greetings from the Zoho Directory team! 2025 has been a highly successful year for Zoho Directory. We are delighted to introduce a fresh set of features, an enriched UI, and major product enhancements. These updates aim to deliver a smootherzoho people 5 report
How do I customize my report in Zoho People Report? I understand that I can get the results of multi-table queries through SQL join statements, but I don't know the relationship between each table. I tried to create a report using Attendance User ReportLeave Report Emailed Weekly
I am wondering if someone knows how to have a report generated either weekly or monthly or both for department heads and ownership of upcoming employee leave. For instance, it would be nice to get an emailed report on Friday for the upcoming week of whoZoho Flow Decision Continuing Despite Not Meeting Conditions
I have a picklist field called Lead Status in the leads module, with the following lead Statuses: New Lead Attempted Contact - 1 Attempted Contact - 2 Attempted Contact - 3 Attempted Contact - 4 Attempted Contact - 5 Attempted Contact - 6 Attempted ContactTip #55- Accessibility Controls in Zoho Assist: Exploring Vision Settings- 'Insider Insights'
As we approach the end of the year, it’s a good moment to reflect on how we can make our tools more inclusive and easier to use for everyone. Remote support often involves long hours in front of screens, varied lighting conditions, and users with differentZoho Recruit Slow and Freezing on all screens
We have had an issue with Zoho Recruit for weeks being extremely slow and at times freezing. We have 100 mega internet, and I went into each computer and updated the virtual memory so there is more available. Also restarted all computers daily. Still having the issues. Almost unable to work.Credit Management: #2 Configuring Right Payment Terms for Credit Control
Think about the last time you ordered something online and saw that little note at the checkout, "Pay on Delivery" or "Pay later". It's simple, but it actually sets the tone. As a business owner, you know exactly when payment is expected. Now, imagineDependent (Conditional) Fields in Zoho Bookings Forms
Hello Zoho Bookings Team, Greetings, We would like to request the ability to create dependent (conditional) fields in Zoho Bookings registration forms. Current Limitation: There is currently no way to make one field’s available options depend on the valueBug Report: Search fails to find existing notes after Evernote import
Hello, I recently migrated from Evernote (~2600 notes across 23 notebooks), but the search functionality is currently broken. The Issue: I can manually browse to a specific note and see it exists. However, when I type the exact or partial title of thatMarketing Tip #13: Win repeat customers with post-purchase emails
The relationship with your customer doesn’t end after the sale; that’s when it begins. A thoughtful post-purchase message shows customers you appreciate them, keeps your brand top of mind, and can even lead to another sale. You can thank them, ask forNext Page