Kunena 6.3.6 Released - Security release

The Kunena team has announce the arrival of Kunena 6.3.6 [K 6.3.6] in stable which is now available for download as a native Joomla extension for J! 4.4.x/5.0.x/5.1.x. This version addresses most of the issues that were discovered in K 6.2 / K 6.3 and issues discovered during the last development stages of K 6.3
Note: Please go to the Kunena Dashboard after an upgrade so that the Kunena database tables are also updated.

This category contains miscellaneous, uncategorised user contributions, (templates, modules, plugins and hacks) relating to older versions of Kunena that are no longer supported.

The topics in this category are for historical interest only. Owing to the structural changes that occurred in K 2.0, many of the ideas in these topics will not work with later versions and, for that reason, the topics are locked.

Loved converter - project Agora 3.0 to Kunena 2.0 - version 0.2

More
11 years 10 months ago - 11 years 10 months ago #1 by rgrae81
Hi,

I had to convert some Topics and Messages from Agora to Kunena, but there's nearly nothing :-)
I decided to write a little PHP file for converting everything I need for myself.

Now I would like to share my work with the community. Maybe some users would complete it to a fully functional Converter.


First of all, synchronize the Joomla-Users.
Then you have to create the categories and subcategories lige in your Agora-Forum.
Follow the instructions in the code to link forums to categories
Upload the saved php-file to your webspace and open it in your browser.
After everything is done, recount statistics under Kunena-Tools

Happy converting :-)
regards
Roman

!!! Updated Version - 0.2 !!!

Changes:
- add functionality to import agora-categories and forums with standard permissions
- automattically maps new category-id with old agora forum id

