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
Trigger Workflow, Approval Process, Blueprint by Deluge in Zoho CRM
#Tips & Trick If you intend to activate your Workflow, Approval Process, or Blueprint through either the deluge script or Zoho CRM API during the creation, updating, or deletion of a record Try this Script mp = Map(); trigger = List(); // Workflow trigger.add("workflow");Zoho Projects Android app: Integration with Microsoft Intune
Hello everyone! We’re excited to announce that Zoho Projects now integrates with Microsoft Intune, enabling enhanced security and enterprise app management. We have now added support for Microsoft Intune Mobile Application Management (MAM) policies throughCant't update custom field when custom field is external lookup in Zoho Books
Hello I use that : po = zoho.books.updateRecord("purchaseorders",XXXX,purchaseorder_id,updateCustomFieldseMap,"el_books_connection"); c_f_Map2 = Map(); c_f_Map2.put("label","EL ORDER ID"); c_f_Map2.put("value",el_order_id); c_f_List.add(c_f_Map2); updateCustomFieldseMapSet connection link name from variable in invokeurl
Hi, guys. How to set in parameter "connection" a variable, instead of a string. connectionLinkName = manager.get('connectionLinkName').toString(); response = invokeurl [ url :"https://www.googleapis.com/calendar/v3/freeBusy" type :POST parameters:requestParams.toString()Possible to connect Zoho CRM's Sandbox with Zoho Creator's Sandbox?
We are making some big changes on our CRM so we are testing it out in CRM's Sandbox. We also have a Zoho Creator app that we need to test. Is it possible to connect Zoho CRM's Sandbox to Zoho Creator's Sandbox so that I can perform those tests?I Need Help Verifying Ownership of My Zoho Help Desk on Google Search Console
I added my Zoho desk portal to Google Search Console, but since i do not have access to the html code of my theme, i could not verify ownership of my portal on Google search console. I want you to help me place the html code given to me from Google searchTimeline Tracker
Hi Team, I am currently using Zoho Creator – Blueprint Workflows, and I would like to know if there is a way to track a timeline of the approval process within a Blueprint. Specifically, I am looking for details such as: Who submitted the record Who clickedPrimary / Other Billing Contacts
If you add an additional contact to a Zoho Billing Customer record, and then mark this new contact as the primary contact, will both the new primary and old primary still receive notifications? Can you stop notifications from going to the additional contactsIs it possible to enforce a single default task for all users in a Zoho Projects ?
In Zoho Projects, the Tasks module provides multiple views, including List, Gantt, and Kanban. Additionally, users can create and switch to their own custom views. During project review meetings, this flexibility creates confusion because different users[Free Webinar] Zoho Creator webinars - Learning Table and Creator Tech Connect Series in 2026
Hello everyone, Wishing you all a wonderful new year! May 2026 and the years ahead bring more opportunities, growth, and learning your way 🙂 We’re excited to kick off the 2026 edition of the Learning Table Series and Creator Tech Connect Series ! LearningReply and react to comments
Hi everyone! We're excited to bring to you a couple of new features that'll make your sprint process simpler. A cloud application brings with it an array of social media features that can be efficiently used in your organizational setup. As an agile scrumModule Customisation - Lookup function not available
Good evening, Within my business, I can have multiple customers, who have multiple mobile assets. When I set these assets up, I enter information such as vehicle registration, Vehicle identification number (VIN), Unit number, YOM, in addition to others.zoho click, and nord VPN
Unfortunately, we've been having problems with Zoho Click, where essentially the line cuts off after about a minute's worth of conversation every time we are on VPN. Is there a way we can change this within the settings so it does not cut the line offZoho Calender
a) does the clanender in zoho project allow you to see the name of the event in the celnder view, it currently says either "Task (1) or "Milestoen (1)" b) Alternatively does the calender in Zoho project integrate with zoho calender?Matching ZOHO Payments in Banking
Our company has recently integrated ZOHO Payments into our system. This seemed really convenient at first because our customers could pay their account balance by clicking on a link imbedded in the emailed invoice. Unfortunately, we can't figure out howNew Enhancements to Zoho CRM and Zoho Creator Integration
Hello Everyone, We’ve rolled out enhancements to the Zoho Creator and Zoho CRM integration to align with recent updates made to the Zoho Creator platform. With enhancements to both the UI and functionality, This update also tightens access control byWork Type - Limitation
Hello, I'm setting up work types and have noticed, a limitation on the parts area to 10 lines. Can this be increased to 20 or greater? In addition to this, when I attempt to add the work type to a work order, the correct labour hours doesn't flow through.Locked out of MFA due to changing phones
I have been locked out of my Zoho Books account since i changed my mobile phone over. I can get the OTP through the ZOHO Books software but i cant confirm using my phone app as nothing appears to authorise. I need to know how to register/set up my newEmails sent through Bigin are not posting in IMAP Sent folder
I have set up my email to work from within Bigin using IMAP. I am using IMAP so I can sync my email across multiple devices - phone / laptop / desktop / iPad / etc. I want all my emails to populate my email client (outlook & iphone email) whether orTurning the page for Zoho SalesIQ: 2025 to 2026
As we wrap up 2025, we would like to take a moment to reflect on what we set out to achieve this year, what we’ve delivered, and where we’re headed next. What we focused on in 2025 This year was all about strengthening the core of engagement and AI, makingZoho Cliq not working on airplanes
Hi, My team and I have been having this constant issue of cliq not working when connected to an airplane's wifi. Is there a reason for this? We have tried on different Airlines and it doesn't work on any of them. We need assistance here since we are constantlyCustomer Management: #6 Common Mistakes in Customer Handling
Managing customers doesn't usually fall apart overnight. More often, slight gaps in the process slowly become bigger problems. Incidents like missed follow-ups, billing confusion, and unhappy customers will lead to revenue loss. Many businesses don'tZoho Desk iOS app update: UI enhancement of picklist and multi picklist fields
Hello everyone! We have enhanced the UI of the picklist and multiselect picklist fields on the Zoho Desk iOS app to provide a more refined, efficient, and user-friendly experience. We have now supported an option to Search within the picklist and multiselectZoho Desk iOS app update: Revamped scribbles with Apple pencil kit
Hello everyone! We’re excited to introduce a revamped Scribble experience, rebuilt from the ground up using Apple PencilKit for smooth strokes, proper scaling, and seamless image uploads. Please update the app to the latest version directly from the AppZoho Desk Android app update: Norwegian language support
Hello everyone! In the most recent Android version of the Zoho Desk app update, we have brought in support to access the app in Norwegian language. We have introduced the Norwegian language on the IM module of the Zoho Desk app as well. Please updatePayment Vouchers
Is there any Payment Vouchers in Zoho? How can we create payment for non-trade vendors, i.e. professional fees, rent, and payment to commissioner income tax?When I click on PDF/PRINT it makes the invoice half size
When I click PDF / Print for my invoice in Zoho Books, the generated PDF appears at half size — everything is scaled down, including the logo, text, and layout. The content does not fill the page as it should. Could someone advise what causes Zoho BooksSearch by contain letter in a column
Hello, everyone I need a filter function that searches by letter in a cell, and it should be a macro. To clarify further, if I have a column with several names and I chose a search cell and what I want is search by a single letter, for example, "a" thenCan add a colum to the left of the item in Zoho Books?
I would need to add a column to the left of the item column in Books. When i create custom fields, i can only display them to the right of the item.AI Bot and Advanced Automation for WhatsApp
Most small businesses "live" on WhatsApp, and while Bigin’s current integration is helpful, users need more automation to keep up with volume. We are requesting features based on our customer Feedbacks AI Bot: For auto-replying to FAQs. Keyword Triggers:2025 Ask the Experts sessions wrap-up : Key highlights from the experts
Here is a rewind journey of our Ask the Experts (ATE) Sessions, where we brought you expert insights and practical best practices together in one place. This recap highlights the key takeaways, learnings, and best practices from all these sessions soHow to disable the edit option in subform
How to disable the edit option in subformMove record from one custom module to another custom module
Is it possible to create a button or custom field that will transfer a record from one custom module to another? I already have the 'Leads' module used for the Sr. Sales department, once the deal is closed they convert it to the 'Accounts' module. I would like to create a 'Convert' button for a custom module ('Locations') for the department that finds locations for each account. Once the location is secured, I want to move the record to another custom module called 'Secured Locations'. It's basicallyNotes Not Saving
Hello, My notes are continuously not saving. I make sure to save them, I know the process to save them. It is not operator error. I go back into a Leads profile a while later and do not see the previous notes that I have made. I then have to go back and do unnecessary research that would have been in the notes in the first place. Not a good experience and it is frustrating. Slows me down and makes me do unnecessary work. Please resolve. As a quick heads up, deleting cookies is not a fixPrefill form with CRM/Campaigns
I created a form in zForms and created prefill fields. I added this to the CRM and selected the fields so when sending from the CRM, the form works great. However, I want to use the same form in Campaigns and I want it to pull the data from CRM (whichTriggering a campaign automation from a Form
I used Forms to create a lead form that is accessed by a button on my website. The field information flows into the CRM. However, I am trying to figure out how to use Campaign automations to start a workflow (series of campaign emails) that is triggeredEmployee Appraisal Applicability - Why is Date of Joining Hard-Coded?
In the new (to me, at least) Performance Appraisal Cycle wizard, it's possible to set criteria to determine for whom the appraisal process should apply. This makes sense on its face. However, one MUST use the Date of Joining criterion as a filter. WhyFormula fields
Zoho People now supports formula fields. This post illustrates it. Formula fields are fields whose value is calculated instead of being entered by the user. Using this, number, decimal and date manipulations can be done. The value of this field could be numeric or date depending on the output of the formula. In date manipulations, the result will be given in milliseconds, which you can format as per you need. The operators we support are +, - , *, /. Formula fields get recalculated automaticallyZobot operator information
I am working on a zobot that calls a plug to send data to our API. I want the response data from that API to be shown only to the operator and not the customer. I have tried writing this data out to the visitorCity field but nothing gets updated on theCopy paste from word document deletes random spaces
Hello Dear Zoho Team, When copying from a word document into Notebook, often I face a problem of the program deleting random spaces between words, the document become terribly faulty, eventhough it is perfect in its original source document (and withoutNext Page