<pre>
<?php
/**
 * J2.5 to J4 Article Migration Script
 *
 * It migrates categories table, articles table, and related assets table (not the whole assets table).
 * Users are not migrated. Use $admin_user_id to set the author id.
 * Some article metadata, images, and attributes are ignored at the moment.
 */

$hostname = "localhost";
$user = "root";
$pass = "";

$source = array(
    "dbname" => "migration_source",
    "prefix" => "hkzl8"
);
$target = array(
    "dbname" => "migration_target",
    "prefix" => "c6oeb"
);

// article authors are not migrated. All articles will have the same author.
$admin_user_id = "666";

// Assets will be stored here to be used in INSERT queries of Categories and Articles
$ASSETS = [];
// Categories will be stored here to be used in INSERT queries of Articles
$CATEGORIES = [];
// Articles will be stored here to be used in INSERT in workflow tables
$ARTICLES = [];

try {

    $db_source = new PDO ("mysql:host=$hostname;dbname=" . $source["dbname"], $user, $pass);
    $db_target = new PDO ("mysql:host=$hostname;dbname=" . $target["dbname"], $user, $pass);


    /**
     *  --------------- get and push category and article assets -----------------------------
     */

    echo "source: select assets";
    // skip "Uncategorised" category
    $q = "SELECT * FROM `" . $source["prefix"] . "_assets` WHERE (name LIKE \"com_content.article%\" OR `name` LIKE \"com_content.category%\")  AND `name` != \"com_content.category.2\";";
    $stmt = $db_source->query($q);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach($rows as $row) {
        $q = insertAssetQuery($row);

        $name = $row["name"];
        echo "\ntarget: insert asset name " . $row["name"];
        $stmt = $db_target->prepare($q);
        // safe escape
        $stmt->bindValue(':title', $row["title"], PDO::PARAM_STR);

        $stmt->execute();

        /**
         * after insertion, get the asset from the target DB to get the asset ID
         */
        echo "\ntarget: select asset name" . $row["name"];
        $stmt = $db_target->query("SELECT * FROM `" . $target["prefix"] . "_assets` WHERE `name` = '".$name."'");
        if ($stmt->rowCount() > 1) {
            throw new Exception("More than 1 asset found with name " . $name);
        }
        if ($stmt->rowCount() <= 0) {
            throw new Exception("No asset found with name " . $name);
        }

        $selected = $stmt->fetch(PDO::FETCH_ASSOC);
        $ASSETS[] = $selected;
    }

    echo "\nop: " . sizeof($rows) . " assets added.";

    // fetch "Uncategorised" Category asset to get the Asset Id in the target db.
    $stmt = $db_target->query("SELECT * FROM `" . $target["prefix"] . "_assets` WHERE `name` LIKE \"com_content.%\" AND `title` = \"Uncategorised\"");
    $uncategorised_asset = $stmt->fetch(PDO::FETCH_ASSOC);
    $ASSETS[] = $uncategorised_asset;


    /**
     *  --------------- get and push categories -----------------------------
     */

    echo "\nsource: select categories ";
    $stmt = $db_source->query("SELECT * FROM `" . $source["prefix"] . "_categories` WHERE `extension` = \"com_content\" AND `alias` != \"uncategorised\"");
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach($rows as $row) {
        $q = insertCatQuery($row);
        echo "\ntarget: insert category id " . $row["id"];
        $stmt = $db_target->prepare($q);

        // safe escape
        $stmt->bindValue(':title', $row["title"], PDO::PARAM_STR);
        $stmt->bindValue(':description', $row["description"], PDO::PARAM_STR);
        $stmt->execute();

        /**
         * after insertion, get the category from the target DB to get the category ID
         */
        echo "\ntarget: select category alias " . $row["alias"];
        $stmt = $db_target->query("SELECT * FROM `" . $target["prefix"] . "_categories` WHERE `alias` = '". $row["alias"] ."'");
        $selected = $stmt->fetch(PDO::FETCH_ASSOC);
        $CATEGORIES[] = $selected;

    }
    echo "\nop: " . sizeof($rows) . " categories added.";

    /**
     * map "Uncategorised" ID from source db to target db.
     * "Uncategorised" should have ID=2, the following code is just to be sure.
     */
    // fetch "Uncategorised" Category asset in source db
    $stmt = $db_source->query("SELECT * FROM `" . $source["prefix"] . "_categories` WHERE `extension` = \"com_content\" AND `title` = \"Uncategorised\"");
    $source_uncategorised_cat = $stmt->fetch(PDO::FETCH_ASSOC);

    // fetch "Uncategorised" Category asset to get the Cat Id in the target db.
    $stmt = $db_target->query("SELECT * FROM `" . $target["prefix"] . "_categories` WHERE `extension` = \"com_content\" AND `title` = \"Uncategorised\"");
    $uncategorised_cat = $stmt->fetch(PDO::FETCH_ASSOC);
    $CATEGORIES[] = $uncategorised_cat;



    /**
     * --------------- get and push articles -----------------------------
     */

    echo "\nsource: select articles";
    $stmt = $db_source->query("SELECT * FROM `" . $source["prefix"] . "_content`");
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // TODO category id is wrong here! (it comes from source db while I need the id in the target)
    foreach($rows as $row){
        unset($row["title_alias"]);
        unset($row["mask"]);
        unset($row["sectionid"]);
        unset($row["xreference"]);
        $q = insertArticleQuery($row);
        echo "\ntarget: insert article with (source) id " . $row["id"];
        $stmt = $db_target->prepare($q);

        // safe escape
        $stmt->bindValue(':title', $row["title"], PDO::PARAM_STR);
        $stmt->bindValue(':introtext', $row["introtext"], PDO::PARAM_STR);
        $stmt->bindValue(':fulltext', $row["fulltext"], PDO::PARAM_STR);

        $stmt->execute();

        /**
         * after insertion, get the category from the target DB to get the category ID
         */
        echo "\ntarget: select article alias" . $row["alias"];
        $stmt = $db_target->query("SELECT * FROM `" . $target["prefix"] . "_content` WHERE `alias` = '". $row["alias"] ."'");
        $selected = $stmt->fetch(PDO::FETCH_ASSOC);
        $ARTICLES[] = $selected;
        $q = insertWorkflowQuery($selected);
        $stmt = $db_target->prepare($q);
        $stmt->execute();
    }
    echo "\nop: " . sizeof($rows) . " articles added.";
    echo "\nop: " . sizeof($rows) . " workflow entries added.";


} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
    var_dump(debug_backtrace());
    die();
}