Convertion-Steps:
First of all, synchronize the Joomla-Users.
Upload the saved php-file to your webspace and open it in your browser.
After everything is done, press "recount statistics" under Kunena-Tools
Code:
<?php /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// // AGORA 3.0.x to Kunena 2.0.x converter ////////////////////////////////////////////////////////////////////////////////////// // Converter version 0.2 ////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //enter your database credentials here. $host = "localhost"; $user = ""; $pass = ""; $db = ""; $link = mysql_connect($host,$user,$pass) or die(mysql_error()); mysql_selectdb($db,$link) or die(mysql_error()); $ag_forums_to_kunena_categories = array(); /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// // converting Agora categories to Kunena categories using original category-ID //////////////////////////////////////////////// mysql_query("TRUNCATE j25_kunena_categories"); $sql = " SELECT c.id AS categories_id, c.cat_name AS categories_name, c.disp_position AS categories_ordering, c.enable AS categories_published FROM jos_agora_categories AS c ORDER BY c.disp_position ASC "; $res = mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_object($res)) { echo $row->c.id; echo "<br />"; $sql = "INSERT INTO `j25_kunena_categories` VALUES (".$row->categories_id.", 0, '".mysql_real_escape_string($row->categories_name)."', '', 0, 0, 'joomla.group', 2, 2, 1, 8, 0, ".$row->categories_ordering.", ".$row->categories_published.", NULL, 0, '0000-00-00 00:00:00', 0, 0, 0, 0, '', '', '', 0, 'lastpost', 0, 0, 0, 0, 0, '{}')"; echo $sql; echo "<br />"; mysql_query($sql) or die(mysql_error()); } /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// // converting Agora Forums to Kunena categories using modified category-ID //////////////////////////////////////////////////// // first of all count how many agory categories are here. Start catid plus 1 for Forums /////////////////////////////////////// $sqlnum = "SELECT * FROM jos_agora_categories"; $resnum = mysql_query($sqlnum); $rows = mysql_num_rows($resnum); $cat_mod_id=$rows+1; //////////////////////////////////////////////// $sql = " SELECT f.id AS categories_id, f.enable AS categories_published, f.forum_name AS categories_name, f.forum_desc AS categories_description, f.num_topics AS categories_numTopics, f.num_posts AS categories_numPosts, f.last_post AS categories_last_post_time, f.last_post_id AS categories_last_post_id, f.disp_position AS categories_ordering, f.cat_id AS categories_parent_id, f.closed AS categories_locked FROM jos_agora_forums AS f ORDER BY f.disp_position ASC "; $res = mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_object($res)) { echo $cat_mod_id; echo "<br />"; if($row->categories_last_post_time == '') { $row->categories_last_post_time = 0; $row->categories_last_post_id = 0; } echo $row->categories_last_post_time; echo "<br />"; $sql = "INSERT INTO `j25_kunena_categories` VALUES (".$cat_mod_id.", ".$row->categories_parent_id.", '".mysql_real_escape_string($row->categories_name)."', '', 0, 0, 'joomla.group', 1, 2, 1, 8, 0, ".$row->categories_ordering.", 1, NULL, 0, '0000-00-00 00:00:00', 0, 0, 0, 0, '".mysql_real_escape_string($row->categories_description)."', '', '', 1, 'lastpost', ".$row->categories_numTopics.", ".$row->categories_numPosts.", 0, ".$row->categories_last_post_id.", ".$row->categories_last_post_time.", '{\"access_post\":[\"6\",\"7\",\"2\",\"3\",\"4\",\"5\",\"8\"],\"access_reply\":[\"6\",\"7\",\"2\",\"3\",\"4\",\"5\",\"8\"]}')"; echo $sql; echo "<br />"; mysql_query($sql) or die(mysql_error()); $ag_forums_to_kunena_categories [$row->categories_id] = $cat_mod_id; $cat_mod_id++; } /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// echo "ARRAY:"; echo "<br />"; print_r($ag_forums_to_kunena_categories); /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// // convert topics from Agora to Kunena //////////////////////////////////////////////////////////////////////////////////////// mysql_query("TRUNCATE j25_kunena_topics"); $sql = " SELECT t.id AS topic_id, t.poster AS topic_poster, t.subject AS topic_subject, t.posted AS topic_first_post_time, t.last_post AS topic_last_post_time, t.last_post_id AS topic_last_post_id, t.last_poster AS topic_last_poster, t.num_views AS topic_hits, t.num_replies AS topic_posts, t.closed AS topic_locked, t.sticky AS topic_hold, t.moved_to AS topic_moved_id, t.forum_id AS topic_category_id, t.question AS topic_poll_id, p.id AS topic_first_post_id, p.posted AS topic_first_post_time, a.jos_id AS topic_first_post_userid, p.message AS topic_first_post_message FROM jos_agora_topics AS t LEFT JOIN jos_agora_users AS a ON a.username = t.poster LEFT JOIN jos_agora_posts AS p ON t.posted = p.posted ORDER BY t.id ASC "; $res = mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_object($res)) { echo $row->topic_id; echo "<br />"; if($row->topic_first_post_userid == '') { $row->topic_first_post_userid = 0; } $cat_id = $ag_forums_to_kunena_categories[$row->topic_category_id]; echo $cat_id; echo "<br />"; $sql = "INSERT INTO `j25_kunena_topics` VALUES (".$row->topic_id.", ".$cat_id.", '".mysql_escape_string($row->topic_subject)."', 0, ".$row->topic_locked.", ".$row->topic_hold.", 0, ".$row->topic_posts.", ".$row->topic_hits.", 0, 0, 0, ".$row->topic_first_post_id.", ".$row->topic_first_post_time.", ".$row->topic_first_post_userid.", '".mysql_escape_string($row->topic_first_post_message)."', NULL, ".$row->topic_last_post_id.", ".$row->topic_last_post_time.", 0, NULL, NULL, '')"; echo $sql; echo "<br />"; mysql_query($sql) or die(mysql_error()); } $sql = " SELECT t.id AS topic_id, t.last_post AS topic_last_post_time, t.last_post_id AS topic_last_post_id, t.last_poster AS topic_last_poster, a.jos_id AS topic_last_post_userid, p.message AS topic_last_post_message FROM jos_agora_topics AS t LEFT JOIN jos_agora_users AS a ON a.username = t.last_poster LEFT JOIN jos_agora_posts AS p ON t.last_post_id = p.id ORDER BY t.id ASC "; $res = mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_object($res)) { echo $row->topic_id; echo "<br />"; $topic_last_post_message = mysql_real_escape_string($row->topic_last_post_message); if($row->topic_last_post_userid == '') { $row->topic_last_post_userid = 0; } $cat_id = $ag_forums_to_kunena_categories[$row->topic_category_id]; echo $cat_id; echo "<br />"; $sql = "UPDATE `j25_kunena_topics` SET `last_post_id` = ".$row->topic_last_post_id.",`last_post_time` = ".$row->topic_last_post_time.",`last_post_userid` = ".$row->topic_last_post_userid.",`last_post_message` = '".mysql_escape_string($row->topic_last_post_message)."',`last_post_guest_name` = NULL,`params` = '' WHERE `j25_kunena_topics`.`id` = ".$row->topic_id.""; echo $sql; echo "<br />"; mysql_query($sql) or die(mysql_error()); } /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// // convert Posts from Agora tu Kunena ///////////////////////////////////////////////////////////////////////////////////////// mysql_query("TRUNCATE j25_kunena_messages"); mysql_query("TRUNCATE j25_kunena_messages_text"); $sql = " SELECT p.id AS messages_id, u.name AS messages_name, a.jos_id AS messages_userid, p.message AS messages_text_message, p.posted AS messages_time, p.edited AS messages_modified_time, p.edited_by AS messages_modified_by, p.topic_id AS messages_thread, t.forum_id AS messages_catid FROM jos_agora_posts AS p LEFT JOIN jos_agora_users AS a ON a.username = p.poster LEFT JOIN jos_agora_topics AS t ON t.id = p.topic_id LEFT JOIN jos_users AS u ON u.id = a.jos_id ORDER BY p.id ASC "; $res = mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_object($res)) { echo $row->messages_id; echo "<br />"; if($row->messages_userid == '') { $row->messages_userid = 0; } $cat_id = $ag_forums_to_kunena_categories[$row->messages_catid]; if ($cat_id != 0) { echo $cat_id; echo "<br />"; $sql = "INSERT INTO `j25_kunena_messages` VALUES (".$row->messages_id.", 0, ".$row->messages_thread.", ".$cat_id.", '".$row->messages_name."', ".$row->messages_userid.", '', '', ".$row->messages_time.", NULL, 0, 0, 0, 0, 0, 0, 0, 0, '')"; $sql2 = "INSERT INTO `j25_kunena_messages_text` VALUES (".$row->messages_id.", '".mysql_escape_string($row->messages_text_message)."')"; echo $sql; echo "<br />"; echo $sql2; echo "<br />"; mysql_query($sql) or die(mysql_error()); mysql_query($sql2) or die(mysql_error()); } } mysql_close();
Last edit: 11 years 10 months ago by rgrae81.
The following user(s) said Thank You: Jiminimonka, silentz0r

