Migrate Joomla! 1.5.x to 2.5.x+https://joellipman.com/articles/cms/joomla/migrating-from-joomla-15-to-16.html

30 January 2011 @new_jos_assets_id_last_lft:=@new_jos_assets_id_last_lft+2 AS lft, @new_jos_assets_id_last_lft + 1 AS rgt, 2 AS level, CONCAT( 'com_banners.category.', @new_jos_assets_last_inc:=@new_jos_assets_last_inc+1 ) AS name, CONCAT(`title`, ' :|joes|', `id`, '|:...

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

Zoho Creator/Deluge: Calculating with Timezone Offsethttps://joellipman.com/articles/crm/zoho/zoho-creator-deluge-calculating-with-timezone-offset.html

07 December 2021 } // // display confirmation text v_ResultInformation = " "; v_ResultInformation = v_ResultInformation.concat(" Customer: " + input.Name + " "); v_ResultInformation = v_ResultInformation.concat(" Phone: " + input.Phone_Number + " "); v_ResultInformation...

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

SQL Queries for Statisticshttps://joellipman.com/articles/database/mysql/sql-queries-for-statistics.html

13 December 2010 unique guests, students and staff by month. Also associates a UserID to the wikimedia_user table to identify a user. SELECT CONCAT(MONTHNAME(t2.Date), ' ', YEAR(t2.Date)) Month, SUM(IF(t2.VisitorType='Staff', 1, 0)) Staff,...

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

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

27 November 2012 Query but without using % (avoids MySQL Error 1065: Works within PHP scripts) SELECT calendar.WeekNumber AS AcademicWeek, CONCAT( DAYOFMONTH(calendar.StartDate), '-', SUBSTR(MONTHNAME(calendar.StartDate), 1, 3), '-', YEAR(calendar.StartDate) ) AS Mon,...

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

MySQL Group_concat equivalent in T-SQL and Oraclehttps://joellipman.com/articles/database/mysql-groupconcat-equivalent-in-t-sql-and-oracle.html

28 November 2011 1 Me,Myself,I Note that the following queries include the nested version because I find myself needing to group concatenate more often from another table then using data from the same table. MySQL SELECT ( SELECT GROUP_CONCAT(column_to_return_as_string)...

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

MySQL parameters in Excel 2007 PivotTableshttps://joellipman.com/articles/database/mysql/mysql-parameters-in-excel-2007-pivottables.html

24 August 2011 one with users names... Teams...another lookup table for just the name value... The one query to rule them all SELECT CONCAT(s.LogID, ' ') AS ActivityID, t.TeamName AS Team, CONCAT(u.Firstname, ' ', u.Lastname) AS StaffName, CASE...

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

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 @stringtoReplaceWith) AS newText, `content_column` as oldText FROM `MYTABLE_content` WHERE `content_column` LIKE CONCAT('%',@stringtoFind,'%') -- yields id title OldFoundInOld NewFoundInOld OldFoundInNew newText oldText 1 Article 1 yes no no Hello World...

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

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

12 September 2015 ( `parent_id`, `lft`, `rgt`, `level`, `name`, `title`, `rules`) SELECT @assetParentID, a.`lft`, a.`rgt`, a.`level`, CONCAT('com_content.article.', @nextAssetContentID:=@nextAssetContentID+1, ':|joe|:', c.`id`) AS new_name, a.`title`, a.`rules` FROM...

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

Returned a data type that is not validhttps://joellipman.com/articles/database/t-sql/returned-a-data-type-that-is-not-valid.html

06 January 2011 expression used in grouping 'table1_month' returned a data type that is not valid. My DataSet Query was as follows: SELECT CONCAT(MONTHNAME(t1.Date), ' ', YEAR(t1.Date)) Month, SUM(IF(t1.VisitorBrowser='MSIE', 1, 0)) 'IE',...

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

List MediaWiki Articleshttps://joellipman.com/articles/cms/mediawiki/list-mediawiki-articles.html