function insertAssetQuery($row) {
    GLOBAL $target;
    return "INSERT INTO " . $target["prefix"] ."_assets(
    `parent_id`,
    `lft`,
    `rgt`,
    `level`,
    `name`,
    `title`,
    `rules`) VALUES(
                '" . $row["parent_id"] . "',
                '" . $row["lft"] . "',
                '" . $row["rgt"] . "',
                '" . $row["level"] . "',
                '" . $row["name"] . "',
                :title,
                '" . $row["rules"] . "')";
}

function insertCatQuery($row) {
    GLOBAL $target;
    GLOBAL $admin_user_id;
    GLOBAL $ASSETS;
    $asset_id = getIdByTitle($row["title"], $ASSETS);

    return "INSERT INTO `" . $target["prefix"] ."_categories`(
        `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`) VALUES(
                '". $asset_id . "',
                '" . $row["parent_id"] . "',
                '" . $row["lft"] . "',
                '" . $row["rgt"] . "',
                '" . $row["level"] . "',
                '" . $row["path"] . "',
                '" . $row["extension"] . "',
                :title,
                '" . $row["alias"] . "',
                '" . $row["note"] . "',
                :description,
                '" . $row["published"] . "',
                '" . $row["checked_out"] . "',
                '" . $row["checked_out_time"] . "',
                '" . $row["access"] . "',
                '" . $row["params"] . "',
                '" . $row["metadesc"] . "',
                '" . $row["metakey"] . "',
                '" . $row["metadata"] . "',
                '" . $admin_user_id . "',
                '" . $row["created_time"] . "',
                '" . $admin_user_id . "',
                '" . $row["modified_time"] . "',
                '" . $row["hits"] . "',
                '" . $row["language"] . "',
                '1')";
}