Please Log in or Create an account to join the conversation.

More
11 years 10 months ago #2 by rgrae81
New Version 0.2 of the convertion Script.
for further informations read the first Post!

regards
Roman

Please Log in or Create an account to join the conversation.

More
11 years 8 months ago - 11 years 8 months ago #3 by silentz0r
Hey, thanks for a great script. However there is a problem with it:

Agora uses Categories, and each category can have many Forums (jos_agora_category and jos_agora_forum MySQL tables). However, Kunena only uses categories (j25_kunena_category MySQL table) and on line 36 you are inserting all jos_agora_category to j25_kunena_category, but on line 81 you also insert jos_agora_forum into j25_kunena_category, which for me at least results in an error "Duplicate entry '6' for key 'PRIMARY'" , because forums and categories have separate ID's on Agora.

I am not sure if this is the way it is supposed to work (insert categories and forums into categories). In Agora every Category has an ID, and every forum has a foreign_key called cat_id which points to the Category it belongs. How do categories and subcategories work in kunena?

In my Agora forum I have 5 Categories with IDs 1-5, and 15 Forums with IDs 2-16, and I get an error about a duplicate entry primary key with ID of 6. So each of the subcategories is supposed to have a parent_id of the agora category. It seems like the Category ID is not incrementing near line 81 for the next iteration, so it tries to create a new category with the same ID as the previous category.

So, basically at lines 46-49 you are counting how many categories there are and you assume they have the same id, but you should instead check for the largest ID. My forums got IDs from 2-6 for some reason, which broke everything.

Here is my code to replace lines 46-49, so that it reads the max id from the current categories and sets it as cat_mod_id (replace the code from line 46):
Code:
$sqlnum = "SELECT * FROM jos_agora_categories"; $resnum = mysql_query($sqlnum); $rows = mysql_num_rows($resnum); $maxidquery = "SELECT id FROM jos_agora_categories ORDER BY id DESC LIMIT 0,1"; $resmax = mysql_query($maxidquery); $resmaxid = mysql_fetch_array($resmax); $maxid = $resmaxid['id']; $cat_mod_id=$maxid+1;

If you want to, you can also check if $rows is different than $max_id and then decide which one to use, however I think you really need to know the actual maximum category id since it is AUTO_INCREMENT as well, and having a duplicate key will break things!

