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
Text Message
When trying to sent a text message, it says its an error i should contact a zoho agentZoho POS is now available for Canadian retailers
Hey everyone, We're excited to introduce the all-new Canadian edition of Zoho POS, which helps retail businesses simplify and manage their end-to-end business operations. Start by signing up and exploring the 15-day free trial. Sign up now How does ZohoPayroll In Canada
Hi, When can we expect to have payroll in Canada with booksMass update to change or shift all project dates and keep project structure + shift all sub-tasks dates with main tasks
Most users would expect that if they change the start date of their project then that will be reflected inside the actual project and any project structure would be retained. Additionally if a task list with associated sub-tasks is moved, most users would expect that those sub-tasks would also be moved along with their parent task. This is not the case. For a total Project shift of dates: * the start and end date from > "Edit Project" page can have a radio button added to "shift all project tasksClosing Accounting Periods - Invoice/Posting dates
Hi, I have seen in another thread but I'm unsure on how the 'transaction locking' works with regards to new and old transactions. When producing monthly accounts if I close December 24 accounts on 8th Jan 25 will transaction locking prevent me from postingIn-person ZUG Meetups for Real Estate Professionals - US Q1 2026
The Real Estate Zoho User Group is going on a multi-city, in-person meetup tour across the US, and we’d love to see you there! These meetups are a great opportunity to: Connect with fellow real estate professionals using Zoho Share challenges and discoverZoho CRM custom fields not showing in zoho creator
Hi Team, I have created a Products form with Zoho CRM integration and connected it to Products module of CRM. But when I see the reports of Products in Zoho creator then I am not able to see custom fields of Products module. Only standard fields of ProductsSending email notifications based on language
Hello. I would like to know how we can bypass the default notifications (which are just in English) for when a ticket is created/replied to/closed, to be in other languages, based on the language field in the ticket? I can create other email templates,Zoho Error: This Operation has been restricted. Please contact support-as@zohocorp.com for further details
Hello There, l tried to verify my domain (florindagoreti.com.br) and its shows this error: This Operation has been restricted. Please contact support-as@zohocorp.com for further details. Screenshot Given Below - please check what went wrong. ThanksDownload a file from within a zoho creator widget
I have a widget running in Zoho Creator , it displays uploaded documents in a table file, and I have added a download link in the view. ( The widget is created with html, css and javascript). I do not succeed in getting the download working. Do I haveZoho / Outlook Calendar sync
The current Marketplace -> Microsoft -> Meetings integration needs 2 changes. 1. The current language for the Two-Way sync option should be changed. It currently states, "Sync both your Zoho CRM Calendar and Office 365 Calendar meetings with each other."Email content just contain 'OK' ,not what we expect
create campaign API URL: https://campaigns.zoho.com/api/v1.1/createCampaign req params: {'campaignname': 'General_Outreach_d0cfc415-43aa-4b96-bb09-558e76a3dda3_50_20251117_214806_660', 'from_email': 'admin@allinmedia.ai', 'subject': 'ALL IN MEDIA', 'list_details':Reopen ticket
Hello! Can I reopen a ticket just using the API ticket/sendReply ? What's the rules to do it? I'm trying but it doesn't reopen the ticket, it just send the replyAllow Admins to Transfer Ownership of Their Own Files & Folders
Hi Zoho WorkDrive Team, Hope you are doing well. We would like to request an important enhancement to the ownership-transfer functionality in Zoho WorkDrive, specifically regarding administrator capabilities. As administrators, we have the ability toTip#46: Capture accurate log hours
Hello everyone, Use the newly introduced timer settings that will streamline the usage of timers and help admins or workspace owners to manage the time entries of the workspace users better. Check out the below mentioned timer settings added to the TimesheetMoving to app-specific authentication for Google integrations
Hello everyone, We’re making an important change to how Google integrations work in our platform. Until now, we used a common Google project across Zoho to enable integrations like Google Drive, Calendar, and more. Going forward, we’ll be moving to anLost the ability to sort by ticket owner
Hi all, in the last week or so, we have lost the ability to sort tickets by Ticket Owner. Unlike the other columns which we can hover over and click on to sort, Ticket Owner is no longer clickable. Is it just us, or are other customers seeing this too?How Can i put a form in Zobot
Hi,how can i integrate a form which has a multiple options to choose from.the form should be opened or displayed by zobot after it meets a requirement in the conversation. Thanks in advance !Playback and Management Enhancements for Zoho Quartz Recordings
Hello Zoho Team, We hope you're all doing well. We would like to submit a feature request related to Zoho Quartz, the tool used to record and share browser sessions with Zoho Support. 🎯 Current Functionality As of now, Zoho Quartz allows users to recordZoho Analytics - Feature Request For Time Based Data Source Fetch
Hi Analytics Team, I have a client using Zoho CRM and they want a weekly report at 4:30pm every Friday, emailed to the sales team showing a pie chart of Closed Won Deals for that week. This is easy to achieve in Analytics but not so easy to ensure theWhich user's capacity is used for Shared Mailbox storage?
We use shared mailboxes at our company, and their size is increasing daily. Which user(s)'s total mailbox limit is being used up by this space?Inserting the current date / time
I'd like to use Zoho Notebook as a log - so it would be great to be able to insert the current date and time at the beginning (or end) of each my log/journal entries - or wherever I want. Something simple, like pressing a button/icon to insert it wherever my text cursor is. It could be formatted like "Mon Dec 11, 2017 - 11:43p".Campaign editor overrides href value
I've been trying to insert a deep link in an email campaign so recipients can directly open a native app on their device. My deep link looks something like "myapp://". The options to insert links in the campaign editor are limited to strict urls, emails,Bug Report and Suggestions for Improvement in Zoho Applications
Hi Zoho Team, I’d like to report a few bugs and improvement suggestions I’ve noticed while using Zoho products: Zoho Cliq Video Call: The camera sometimes turns off automatically during video calls. This seems to be a bug — please check and fix it. ZohoCanvas Flex Box containers should not require a width/height.
Flexbox containers are often used as organizational concepts, to ease re-flow on mobile etc. - I cannot use % for flexbox W or H - I cannot omit W or H This means that the content cannot dictate, and a Flexbox container cannot be used merely as an organizationalZoho Desk Android app update: Filter enhancement, Save Filters
Hello everyone! We have now enhanced the filter section on the ticket listing screen of Zoho Desk Android app as it is on the web(desk.zoho.com). Also, we have introduced an option to save filters for tickets on the mobile app. You can easily apply, rename,Very long loading times | bad performance [ZOHO DC: EU]
Hello there, we have recently noticed that the loading times have become extremely long when retrieving tickets and ticket details. For example, I open any ticket and get the following view. The subject and standard information (ticket owner, status etc.)Zia Profile Summary now supports six languages and responds faster
After numerous customer requests, we’ve expanded Zia Profile Summary to do more than just summarize resumes quickly. It can now summarize them in six different languages and deliver results even faster. What’s New 1. Extended language support Zia canIntroducing Zoho Show Android 3.0
Good ideas need the right space to grow. They deserve tools that move as fast as your thoughts and make creating feel effortless. And that is why we are bringing you Zoho Show Android 3.0! From a brand-new look and smoother navigation to improved collaborationProperly Capture Dropdowns & Hover Elements When Recording a Window/Tab
Hi Zoho WorkDrive Team, Hope you are doing great. We encountered a limitation when recording a selected window or browser tab: Certain UI elements, such as dropdown lists, hover menus, and overlays, are not captured unless we record the entire screen.Peppol integration zoho invoicefu
Hi, Belgium will require Peppol invoicing as of 2026. I found that this is being prepared for Zoho books, to be released in Sep 2025. Will Zoho Invoice get this functionality too? I like the Invoice app for my part-time side business as bike mechanicAllow Recording Only a Selected Portion of the Screen
Hi Zoho WorkDrive Team, Hope you are doing well. We would like to request a more flexible recording option in Zoho WorkDrive. At the moment, we can record either the entire screen or a specific window/tab — but not a custom selected portion of the screenRestricting Calendar View to Working Hours
Hi: I'm trying to implement a calendar which displays all of my customer appointments. Currently, the calendar shows all 24 hours of the day. Is there a way to restrict the hours to simply the times my business is open? Thanks!Add Camera Background Blur During Recording
Hi Zoho WorkDrive Team, Hope everything is well. We would like to request an enhancement to the video recording feature in Zoho WorkDrive. Currently, the camera preview displayed during a recording does not support background blur. This is an essentialAmazon FBA integration - Handle invoice reconciliation under different GSTINs,
Dear Zoho Community, Thank you for the clarification regarding the Amazon FBA integration, default warehouse, and GSTIN mapping in Zoho Inventory. I understand that Zoho currently supports only one warehouse for Amazon FBA, and that all FBA orders areWhatsapp Integration questions
Hi Questions: 1. Whatsapp messages are not reaching our CRM, is there a delay? 2. Is there any way to create a whatsapp catalog on a number that is integrated with ZOHO? 3. How do I configure my profile picture on a number that is integrated with ZOAllow Font Size 11 in Editors...
That is basic functionality...link mail.zoho.com/UnblockMe to unblock
Could you please unblock my account heloisa.bomfim@expansaodenegocios.comUnblocked me
Please can you help unblocked my account thanks materiauxjcbrunet2000@zohomail.comIntegrating External Portal Tickets Into Zoho Desk
A B2B travel company has a customer portal where clients can raise and submit tickets related to their bookings. These tickets need to be handled and managed in the backend through Zoho Desk. Ticket updates and replies from Zoho Desk should also be visibleNext Page