Generate Academic Calendar using MySQLhttps://www.joellipman.com/articles/database/mysql/generate-academic-calendar-using-mysql.html

27 November 2012 year for referencing by staff/students. How? Let us assume that we have a system holding a calendar in the following table (called "joes_weekstructure"): ID AcademicSet WeekNumber StartDate --------- ----------------- --------------- ------------------...

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

T-SQL Conversion failed when converting the varchar to data type inthttps://www.joellipman.com/articles/database/t-sql/t-sql-conversion-failed-when-converting-the-varchar-to-data-type-int.html

14 May 2013 A very quick note in case I forget this one. If you are trying to join two tables and receiving the error "Conversion failed when converting the varchar value 'B110' to data type int" then read on. How? So where does the 'B110' string come from, well...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• 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 is a quick article on how to use a counter added to the end of a field column of data. Eh. What? Assume the following two tables exist: TABLE: Employee -------------------------------------------------- ID FirstName Surname 1 Joel Lipman 2 Another Joe...

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

Converting SQL date in PHP to European date format and vice-versahttps://www.joellipman.com/articles/web-development/php/converting-sql-date-in-php-to-european-date-format-and-vice-versa.html

17 September 2009 convert a given SQL date to the standard European format: $this_date=mysql_result(mysql_query("SELECT my_date_field FROM my_table WHERE my_id='$this_id'"), 0, "my_date_field"); $this_date_array=explode("-", trim(substr($this_date, 0, strpos($this_date,...

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

MySQL Oracle equivalentshttps://www.joellipman.com/articles/database/mysql-oracle-equivalents.html

27 September 2010 fact, this is just a quick list of some regular commands in MySQL that I need in Oracle: Objective MySQL Oracle - See if a table exists SHOW TABLES Select table_name from sys.dba_tables - Return a specific number of rows SELECT * FROM table1 LIMIT 0, 10...

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

Oracle SQL - Convert given list (CSV) into Rowshttps://www.joellipman.com/articles/database/pl-sql/oracle-sql-convert-given-list-csv-into-rows.html

30 August 2011 Situation I have one column of values that I've suffixed a comma to but I want to display this as a rows in a table. This is for Oracle PL/SQL. My List: 1, 2, 3, 4, 5, 6 Yields: 1,2,3,4,5,6 Using this snippet SELECT EXTRACT (VALUE (d),...

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

SSIS: How to loop through multiple flat files as data sourceshttps://www.joellipman.com/articles/microsoft/ssis/ssis-how-to-loop-through-multiple-flat-files-as-data-sources.html

13 June 2014 a quick step-by-step on having an SSIS package loop through a directory/folder of files in order to populate a database table. We could add each file as a separate connection manager but this is inefficient and not versatile enough to accommodate files...

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

SITS: Export field code and namehttps://www.joellipman.com/articles/web-development/xml/sits-export-field-code-and-name.html

12 August 2014 If however, for some reason the code does not exist the code lookup will fail: -- If CODE=0000 and 0000 does not exist in table to lookup name, -- Using &G[COD.SRS:•|•] -- yields: -- But if CODE is not populated, -- Using &G[COD.SRS:•|•] -- also yields:...

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

Copy a Wordpress Site for Developmenthttps://www.joellipman.com/articles/cms/wordpress/copy-a-wordpress-site-for-development.html

08 March 2015 in LIVE environment Clear the database in DEV Copy database from LIVE to DEV Change database values: Navigate to wp_options table and change the two values where `option_name`='siteurl' change `option_value` // from www.myexample.com to...

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

MySQL: Count occurrences of words in a columnhttps://www.joellipman.com/articles/database/mysql/mysql-count-occurrences-of-words-in-a-column.html

12 March 2020 each word in your database column by order of most used first (replace 'mydbprefix_content' with the name of your database table AND `introtext` with the name of your column): SELECT SUM(`total_count`) as `total`, `value` FROM ( SELECT count(*) AS...

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

Zoho Analytics: Setup DataSource via FTP Connectionhttps://www.joellipman.com/articles/crm/zoho/zoho-analytics-setup-datasource-via-ftp-connection.html

12 January 2022 filter field in the top right and there should be a single FTP option displayed to you: Step 1 of 2: Give the datasource the table name it will have in Zoho Analytics, a description and then enter in the FTP details as per your configuration, then click...

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

Zoho CRM: Template: Empty Space between Header and Line Itemshttps://www.joellipman.com/articles/crm/zoho/zoho-crm-template-empty-space-between-header-and-line-items.html

03 February 2022 A very quick article on how to deal with an issue where a CRM template has been used and where the table of line items appears on a separate page (and to remind me of the CSS to fix this). Why? By default / out-of-the-box / vanilla, in a Zoho CRM...

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

Zoho Analytics: Set Up a Widget Displaying Sales Personhttps://www.joellipman.com/articles/crm/zoho/zoho-analytics-set-up-widget-displaying-sales-person.html

24 October 2022 the page that a widget has clearly defined values based on the filters I select on a dashboard. First you will need a table or query that has a bunch of columns and rows with one of the columns containing the sale person name. Something like the...

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

ZohoAnalytics: Pivot Campaigns vs Contactshttps://www.joellipman.com/articles/crm/zoho/zohoanalytics-pivot-campaigns-vs-contacts.html

20 March 2023 confused with the GUI: Query 1 Due to the limit of sub-query levels we can go down, I want to join this to the contacts data table. So I'm going to make this first query and save it as "Contacts vs Campaigns Pivot": SELECT * FROM ( SELECT "Contact Id",...

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

Zoho Survey & Zoho Analytics: Query to generate individual responses and grouped pageshttps://www.joellipman.com/articles/crm/zoho/zoho-survey-zoho-analytics-query-to-generate-individual-responses-and-grouped-pages.html

25 September 2025 but at least it lets me synchronize every hour. Then I'll write the SQL queries to get the individual responses into a table with labels and columns that can help me group them (and identify which responses belong to which survey attempts). Then I'll...

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

First and Last Entry on a Page using Modulus Remainderhttps://www.joellipman.com/articles/web-development/php/first-and-last-entry-on-a-page-using-modulus-remainder.html

26 September 2011 looking for... always difficult. After a cup of tea the solution was glaringly obvious, just do a primary school mathematics table and it all makes sense (see my 10 mod table below). This article is a quick note (so I never spend as long again) in PHP...

• Type: Article
• Author: Joel Lipman
• Category: Personal Home Page
• Language: *

Disable User Registration in Joomla 1.5 - Configuration Filehttps://www.joellipman.com/articles/cms/joomla/disable-user-registration-in-joomla-1-5-configuration-file.html

08 October 2013 configuration.php file prior to Joomla! v1.6.x but in the database. Open the database to the Joomla 1.5.x website Browse the table "jos_components" (or "#__components" where "#_" is the site table prefix. default is "jos"). Open the row where name="User...

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

Zoho Inventory Template Issue: First Page is Blankhttps://www.joellipman.com/articles/crm/zoho/zoho-inventory-template-issue-first-page-is-blank.html

15 November 2018 an article to help me resolve the particular issue where the organization details and the header of the product line items table would display on the first page, the product line items would then print on the second page, with the terms and conditions...

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

COVID-19: Working from Homehttps://www.joellipman.com/covid-19-work-from-home.html

05 July 2020 for efficiency and comfort in the working environment." So it's pretty important not to be slouching over the coffee table for hours or days on end. I was waiting to move house during 2020 but then a virus capable of locking down the world manifested...

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

Prices get rounded up in Virtuemarthttps://www.joellipman.com/articles/ecommerce/virtuemart/prices-get-rounded-up-in-virtuemart.html

27 September 2009 to convert any number to a currency or two decimal places. Another suggestion was to alter the main virtuemart database table (jos_vm_prodcut_price)... Don't do any of these things! After modifying tax rates and ensuring these were all the same, we also...

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