--EDIT--
So here is my version of the code with my fix implemented:
Code:
<?php /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// // AGORA 3.0.x to Kunena 2.0.x converter ////////////////////////////////////////////////////////////////////////////////////// // Converter version 0.2.1 ////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //enter your database credentials here. $host = "localhost"; $user = ""; $pass = ""; $db = ""; $link = mysql_connect($host,$user,$pass) or die(mysql_error()); mysql_selectdb($db,$link) or die(mysql_error()); $ag_forums_to_kunena_categories = array(); /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// // converting Agora categories to Kunena categories using original category-ID //////////////////////////////////////////////// mysql_query("TRUNCATE j25_kunena_categories"); $sql = " SELECT c.id AS categories_id, c.cat_name AS categories_name, c.disp_position AS categories_ordering, c.enable AS categories_published FROM jos_agora_categories AS c ORDER BY c.disp_position ASC "; $res = mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_object($res)) { echo $row->c.id; echo "<br />"; $sql = "INSERT INTO `j25_kunena_categories` VALUES (".$row->categories_id.", 0, '".mysql_real_escape_string($row->categories_name)."', '', 0, 0, 'joomla.group', 2, 2, 1, 8, 0, ".$row->categories_ordering.", ".$row->categories_published.", NULL, 0, '0000-00-00 00:00:00', 0, 0, 0, 0, '', '', '', 0, 'lastpost', 0, 0, 0, 0, 0, '{}')"; echo $sql; echo "<br />"; mysql_query($sql) or die(mysql_error()); } /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// // converting Agora Forums to Kunena categories using modified category-ID //////////////////////////////////////////////////// // first of all count how many agory categories are here. Start catid plus 1 for Forums /////////////////////////////////////// $sqlnum = "SELECT * FROM jos_agora_categories"; $resnum = mysql_query($sqlnum); $rows = mysql_num_rows($resnum); $maxidquery = "SELECT id FROM jos_agora_categories ORDER BY id DESC LIMIT 0,1"; $resmax = mysql_query($maxidquery); $resmaxid = mysql_fetch_array($resmax); $maxid = $resmaxid['id']; $cat_mod_id=$maxid+1; //////////////////////////////////////////////// $sql = " SELECT f.id AS categories_id, f.enable AS categories_published, f.forum_name AS categories_name, f.forum_desc AS categories_description, f.num_topics AS categories_numTopics, f.num_posts AS categories_numPosts, f.last_post AS categories_last_post_time, f.last_post_id AS categories_last_post_id, f.disp_position AS categories_ordering, f.cat_id AS categories_parent_id, f.closed AS categories_locked FROM jos_agora_forums AS f ORDER BY f.disp_position ASC "; $res = mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_object($res)) { echo $cat_mod_id; echo "<br />"; if($row->categories_last_post_time == '') { $row->categories_last_post_time = 0; $row->categories_last_post_id = 0; } echo $row->categories_last_post_time; echo "<br />"; $sql = "INSERT INTO `j25_kunena_categories` VALUES (".$cat_mod_id.", ".$row->categories_parent_id.", '".mysql_real_escape_string($row->categories_name)."', '', 0, 0, 'joomla.group', 1, 2, 1, 8, 0, ".$row->categories_ordering.", 1, NULL, 0, '0000-00-00 00:00:00', 0, 0, 0, 0, '".mysql_real_escape_string($row->categories_description)."', '', '', 1, 'lastpost', ".$row->categories_numTopics.", ".$row->categories_numPosts.", 0, ".$row->categories_last_post_id.", ".$row->categories_last_post_time.", '{\"access_post\":[\"6\",\"7\",\"2\",\"3\",\"4\",\"5\",\"8\"],\"access_reply\":[\"6\",\"7\",\"2\",\"3\",\"4\",\"5\",\"8\"]}')"; echo $sql; echo "<br />"; mysql_query($sql) or die(mysql_error()); $ag_forums_to_kunena_categories [$row->categories_id] = $cat_mod_id; $cat_mod_id++; } /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// echo "ARRAY:"; echo "<br />"; print_r($ag_forums_to_kunena_categories); /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// // convert topics from Agora to Kunena //////////////////////////////////////////////////////////////////////////////////////// mysql_query("TRUNCATE j25_kunena_topics"); $sql = " SELECT t.id AS topic_id, t.poster AS topic_poster, t.subject AS topic_subject, t.posted AS topic_first_post_time, t.last_post AS topic_last_post_time, t.last_post_id AS topic_last_post_id, t.last_poster AS topic_last_poster, t.num_views AS topic_hits, t.num_replies AS topic_posts, t.closed AS topic_locked, t.sticky AS topic_hold, t.moved_to AS topic_moved_id, t.forum_id AS topic_category_id, t.question AS topic_poll_id, p.id AS topic_first_post_id, p.posted AS topic_first_post_time, a.jos_id AS topic_first_post_userid, p.message AS topic_first_post_message FROM jos_agora_topics AS t LEFT JOIN jos_agora_users AS a ON a.username = t.poster LEFT JOIN jos_agora_posts AS p ON t.posted = p.posted ORDER BY t.id ASC "; $res = mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_object($res)) { echo $row->topic_id; echo "<br />"; if($row->topic_first_post_userid == '') { $row->topic_first_post_userid = 0; } $cat_id = $ag_forums_to_kunena_categories[$row->topic_category_id]; echo $cat_id; echo "<br />"; $sql = "INSERT INTO `j25_kunena_topics` VALUES (".$row->topic_id.", ".$cat_id.", '".mysql_escape_string($row->topic_subject)."', 0, ".$row->topic_locked.", ".$row->topic_hold.", 0, ".$row->topic_posts.", ".$row->topic_hits.", 0, 0, 0, ".$row->topic_first_post_id.", ".$row->topic_first_post_time.", ".$row->topic_first_post_userid.", '".mysql_escape_string($row->topic_first_post_message)."', NULL, ".$row->topic_last_post_id.", ".$row->topic_last_post_time.", 0, NULL, NULL, '')"; echo $sql; echo "<br />"; mysql_query($sql) or die(mysql_error()); } $sql = " SELECT t.id AS topic_id, t.last_post AS topic_last_post_time, t.last_post_id AS topic_last_post_id, t.last_poster AS topic_last_poster, a.jos_id AS topic_last_post_userid, p.message AS topic_last_post_message FROM jos_agora_topics AS t LEFT JOIN jos_agora_users AS a ON a.username = t.last_poster LEFT JOIN jos_agora_posts AS p ON t.last_post_id = p.id ORDER BY t.id ASC "; $res = mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_object($res)) { echo $row->topic_id; echo "<br />"; $topic_last_post_message = mysql_real_escape_string($row->topic_last_post_message); if($row->topic_last_post_userid == '') { $row->topic_last_post_userid = 0; } $cat_id = $ag_forums_to_kunena_categories[$row->topic_category_id]; echo $cat_id; echo "<br />"; $sql = "UPDATE `j25_kunena_topics` SET `last_post_id` = ".$row->topic_last_post_id.",`last_post_time` = ".$row->topic_last_post_time.",`last_post_userid` = ".$row->topic_last_post_userid.",`last_post_message` = '".mysql_escape_string($row->topic_last_post_message)."',`last_post_guest_name` = NULL,`params` = '' WHERE `j25_kunena_topics`.`id` = ".$row->topic_id.""; echo $sql; echo "<br />"; mysql_query($sql) or die(mysql_error()); } /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// // convert Posts from Agora tu Kunena ///////////////////////////////////////////////////////////////////////////////////////// mysql_query("TRUNCATE j25_kunena_messages"); mysql_query("TRUNCATE j25_kunena_messages_text"); $sql = " SELECT p.id AS messages_id, u.name AS messages_name, a.jos_id AS messages_userid, p.message AS messages_text_message, p.posted AS messages_time, p.edited AS messages_modified_time, p.edited_by AS messages_modified_by, p.topic_id AS messages_thread, t.forum_id AS messages_catid FROM jos_agora_posts AS p LEFT JOIN jos_agora_users AS a ON a.username = p.poster LEFT JOIN jos_agora_topics AS t ON t.id = p.topic_id LEFT JOIN jos_users AS u ON u.id = a.jos_id ORDER BY p.id ASC "; $res = mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_object($res)) { echo $row->messages_id; echo "<br />"; if($row->messages_userid == '') { $row->messages_userid = 0; } $cat_id = $ag_forums_to_kunena_categories[$row->messages_catid]; if ($cat_id != 0) { echo $cat_id; echo "<br />"; $sql = "INSERT INTO `j25_kunena_messages` VALUES (".$row->messages_id.", 0, ".$row->messages_thread.", ".$cat_id.", '".$row->messages_name."', ".$row->messages_userid.", '', '', ".$row->messages_time.", NULL, 0, 0, 0, 0, 0, 0, 0, 0, '')"; $sql2 = "INSERT INTO `j25_kunena_messages_text` VALUES (".$row->messages_id.", '".mysql_escape_string($row->messages_text_message)."')"; echo $sql; echo "<br />"; echo $sql2; echo "<br />"; mysql_query($sql) or die(mysql_error()); mysql_query($sql2) or die(mysql_error()); } } mysql_close();
Last edit: 11 years 8 months ago by silentz0r. Reason: Added full php code

Please Log in or Create an account to join the conversation.

Time to create page: 0.449 seconds