K2 Article Import Tutorial for Joomla Content

This SQL code snippet performs two operations:

Deletes all rows from the "dyem7_content" table where the id is greater than or equal to 15.

Alters the auto_increment value of the "dyem7_content" table to 15.

/*
    This SQL code snippet performs two operations:
    1. Deletes all rows from the "dyem7_content" table where the id is greater than or equal to 15.
    2. Alters the auto_increment value of the "dyem7_content" table to 15.
*/
DELETE FROM dyem7_content WHERE id >= 15;
ALTER TABLE dyem7_content AUTO_INCREMENT = 15;

Selects records from the dyem7_k2_items table and assigns specific values to each selected column.

/*
    Selects records from the dyem7_k2_items table and assigns specific values to each selected column.
    The result is a temporary table containing columns title, alias, introtext, fulltext, state, created, created_by, created_by_alias, modified, modified_by, checked_out, attribs, publish_up, images, urls, version, ordering, metakey, metadesc, access, hits, metadata, featured, language, and note.
    Records are ordered by id.
    The category for each item is set to 2.
*/
SELECT 
    title,
    alias,
    introtext,
    2 as catid,
    `fulltext`,
    CASE 
        WHEN published = 0 THEN 0
        WHEN trash = 1 THEN -2
        ELSE 1
    END as state,
    created,
    created_by,
    created_by_alias,
    CASE modified
        WHEN '0000-00-00 00:00:00' THEN created
        ELSE modified
    END as modified,
    modified_by,
    checked_out,
    '{"show_title":"","link_titles":"","show_intro":"","show_category":"","link_category":"","show_parent_category":"","link_parent_category":"","show_author":"","link_author":"","show_create_date":"","show_modify_date":"","show_publish_date":"","show_item_navigation":"","show_icons":"","show_print_icon":"","show_email_icon":"","show_vote":"","show_hits":"","show_noauth":"","alternative_readmore":"","article_layout":""}' AS attribs,
    publish_up,
    '' as images,
    '' as urls,
    1 as version,
    ordering,
    metakey,
    metadesc,
    access,
    hits,
    metadata,
    featured,
    language,
    '' as note
FROM `dyem7_k2_items`
ORDER BY id;

Selects records from the dyem7_content table to create a temporary table with specific values.

/*
    Selects records from the dyem7_content table where id is greater than or equal to 15, ordered by id.
    Then assigns specific values to each selected column.
    The result is a temporary table containing columns parent_id, lft, rgt, level, name, title, and rules.
*/
SELECT 
    27 AS parent_id,
    @row_number := @row_number + 1 AS lft,
    @row_number := @row_number + 1 AS rgt,
    3 AS level,
    CONCAT('com_content.article.', id) AS name,
    title AS title,
    '{}' AS rules
FROM (
    SELECT * FROM dyem7_content WHERE id >= 15 ORDER BY id ASC
) AS articles, (SELECT @row_number := 0) AS init;

Updates the asset_id field of the dyem7_content table with corresponding values from the dyem7_assets table.

/*
    Updates the asset_id field of the dyem7_content table with corresponding values from the dyem7_assets table.
    The update is performed for records in the dyem7_content table where id is greater than or equal to 15.
    The matching between tables is done through the name field of the dyem7_assets table, which should be equal to CONCAT('com_content.article.', c.id),
    where c is an alias for the dyem7_content table.
*/
UPDATE dyem7_content AS c
JOIN dyem7_assets AS a ON CONCAT('com_content.article.', c.id) = a.name
SET c.asset_id = a.id
WHERE c.id >= 15;

Updates workflows for existing articles with id >= 15.

/*
    Updates workflows for existing articles with id >= 15.
*/
INSERT INTO `dyem7_workflow_associations` (`item_id`, `stage_id`, `extension`)
SELECT id, 1, 'com_content.article'
FROM dyem7_content
WHERE id >= 15;

Inserts K2 categories into the Joomla categories table, starting from the maximum existing ID plus one.

/*
    Step 1: Get the maximum ID from the Joomla categories table
*/
SET @max_id = (SELECT MAX(id) FROM dyem7_categories);

/*
    Step 2: Increment the max ID to use as starting ID for new entries
*/
SET @new_id = @max_id + 1;

/*
    Step 3: Insert the K2 categories into the Joomla categories table
*/
INSERT INTO dyem7_categories (id, asset_id, parent_id, lft, rgt, level, path, extension, title, alias, note, description, published, checked_out, checked_out_time, access, params, metadesc, metakey, metadata, created_user_id, created_time, modified_user_id, modified_time, hits, language, version)
SELECT 
    @new_id := @new_id + 1 AS id,
    0 as asset_id,
    parent as parent_id,
      
    0 as lft,
    0 as rgt,
    0 as level,
    alias as path,  
	 'com_content' AS extension,
    name as title,
    alias,
    '' as note,
    description,
    CASE 
        WHEN published = 0 THEN 0
        WHEN trash = 1 THEN -2
        ELSE 1
    END AS published ,     
    NULL as checked_out,
    -- now as checked_out_time,
    now() as checked_out_time,
    access,
    '{"category_layout":"","image":"","image_alt":""}' as params,
    '' as metadesc,
    '' as metakey,
    '{"author":"","robots":""}' as metadata,
    0 as created_user_id,
    now() as created_time,
    0 as modified_user_id,
    now() as modified_time,
    0 as hits,
    language,
    1 as version
FROM dyem7_k2_categories
ORDER BY parent, ordering;