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

11 January 2013 So this is a quick article on how to delete from multiple tables in a mySQL database where we use some JOIN statements. Why? It is never recommended to delete from multiple tables and instead to use the system you were given. Given a Relational Database...

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

JDatabase: using the Joomla database with exampleshttps://www.joellipman.com/articles/cms/joomla/jdatabase-using-the-joomla-database.html

27 January 2013 = $row->username; $this_user_realname = $row->real_name; } Chaining A quick note on how to chain, the following two statements are equivalent: -- note the semi-colons at the end of each line $query->select($db->quoteName(array('user_id', 'user_name')));...

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

Country Lookup by IP address CSVhttps://www.joellipman.com/articles/database/mysql/country-lookup-by-ip-address.html

17 March 2013 site: «Download» Run the SQL script against a test database, Modify the table name to use if necessary. Uncomment the drop statement if running for the second time. Modify the PHP file to use database login credentials, $db_host = "localhost"; //...

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

Improve Default Joomla Search https://www.joellipman.com/articles/cms/joomla/improve-default-joomla-search-heuristics.html

07 May 2013 « Show Me » [Same file again] \plugins\search\content\content.php: Add the option to select by relevance to the CASE order statement: switch ($ordering) { // BEGIN: joes new ordering option case 'relevance': $order = 'relevance DESC'; break; // END:...

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

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

24 May 2013 and more employees. PARTITION BY So a sort of "group" action will allow this if we add "PARTITION BY" to the ROW_NUMBER() statement: SELECT q.Employee AS EmployeeNo, RIGHT('000' + CAST(q.Employee AS VARCHAR), 3) + RIGHT( '00' + CAST( ( ROW_NUMBER() OVER...

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

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

23 August 2013 003 2.00 1999-02-25 1.00 If you have more than 999 items in the column, then you may need to increment the 3 in the above statement "LEFT(..., 3)". Almost there, but in the above example, the dates are wrong for subsequent days off sick: SELECT...

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

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

07 July 2014 [carriage return] but to do this in the name of the column heading, the answer is a much simpler one, in your SQL statement, simply place your cursor where you want the carriage return and press Return/Enter. This has to be a label to the name of the...

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

A quick run through of setting up an export process in SITShttps://www.joellipman.com/articles/web-development/xml/a-quick-run-through-of-setting-up-an-export-process-in-sits.html

21 August 2014 enclosed by double chevrons Enter the same field to monitor under "New field/Value" enclosed by double chevrons The final statement will be something like != Use the Operand "OR" or "AND" to add further fields, in our demo we want all the key fields:...

• Type: Article
• Author: Joel Lipman
• Category: Extensible Markup Language
• Language: *

Migrating from Joomla 2.5.x to 3.4.xhttps://www.joellipman.com/articles/cms/joomla/migrating-from-joomla-2-5-x-to-3-4-x.html

12 September 2015 personal website: www.joellipman.com. This article assumes you are familiar with upgrading a Joomla! CMS and running MySQL statements against your database. For the purposes of this article, we will refer to the live/current/production website running...

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

Convert foreign characters to English alphabethttps://www.joellipman.com/articles/database/mysql/convert-foreign-characters-to-english-alphabet.html

13 June 2016 A quick article showing my MySQL statement when I want to remove all the accents from foreign characters and return the English equivalent. Why? A content management system (CMS) that I'm working on has just gone international and started including the...

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

Zoho Creator: Retrieve record with case-insensitive queryhttps://www.joellipman.com/articles/crm/zoho/zoho-creator-retrieve-record-with-case-insensitive.html

21 November 2019 but returns the error "Result: Error on Execution" > "Error details: Execution Failed Due to invalid expressions insert statement is terminated Line:(99)" > "Duplicate entry 'MyProduct1' for key 'Product_Name' Line:(99)". This indicates that the search...

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

Zoho Creator: Download uploaded file and attach to Sales Order in Zoho Bookshttps://www.joellipman.com/articles/crm/zoho/zoho-creator-download-uploaded-file-and-attach-to-sales-order-in-zoho-books.html

15 May 2020 file name. {"code":2,"message":"Invalid value passed for doc"}: The doc parameter contains special characters. Improper Statement Error might be due to missing ';' at end of the line or incomplete expression This could be a missing semi-colon somewhere...

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

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

12 August 2020 CRM: APIv2 using PHP & cURL (3rd-Party to Zoho), in that this details how to setup a connection to use in an invoke URL statement. Specifically for Zoho Books, Subscriptions or Inventory. Why? Setting up a connection avoids the hassle of having to...

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

Zoho Deluge: Convert Map to HTML Table without a FOR loophttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-convert-map-to-html-table-without-a-for-loop.html

11 January 2021 a Map string into a HTML table without using a for each loop. Why? I have quite a big response from our CRM that hits a statement execution limit if I use a for loop. I have a map with 3 columns: first_name, last_name, and ID. What I want is a HTML...

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

Zoho Deluge: Regex Rounding and/or Removing Trailing Zeroshttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-regex-rounding-and-or-removing-trailing-zeros.html

08 February 2022 digit thereafter to see if there are zeros... But I'm keen on avoiding using loops where possible so we don't breach a statement execution limit in the Zoho app function. How? If we're not going to use loops, then the next best thing I'm aware of is a...

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

Zoho Creator: Find Duplicates by Customer Name for Large Datasetshttps://www.joellipman.com/articles/crm/zoho/zoho-creator-find-duplicates-by-customer-name-for-large-datasets.html

12 July 2023 already records in the system with that key. I can't sit there entering one customer at a time. Even a loop would hit a statement execution limit. Why? My use-case is that I need this done over a dataset of 20k+ contact records and that's only half of...

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

Zoho CRM & Zoho Sign: Send CRM Merged Template for Zoho Signhttps://www.joellipman.com/articles/crm/zoho/zoho-crm-zoho-sign-send-crm-merged-template-for-zoho-sign.html

06 August 2024 templates in ZohoSign // NB: if your document is NOT in ZohoSign but is a mail merge template in CRM, then skip this if statement if(v_ZohoSign_DocumentType == "CreditApplication") { v_RequestID =...

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

If Then Else in Report Builder 2.0 Expressionshttps://www.joellipman.com/articles/microsoft/ssrs/if-then-else-in-report-builder-20-expressions.html

06 May 2010 find this on the WWW so I was obviously not using Google properly. Anyway here's just a quick note on how to do if else statements in Report Builder 2.0 I'm looking at the following if statement: If (MyFieldName = 0) Then Return 1 Else Return...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

SSRS Display question mark when date is blankhttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-display-question-mark-when-date-is-blank.html

15 April 2014 -- yields 11/02/2014 11:21:32 Sounds good, where's the problem? The problem Putting the date in an IIF (if-then-else) statement displays "#Error". You should get errors referring to converting strings to dates and incorrect data types. How? The fix is...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

Joomla WHERE clause with ORhttps://www.joellipman.com/articles/cms/joomla/joomla-where-clause-with-or.html

16 July 2014 clause in a joomla database query. Why? In response to a member, I use the old form where I can include the whole SQL statement: $db->query('SELECT * FROM #__myTable WHERE condition1=true or condition2=true') Method #1 - chain: $query...

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