function insertArticleQuery($row): string {
    GLOBAL $target;
    GLOBAL $admin_user_id;
    GLOBAL $ASSETS;
    GLOBAL $CATEGORIES;
    GLOBAL $source_uncategorised_cat;

    $asset_id = getIdByTitle($row["title"], $ASSETS);
    $category_id = getIdByTitle("Uncategorised", $CATEGORIES);

    return "INSERT INTO `" . $target["prefix"] ."_content`(
        `id`,
        `asset_id`,
        `title`,
        `alias`,
        `introtext`,
        `fulltext`,
        `state`,
        `catid`,
        `created`,
        `created_by`,
        `created_by_alias`,
        `modified`,
        `modified_by`,
        `checked_out`,
        `checked_out_time`,
        `publish_up`,
        `publish_down`,
        `images`,
        `urls`,
        `attribs`,
        `version`,
        `ordering`,
        `metakey`,
        `metadesc`,
        `access`,
        `hits`,
        `metadata`,
        `featured`,
        `language`,
        `note`
        ) VALUES(
                NULL,
                '". $asset_id . "',
                :title,
                '" . $row["alias"] . "',
                :introtext,
                :fulltext,
                '" . $row["state"] . "',
                '" . $row["catid"] . "',
                '" . $row["created"] . "',
                '" . $admin_user_id . "',
                '" . $row["created_by_alias"] . "',
                '" . $row["modified"] . "',
                '" . $admin_user_id . "',
                '" . $admin_user_id . "',
                '" . $row["checked_out_time"] . "',
                '" . $row["publish_up"] . "',
                '" . $row["publish_down"] . "',
                '{\"image_intro\":\"\",\"image_intro_alt\":\"\",\"float_intro\":\"\",\"image_intro_caption\":\"\",\"image_fulltext\":\"\",\"image_fulltext_alt\":\"\",\"float_fulltext\":\"\",\"image_fulltext_caption\":\"\"}',
                '{\"urla\":\"\",\"urlatext\":\"\",\"targeta\":\"\",\"urlb\":\"\",\"urlbtext\":\"\",\"targetb\":\"\",\"urlc\":\"\",\"urlctext\":\"\",\"targetc\":\"\"}',
                '{\"article_layout\":\"\",\"show_title\":\"\",\"link_titles\":\"\",\"show_tags\":\"\",\"show_intro\":\"\",\"info_block_position\":\"\",\"info_block_show_title\":\"\",\"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_hits\":\"\",\"show_noauth\":\"\",\"urls_position\":\"\",\"alternative_readmore\":\"\",\"article_page_title\":\"\",\"show_publishing_options\":\"\",\"show_article_options\":\"\",\"show_urls_images_backend\":\"\",\"show_urls_images_frontend\":\"\"}',
                '1',
                '" . $row["ordering"] . "',
                '" . $row["metakey"] . "',
                '" . $row["metadesc"] . "',
                '" . $row["access"] . "',
                '" . $row["hits"] . "',
                '{\"robots\":\"\",\"author\":\"\",\"rights\":\"\"}',
                '" . $row["featured"] . "',
                '*',
                ''
                )";
}

function insertWorkflowQuery($row): string {
    GLOBAL $target;
    GLOBAL $admin_user_id;
    GLOBAL $ASSETS;
    GLOBAL $CATEGORIES;
    GLOBAL $ARTICLES;
    GLOBAL $source_uncategorised_cat;

    return "INSERT INTO `" . $target["prefix"] . "_workflow_associations` (
        `item_id`,
        `stage_id`,
        `extension`) VALUES (
        '". $row["id"] . "',
        '1',
        'com_content.article');";
}

/**
 * This is used to search into $ASSETS and $CATEGORIES
 *
 */
function getIdByTitle($title, $data) {
    foreach ($data as $el) {
        if ($el["title"] == $title) {
            return $el["id"];
        }
    }
    throw new Exception("Element with title " . $title . " not found.");
}

/**
 * escape double quote and quote
 */
function escape($str) {
    return htmlspecialchars($str, ENT_QUOTES);
}