T-SQL example of Case-Sensitive Soundexhttps://joellipman.com/articles/database/t-sql/t-sql-example-of-case-sensitive-soundex.html

10 October 2012 This is obviously a simplified table as it doesn't tell us much but it is just for demo purposes. Consider the following query: SELECT DISTINCT StudentDetail FROM StudentTable WHERE SOUNDEX(StudentDetail)=SOUNDEX('Data Not Yet Available') -- yields...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Search a database for a value and count matching rowshttps://joellipman.com/articles/database/search-a-database-for-a-value-and-count-matching-rows.html

05 November 2012 EXEC(@SqlToExecute); FETCH NEXT FROM Cursor1 INTO @TableToProcess END CLOSE Cursor1; DEALLOCATE Cursor1; -- Generate final query and execute it SELECT * FROM #CountRecordsPerTablePerCol; END Issues Consider adding a drop of the temporary table to the...

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: en-GB

Delete related records from multiple tableshttps://joellipman.com/articles/database/mysql/delete-related-records-from-multiple-tables.html

11 January 2013 DELETE clause. Rather straightforward but I got this answer from StackOverflow. Based on the previous example, here's the query to only delete records in Session and SessionStudent represented by aliases s and sst respectively and to leave SessionStaff...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: *

Quickly update all content in mySQL database replacing a stringhttps://joellipman.com/articles/database/mysql/quickly-update-all-content-replacing-a-string.html

03 February 2013 REPLACE(`introtext`, @stringtoFind, @stringtoReplaceWith) WHERE `introtext` LIKE CONCAT('%',@stringtoFind,'%') Run the first query again and no rows should be returned. Quick Joomla One I use this reluctantly but often necessarily. SET @stringtoFind='...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: en-GB

Fix Oracle Tnsping 3511 without Windows Registryhttps://joellipman.com/articles/database/pl-sql/fix-oracle-tnsping-3511-without-windows-registry.html

04 March 2013 being already logged in, this is the same user but re-prompted for credentials) Cannot run System Registry (not even DOS REG Query!!!) All oracle utilities and connections working on my computer (except for TNSPING). TNS_ADMIN is another environment...

• Type: Article
• Author: Joel Lipman
• Category: Oracle PL/SQL
• Language: en-GB

T-SQL concatenate an incremental row numberhttps://joellipman.com/articles/database/t-sql/t-sql-concatenate-an-incremental-row-number.html

24 May 2013 How? So how do we do it? In the above example, Joel Lipman has two qualification records. So let's start with a simple query: SELECT RIGHT('000' + CAST(e.ID AS VARCHAR), 3) AS EmployeeNo, RIGHT('000' + CAST(q.Employee AS VARCHAR), 3) + '01' AS...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Split a row into multiple rows based on a column valuehttps://joellipman.com/articles/database/t-sql/split-a-row-into-multiple-rows-based-on-a-column-value.html

23 August 2013 1.00 2000-02-19 002 1.00 2000-02-20 003 1.00 1999-02-25 003 1.00 1999-02-26 Some thing(s) to consider: User running the SQL query needs permission to read the master database. If run by a service account, ask a DBA to emulate the user for testing. the...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

Registered Users Cannot Login - Super Users Canhttps://joellipman.com/articles/cms/joomla/registered-users-cannot-login-super-users-can.html

26 February 2014 article is not addressing your issue. This is a red herring. If the number returned is greater than 1 then run the following query: UPDATE #__assets SET parent_id=1 WHERE parent_id=0 AND id1 AND title'Root Asset' -- where #_ is the Database Tables...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: en-GB

Export Joomla Users scripthttps://joellipman.com/articles/cms/joomla/export-joomla-users-script.html

28 February 2014 passwords to be in MD5. All I want is a quick bit of code to get all the users out of a Joomla CMS. How? I could just type a query on the database and get the same result, but this script was more fun. Simply create a text file with the following code...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: en-GB

Include a carriage return in a column headinghttps://joellipman.com/articles/microsoft/sql-server/include-a-carriage-return-in-a-column-heading.html

07 July 2014 17-Jul-2014 18-Jul-2014 2 21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014... How? To do this in a select query resultset, you insert the special character references "CHAR(10)" [line feed] and "CHAR(13)" [carriage return] but to do this in...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server
• Language: *

Foreign Characters create symbols in PHP and MySQLhttps://joellipman.com/articles/web-development/php/foreign-characters-create-symbols-in-php-and-mysql.html

