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
Let us add Lookup fields in the Blueprint Transitions
We are unable to add Lookup Fields in the blueprint transitions in Zoho Desk, we wanted to make it a requirement for our workflow but since it's not available in the transition we cannot. The lookup field exists in the Layout: But it cannot be added/selectedZoho Projects API Scope for Issue Attachments
I try to download issue attachments via the API, unfortunately I always get the following error code when trying to download the file from the supplied URL: {"ERROR_MESSAGE":"INVALID_OAUTHSCOPE","ERROR_CODE":401} indicating an auth scope error. I've triedHow to fetch custom fields for time entries in Zoho Project API v3
In the previous Zoho Projects REST API, we were able to retrieve custom field details for time entries, including picklist options, using the endpoint: GET /restapi/portal/[PORTAL_ID]/timesheetcustomfields In the new Zoho Projects API v3, we tried usingZoho Survey Enhancements
We love Survey. We use it a ton. It needs some enhancements. Maybe some of these are already on the roadmap? API - this is crucial. We have some complex surveys that take place and need to update records, trigger other functions/automations, etc. I wouldImages Don't Display on Blog
Hi, I've just posted a new blog entry, but regardless of format (png or jpeg) Images that display in Preview mode do not display on any browser in the published post. I've not done anything different with this post than others regarding images, please advice. Thanks! Edit: I've discovered that moving the image files from a my sub directory folder "Pics for Blog" to the top level of the "Files" folder on my site allows the images to display live. This, even though my other blog posts are correctlyCheckbox Field Mapping Zoho Forms to Zoho Sign
I have an application that will be filled out via Forms that I am mapping into a PDF Filler on Zoho Sign. I need to have a series of checkboxes mapped to the corresponding fields in Sign and I only see single checkboxes available via the mapping, thoughBooks P&L by Customer
Lately, we have found that the Customer field filter for the Profit & Loss report in Zoho Books is no longer available. We have several projects with the same customer and look at the P&Ls by project AND by Customer (overall). Can you please add back in this field selection alongside the Project field to the P&L report filter? Thanks! -GinaSent mail sort by date disappeared
Hello, We used to be able to sort the emails by date in the sent folder, but this feature has recently disappeared. Can we bring it back?I cannot check out to Zoho People.
When I tried to check out today, there's prompt that inhibits me to check out: To add entry in Attendance, log time for any of your jobsPricing Strategies: #2 Plan your Service Offerings using Plans
Meet Harvey & Mia, hailing from the same Tech school, carrying the dream of setting up their own IT maintenance firm in their location. They drew up a clear business plan and put their business in motion on New Year's Eve. They had great reception rightOpen a record to edit based on another form being edited
Hi all, I am trying to edit a record based on another record being edited. It works out like this: When I create a Monthly_Update and then submit I open the Monthly_Levels form and pass the ID from the Monthly_Update to a field called mu. I want it toProblem with signature on zoho survey
Hello, I'm trying to export individual responses with signatures on zoho survey. But the signatures on some of my surveys are not exported as the original image, but as a generic image, same for all (screen joins). Is there a solution to have the signaturesChange in Zoho CRM API?
Hello, I am using the Zoho API trough the PHP SDK v2.1 Since few days, I noticed that I have to change the way I pass the data to the API when I create, update, or upsert a record. Dates Before I was passing a PHP date object to "$record->addKeyValue(...)",MTA - BAD IP reputation by outlook/hotmail
Messages to Microsoft email servers are bouncing back due to poor reputation. Message: 4.7.650 The mail server [136.143.188.206] has been temporarily rate limited due to IP reputation. For e-mail delivery information see https://postmaster.live.com (S775)QuickBooks Extension for Zoho CRM - Advanced Features -2025
Hello Everyone, We’re happy to announce the latest version of our QuickBooks Extension for Zoho CRM, now officially live on the Zoho Marketplace! This release introduces one-click data sync, a user-friendly UI, enhanced performance, and a powerful setChanging Department often causes the Firefox tab to freeze
Title, it doesn't seem to happen with neither Opera nor Chrome. And even in Firefox, sometimes it just lets me change the department I'm in no problem, even to All Departments which is probably the most, like, resource heavy? But most of the time, theNeed to integrate Zoho Mail Mobile app with Zoho Meeting Mobile App for Android and Apple
Hello Zoho Team, Please bring integration of Zoho Mail Mobile app with Zoho Meeting for Android and Apple Thanks¡Muchas gracias por participar a los Meetups de Usuarios de Zoho! Y Novedades del ecosistema Zoho
¡Hola Comunidad de Zoho en Español! 👋 Después de un breve lapso de tiempo, volvemos con una nueva edición de nuestro Community Digest, donde te contamos las novedades de los productos de Zoho en los últimos meses. Estas mejoras se centran en nuestrosAnyway to move mail from one account to another yet?
Hello, Is there any way to move email from one mailbox account to another mailbox account in zoho yet? Thanks, Ryan.Using a CRM Client Script Button to create a Books Invoice
Hello, I need help handling error messages returned to my client script from a function. The scenario I have setup a client script button which is available from each Deal. This CS executes a crm function, which in turn creates an invoice based on theBuilding Toppings #2 - Learn how to use Bigin's Developer Console to build toppings
Hey Biginners, In our last post, we discussed what toppings are, why they're essential to extending Bigin's capabilities, and how the Bigin Developer Center serves as the starting point for building them. As a cloud platform, the Developer Center empowersLinks are incorrect when sent out
I'm adding in hyperlinks into my eDM. When I send a test email, it's all correct. However, when I send out the eDM, all the hyperlinks jump up one space so none of the links are opening to the correct page. Why is this happening and how can I fix it?Tip of the week #16 - Search and filter threads based on criteria
Zoho TeamInbox lets you search and filter threads with any information that you have about the thread. You just have to input the criteria and Zoho TeamInbox will list all the threads that match the condition. Firstly, there is a global search you canIntroducing recipient authentication via Stripe Identity in Zoho Sign
Hi everyone! It's important to authenticate your recipient's identity before they access and sign important documents to ensure the highest level of compliance. Zoho Sign already helps businesses do this with various authentication methods: SMS OTP EmailKeep Converted Leads
How do I keep the converted leads in the Leads Module after conversion (converting it to account, contact, deal). I want to add it in a converted stage in the leads module in order to get a report or dashboard and see all converted leads from my pipCustomizing Global Search Settings for All Users
Hi Our team use the brilliant global search functionality within CRM many many times daily. But, we struggle with the out-of-the box columns that CRM gives you. We are always telling users to customize this look to more suit our business, to show theIntroducing Formula Fields for performing dynamic calculations
Greetings, With the Formula Field, you can generate numerical calculations using provided functions and available fields, enabling you to derive dynamic data. You can utilize mathematical formulas to populate results based on the provided inputs. ThisTip of the Week #77– Stay informed of the activities happening in your organization
Whenever a message is handled in Zoho TeamInbox, every action is recorded in the Activity Log. This ensures you always know what’s happening across your teams and inboxes. To access it, simply click the Audits icon on the left pane’s top bar after loggingZoho Logs - Not seeing logs since 30 Nov
Hi, we have a few functions running, I am testing some new ones and noticed that although I can see executions, I cannot see any logs, even when the first line on the functions is a log. I reviewed some existing functions, one of which is invoked on aWorkdrive MS Office integration
Have installed subscribed version of Zoho WorkDrive VSTO runtime not found is the error when I try to install Zoho_WorkDrive_For_Office Unable to open work files in Excel and Word Urgent, since I have migrated all my OneDrive files to work drive alreadyHow to change Zoho Vault password
I am searching where I can change the Vault Password after having changed the Zoho account password. I don't see this option anywhere in my account. It should be simple and accessible! Please help. Thanks!Zoho Workdrive - Communication / Chat Bar
Hi Team, Please consider adding an option to allow admins to turn on or off the Zoho Communication Bar. Example of what I mean by Communication Bar: It's such a pain sometimes when I'm in WorkDrive and I want to share a link to a file with a colleagueWhen Marking a Multiple Choice Answer Exclusive - Not Following My Survey Disqualification Logic
Using a multiple choice (many answers) question and I created survey disqualification logic that was working as intended. My question: Disqualification page logic is: If (QUESTION) is "any one of the following" then (OPTIONS) - a custom message populatesWhere to Add Machines as Products to Map with Assets in Zoho FSM?
implementing Zoho FSM for a clinical equipment supply company. The business sells and installs clinical machines in hospitals and clinics, and they also handle service requests, scheduled maintenance, calibration visits, and general machine upkeep. InShow Zoom Link in Recipient's Calendar
We set up meetings within a record, selecting the "Make this an online meeting". We use Zoom. Most of the recipients go to their calendar (usually Gmail or Outlook, corporate) to join the Zoom meeting, but there is no Zoom link in the calendar. Can thisZoho Bigin - should be able to link a "contact" to multiple "companies"
Hello Support, I called into telephone support and was told that a contact can only be linked to one company. We have situations were director are contacts of and directors of multiple companies so that seems a basic weakness in Bigin. When go to addDoes Thrive work with Zoho Billing (Subscriptions)?
I would like to use Thrive with Zoho Billing Subscriptions but don't see a way to do so. Can someone point me in the right direction? Thank youRadio button data won't update
Wondering if anyone is experiencing the same problem. I tried bulk updating our data on Zoho Creator using API and noticed that the radio button field wasn't updated. I have tried updating it manually, it didn't work. When I tried updating a text fieldIntroducing Global Sets for easy management of similar picklists in CRM
Latest update (December 2025): You can now apply color coding to the values inside a global set, the same way you color code values in regular picklist fields. Update (Sep 2024): We've increased the maximum count limit for global sets. These new limitsNo Ability to Rename Record Template PDFs in SendMail Task
As highlighted previously in this post, we still have to deal with the limitation of not being able to rename a record template when sent as a PDF using the SendMail Task. This creates unnecessary complexity for what should be a simple operation, andNext Page