22 September 2011 AS PageID, CONVERT(p.page_title USING latin1) AS PageTitle, CONVERT(t.old_text USING latin1) AS PageContent, (SELECT GROUP_CONCAT(CONVERT(wikimedia_categorylinks.cl_to USING latin1)) FROM wikimedia_categorylinks WHERE...

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

Zoho Analytics: Determine profits from invoices and purchase ordershttps://joellipman.com/articles/crm/zoho/zoho-analytics-determine-profits-from-invoices-and-purchase-orders.html

03 November 2022 0) / if(iquantity."InvItm Line Count" = 0, 1, iquantity."InvItm Line Count"))) AS "Invoice Line Total inc Tax", GROUP_CONCAT(DISTINCT t1."PO Ref") AS "PO Refs", AVG(if_null(t1."PO Item Cost", 0)) AS "PO Item Cost", SUM(if_null(t1."PO Item Shipping", 0))...

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

Importing Joomla articles to WordPress postshttps://joellipman.com/articles/cms/wordpress/importing-joomla-articles-to-wordpress-posts.html

16 August 2011 INSERT INTO my_wordpress_db.wp_posts SELECT id 'ID', 1 'post_author', created 'post_date', created 'post_date_gmt', CONCAT(introtext, ' ', `fulltext`) 'post_content', title 'post_title', '' post_excerpt, CASE state WHEN '1' THEN 'publish' ELSE 'draft'...

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

MySQL: Display Users and Duration in Matrix Timesheethttps://joellipman.com/articles/database/mysql/mysql-display-users-and-duration-in-matrix-timesheet.html

27 April 2012 more of the work: User Query $t_value = $the_team_id_that_i_want_to_look_at; $user_list_query=" SELECT DISTINCT u.UserID, CONCAT(u.FirstName, ' ', u.LastName) AS 'Fullname' FROM Users u WHERE u.StatusType NOT IN ('expired','guest','test account') AND...

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

Search a database for a string (MySQL, T-SQL)https://joellipman.com/articles/database/search-a-database-for-a-string-mysql-t-sql.html

08 October 2012 DECLARE db,tbl,clmn CHAR(50); DECLARE done INT DEFAULT 0; DECLARE COUNTER INT; DECLARE table_cur CURSOR FOR SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM `',table_schema,'`.`',table_name,'` WHERE `', column_name,'` REGEXP "',in_search,'"')...

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

MySQL Commands to display all columnshttps://joellipman.com/articles/database/mysql/mysql-commands-to-display-all-columns.html

28 October 2010 query that finds all columns (displayed as "tablename.columnname") that had the data_type TEXT across all databases: SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' ORDER BY TABLE_NAME,...

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

MediaWiki: MySQL to extract path to imageshttps://joellipman.com/articles/cms/mediawiki/mediawiki-mysql-to-extract-path-to-images.html

06 December 2011 that the paths are determined using the MD5 Hash of the filename: select img_name, img_size, img_user_text, img_timestamp, CONCAT(SUBSTR(MD5(img_name), 1, 1), '/', SUBSTR(MD5(img_name), 1, 2)) AS img_path, CONCAT(SUBSTR(MD5(img_name), 1, 1), '/',...

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

Cheat Sheet for mySQL vs t-SQLhttps://joellipman.com/articles/database/cheat-sheet-for-mysql-vs-t-sql.html

31 January 2013 needle) LOCATE(needle, haystack [, offset]) PATINDEX(%needle%, haystack) CHARINDEX(needle, haystack [, offset]) String Concatenation CONCAT(string1,string2[,stringN]) string1 + string2 SS v2012+: CONCAT(string1, string2 [, stringN ]) String Substring...

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

URL Alias uniqueness with PHP & MySQLhttps://joellipman.com/articles/web-development/php/url-alias-uniqueness-with-php-mysql.html

06 May 2019 set url_counter = 1; while exists (select true from my_table_name where url_alias = new.url_alias) do set new.url_alias = concat(original_url, '_', url_counter); set url_counter = url_counter + 1; end while; END; | DELIMITER ; Note that running this in...

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