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
How to restrict user/portal user change canvas view
Hi , I would like to restrict user / portal user change their canvas view because I hide some sensitive field for them. I dont want my user switch the canvas view that do not belong to them But seems Zoho do not provide this setting?How to add to Subforms via Zapier with Zoho Writer?
I have the following layout for a Zoho Writer Document. As you can see there is a repeating subform taking in "Items". I am trying to make a Zapier integration with it, and I can see there is 1 field saying: "Items", but it does not specify how I'm supposedContrôles administratifs granulaires : un atout pour la sécurité des e-mails
La moindre erreur dans votre système de messagerie peut coûter très cher à votre entreprise, tant en argent qu’en conformité et en image de marque. Pour beaucoup d’organisations, ce risque est bien réel. Les e-mails véhiculent quotidiennement des informationsCollections Management: #6 Realign Customers who gets back In-Term
Arun stared at the subscription list on his dashboard. Another account had just been moved to Cancelled status after completing the whole dunning process. Nothing unusual, just that payment failures happen, retries fail, and cancellation is the expectedZoho Mail IP Blacklist
I need problems with send mails: Error: junk mail rejected - sender4-op-o10.zoho.com 136.143.188.10, is in RBL. Spamcop. Please remove FQDN for blacklist. Regards.I can receive but not send emails
Hello, I've been not able to send emails for almost a year now. I been using alternate email to do this. I want to know how to fix this so I can use my zoho account normally again.The challenge of 24/7 connectivity: Being present and meeting customer expectations
Before television entered our homes, radio was our window to the world. We had to tune carefully to catch voices from distant places. When television arrived, the world began to grow smaller. We can watch rocket launches, see the goal that wins our favoriteHow to download all attachments from inbox, send, other folders in one go
Hi All, Appreciate if anyone could help me with steps for below requirement. How to download all attachments from inbox, send, other folders in one go. Even mapping to new folder will help me. Thanks in advance.Cannot connect mail accounts to Thunderbird
Hi Support - I'm attempting to add my mail accounts to Thunderbird but I'm getting an unable to login to server error. I tried to use the password associated with my account I received the unable to login error. So I went into Zoho Accounts and generateAlias Email Id already exists
Hi, I just verified my domain sesque (dot) com and now I am trying to create the admin account using admin (at) sesque (dot) com, but I am getting an error saying "Alias Email Id already exists". I used to have another Zoho account with this email address,Unable to connect to smtp server, connection timed out
Hi Team, I am facing below issue, while sending out emails from thunderbird client. It used to work, facing this issue from morning. Error: Sending of the message failed. The message could not be sent because the connection to Outgoing server (SMTP) smtp.zoho.comKaizen #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.DKIM Verification Failed (Namecheap)
Hi! I have already set up the TXT records in Namecheap but I keep getting the "Verification Failed" pop up. Was wondering if I'm the only one who has this problem and can anyone help me with this? Thanks!Emails stuck in Queue
Hi there, Since yesterday I have a few out going emails stuck in a queue. It say it will auto retry sending however nothing is happening. It seems to be affecting roughly 50% of my outgoing emails. Please help ThanksSoft Bounce from transational emails from BREVO (Sendinblue)
I manage the website of a client who uses your EMAIL service for the domain floranativadobrasil.com. And I use the BREVO email service, previously called SendinBlue, to send transactional emails about events specific to the website. All emails sent toGetting an error Address not found Your message wasn't delivered
Hey, I'm trying to configure zoho mail for my website https://businessentity.org/ The email is meredith.karter@businessentity.org I'm able to successfully send the mails but when someone sends an email to above mail, this error shoots up: Address notSupport Uploading YouTube Videos Longer Than 60 Minutes
Hi Zoho Social Team, How are you? We would like to request support for uploading YouTube videos longer than 60 minutes directly through Zoho Social. Your support team informed us that Zoho Social currently cannot upload videos over 60 minutes due to “APINeed Faster Help? Try Live Chat Support
Hello there, We understand that sometimes, whether you’re facing an issue, exploring a feature, or need quick clarification, sending an email and waiting for a response just doesn’t cut it. You need answers, and you need them now. That’s exactly why weCan't deactivate Spell Check
Hi Community, right now I'm using the Zoho Mail Desktop-Software. So far, so good.. many possibilities. Overall very nice. What is extremely annoying right now, is that we are not able to deactivate the Spell Check feature. And we are barely able to focusZoho Toolkit Email Signature Generator
I'm having real issues with the email signature generator with no matter where I host the photo, Zoho doesn't seem to show the photo on the link provided?Company Policy Upload - Request All EE to review and sign
How can I upload policies into Zoho People and have the employees review them and sign off saying they agree, etc.? Also, if I make a revision to a policy, I would like that changed or updated policy to be distributed or have the employees notified thatZoho Sign Global Settings vs. Template and Document
Hello, We are running into an issue on a current use case. We already use Zoho Sign. Now that KBA is available, we want to begin using it in our tax delivery process, by allowing clients to sign electronically, but also download a copy of their returnZoho Mail Desktop Crashes on Linux - Ubuntu 24 LTS
Hi, I have been trying to run the desktop app on Ubuntu for the past few day with no luck. I have tried both the .deb package and the appImage. When I attempt to open the app. It just crashes immediately. The crash error dialog appeared once and I cantZoho IP blocked by SpamCop
Hi, Many of my emails are blocked and I receive this: INVALID_ADDRESS, ERROR_CODE :550, ERROR_CODE :spamcop.mimecast.org Blocked - see https://www.spamcop.net/bl.shtml?136.143.188.51. - https://community.mimecast.com/docs/DOC-1369#550 [DGwIYPPSOfWIServer error when trying to Data > Sort > Custom Sort
Been using Data > Sort > Custom Sort for a while, now it has suddenly stopped working. When selecting the same data range and trying to execute, I get "Sorry! There was a problem saving your last edit. Please try again."To Assign a genrated pdf to a file upload field using delug
content = "<html><body>HTML Content on page One <div style='page-break-after:always'></div> HTML Content on page Two </body></html>"; file = zoho.file.convertToPDF(content); file.setFileName("Name of the file"); <variableName> = <FormLinkName>[ID == input.ID];TArgets To Accounts (Modules)
How can i set sale target to Customers (Accounts Module)Breaking barriers with multilingual WhatsApp templates in IM
Ever wondered what it feels like to be greeted in your own language by a brand you love? A “Welcome!” feels nice, but a “¡Bienvenido!” or “स्वागत है!” feels personal. In today’s global world, conversations often need to cross both time zones and crossHow to share private Opportunities with whole org at an account level
Opportunities are currently set to private, so our sales team only sees their own opportunities, along with their manager and upper leadership. The need is the ability for the rest of the Org to see the opportunities at an account level, not within theProblema Verificacion con proveedor NIC.AR
No puedo realizar la verificación del correo, he seguido los pasos indicados y configurado los MX. Y no puedo verificar el correo. He leido en el foro que otros han tenido el mismo problema. Alguno pudo solucionarlo?How to remove some users in zoho accounts
How to remove some users in Zoho accounts.Unified Inbox for all, including fetched mails
I fetch mails from different third-parties mailboxes. But I need to switch mailbox too see fetched mails. It's strange. All mailboxes have one shared disk space for own mail and fetched mail, but why do we need to switch mailbox (on the left bottom) toWhatsapp Limitation Questions
Good day, I would like to find out about the functionality or possibility of all the below points within the Zoho/WhatsApp integration. Will WhatsApp buttons ever be possible in the future? Will WhatsApp Re-directs to different users be possible basedUsers Not Automatically Being Added To WorkDrive Team
I have already created a ticket for this issue, but the support team doesn't seem to understand what's happening. Our organization started with a trial of Zoho Workplace around November 10, 2025. I created 10 users, including myself. I sent out the invites,Synchronization between Gmail and Zoho Mail
Hello! I am using Zoho Mail within the Zoho One platform. I have completed the basic setup and added all the required DNS records with our domain provider. Our goal is to set up two-way synchronization between our current Gmail inbox and Zoho Mail, butIMAP login problem
I have my domain hosted with zoho @wilson.ie I have added a new user and have enabled IMAP access to this user account The user can login to zoho mail on the web. When we enter the server settings into Outlook as per below, Outlook cannot login to theChanges to subform in Zoho CRM Portal Timeline History Unavailable
Hi Support Team, We have noticed a feature limitation in the Zoho CRM portal. We created a portal for our vendors to edit records directly, but when vendors make updates, the Modified Time and Date fields are not being updated. Additionally, these updatesThis mobile number has been marked spam. Please contact support.
Hi Support, Can you tell me why number was marked as spam. I have having difficult to add my number as you keep requesting i must use it. My number is +63....163 Or is Zoho company excluding Philippines from their services?Duplicating report but custom layout does not
Dear Zoho Creator, I need to duplicate a report into 10 copies, but unfortunately the custom layout (detail view) doesn’t copy along with it. I tried exporting and importing the custom layout, but the field mappings are incorrect. I believe everyone areCredit Card Readers?
We would like to use our commerce website at conferences (and eventually in store) to swipe credit cards to pay for orders. How would we accomplish this? Does Zoho have anything available for a developer write code to integrate something like Stripe TerminalNext Page