25 January 2016 My other fix (for Croatian characters and Western languages): // insert after database connection and prior to database query (where $db_conn is your mysqli connection) mysqli_set_charset($db_conn,"utf8"); // decode and display in HTML-safe...

• Type: Article
• Author: Joel Lipman
• Category: Personal Home Page
• Language: en-GB

Google Drive API v3 - OAuth2 using Service Account in PHP/JWThttps://joellipman.com/articles/google/google-drive-oauth-using-service-account-in-php.html

02 February 2019 $method); curl_setopt($ch, CURLOPT_HTTPHEADER, $header); curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($data)); curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); $response = curl_exec($ch); curl_close($ch); $output = json_decode($response, true);...

• Type: Article
• Author: Joel Lipman
• Category: Google
• Language: en-GB

Zoho Deluge: Setup an API Connection for InvokeURLhttps://joellipman.com/articles/crm/zoho/zoho-deluge-setup-an-api-connection-for-invokeurl.html

12 August 2020 clients. eg. "JoelLipman_BooksConnector". Homepage URL: put our corporate website URL, this is for developers who need to query what this app belongs to or does. eg. "https://www.joellipman.com/" Authorized Redirect URIs: for this put in one of the...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Zoho Deluge: Zoho Bookings Get Available Slotshttps://joellipman.com/articles/crm/zoho/zoho-deluge-zoho-bookings-get-available-slots.html

23 June 2021 This is an article of code snippets to query a Zoho Bookings instance from within Creator. This one focuses on getting the available slots. Why? This is for a Creator app which had a form to allow customers to make a booking based on the configuration...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Zoho Creator / eBay: Get all Active Productshttps://joellipman.com/articles/crm/zoho/zoho-creator-ebay-get-all-active-products.html

13 October 2021 page, returns these in JSON as a Zoho Map datatype. How? I'm not going to go into detail on how I create an access token to query the eBay Trading API as you can read this in my article: Joellipman: Zoho Creator: Push to eBay Listings. You will note...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Zoho CRM: Schedule a Task: Timesheet Reminderhttps://joellipman.com/articles/crm/zoho/zoho-crm-schedule-a-task-timesheet-reminder.html

02 December 2021 from Monday 00:00 v_FirstDateOfWeek = zoho.currentdate.toStartOfWeek().addDay(1).toString("yyyy-MM-dd"); // // build up COQL Query v_CoqlQuery = "select Start_DateTime, End_DateTime from Events where Created_By='" + v_UserID + "' and ("; v_CoqlQuery =...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Zoho CRM/Deluge: Get TimeZone Based on GeoCoded Address (Lat/Lng)https://joellipman.com/articles/crm/zoho/zoho-crm-deluge-get-timezone-based-on-geocoded-address-lat-lng.html

15 December 2021 + r_GeoCode.get("latitude")); l_Params.add("lng=" + r_GeoCode.get("longitude")); l_Params.add("by=position"); } // // query with a GET method r_Timezone = getUrl("http://api.timezonedb.com/v2.1/get-time-zone?" + l_Params.toString("&")); // // if we got...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

ZohoCRM & Xero Real-Time Invoices: Receive Webhookhttps://joellipman.com/articles/crm/zoho/zohocrm-xero-real-time-invoices-receive-webhook.html

10 January 2022 // Generate your access token to connect to Xero and query Xero records // See my article: https://www.joellipman.com/articles/crm/zoho/zoho-deluge-sync-to-xero-api.html // v_TenantID = ""; v_AccessToken = ""; r_Response =...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

ZohoDeluge: Get All eBay Orders Given a From and Till Datehttps://joellipman.com/articles/crm/zoho/zohodeluge-get-all-ebay-orders-given-a-from-and-till-date.html

28 February 2023 An article on how I can query all the orders for yesterday without using the eBay GUI Selling website. I've included the code to parse out the information as well. Why? Because I need this function too often either for debugging/monitoring purposes but...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

ZohoDeluge: Check Shipment Status via DHL APIhttps://joellipman.com/articles/crm/zoho/zohodeluge-check-shipment-status-via-dhl-api.html

21 March 2023 first I'll give some brief instructions on how to get an API key from DHL as a developer and then I'll include the code to query the shipment status based on a tracking number. Becoming a DHL Developer Browse to https://developer.dhl.com/user/register...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *