Import articles to Joomla! (Postnuke-Nuke raw example)

This is a very raw (working) test/script that use Php and MySQL to extract Postnuke/Nuke articles content from a database (look queries schema to fit yours) and import these data on another Joomla! db, installed on same server.

The script use ezSQL library to connect/interact with database.

NOTE: this script can be coded much better (queries and functions can be reduced, but as said, it is a raw script). By the way it work also as is, and it can be adapted to import articles on Joomla! 3> from any other CMS with a modification to few instructions.

NOTE: this script suppose that on Joomla! database exist same Categories named as on Postnuke database.
So it is necessary to create the same Categories in Joomla! named as are on Nuke/Postnuke.
I will not post this part of code as it do not respect, and corrupt, the joomla! assets table on inserting categories form Nuke/Postnuke. I had use it for many categories to insert, but using after a further tip to rebuild the joomla asset, and i never had adjust it.

NOTE: on INSERT queries: many values have been set as vars with a default value, like $asset_rules for the column rules. Some other queries values instead, like column created_by have been set ‘inline’ (as in the example user with id 734 as default for all inserted articles on column created_by).

More about Joomla! asset also at Joomla! documentation: Fixing the Joomla! assets table.

import articles to Joomla! code:

<?php

$start_select = $_GET["start_select"];
$limit_select = $_GET["limit_select"];

// db connection credentials
$host0 = "127.0.0.1";
$user0 = "root"; 
$password0 = "password";
$db0="patriotfiles_old"; // select postnuke database

// require ezSQL library
require_once "./lib/ezSQL/shared/ez_sql_core.php"; // the core
require_once "./lib/ezSQL/mysqli/ez_sql_mysqli.php"; // the mysqli class
		
$dbs = new ezSQL_mysqli($user0,$password0,$db0,$host0);	

function pnseccont_select($start_select,$limit_select){
	global $dbs;

 $b = $dbs->get_results("SELECT * FROM pn_seccont WHERE pn_secid != 0 ORDER BY pn_artid ASC LIMIT $start_select, $limit_select", ARRAY_N);
 return $b;
	
}

function pnsecid_name(){
	global $dbs;
	
	$a = $dbs->get_results("SELECT pn_secid, pn_secname FROM pn_sections");
	return $a;
	 }

function jCatId($assid){
	global $dbs;
	
	$j = $dbs->get_var("SELECT id FROM jp_categories WHERE asset_id = '$assid'");
	return $j;
	
}

// Joomla assets columns
function jAssets_query_colswitch($switchCols){
	global $dbs;
	
	// MAX asset_id
	  if($switchCols == "jasset_id"){
	    $a = $dbs->get_var("SELECT MAX(id) FROM jp_assets") ;
     }
  // MAX asset rgt
    if($switchCols == "rgt"){
	    $a = $dbs->get_var("SELECT MAX(rgt) FROM jp_assets") ;
	   }
	 return $a;

	}

if(!isset($_GET["start_select"])){
 $start_select = 0;
 $limit_select = 0;
}

// where we are
echo "START = $start_select - LIMIT = $limit_select";

$pn_seccont = pnseccont_select($start_select,$limit_select);

echo"<pre>";
//print_r($pn_seccont);  
echo"</pre>";

$pn_secid_name = pnsecid_name();

echo"<pre>";
//print_r($pn_secid_name);
echo"</pre>";

// set here joomla db to switch connection
$dbs->select("JoomlaDB"); // switch to joomla db to work with

// grab asset_id category
$pnsecid_arr = array();
$jassid_arr = array();


while (list($key, $value) = each($pn_secid_name)) {
$j = $dbs->get_results("SELECT id, title, asset_id FROM jp_categories WHERE title = '$value->pn_secname'");

array_push($pnsecid_arr,$value->pn_secid);
array_push($jassid_arr,$j[0]->asset_id);   
}

$pncid_jassid = array_combine($pnsecid_arr, $jassid_arr);

echo"<pre>";
//print_r($pncid_jassid);
echo"</pre>";

$ja_max_rgt       = jAssets_query_colswitch("rgt");

// prepare for article insertion

// default joomla asset cols values
$asset_rules = '{"core.delete":{"6":1},"core.edit":{"6":1,"4":1},"core.edit.state":{"6":1,"5":1}}';
$image_col  = '{"image_intro":"","float_intro":"","image_intro_alt":"","image_intro_caption":"","image_fulltext":"","float_fulltext":"","image_fulltext_alt":"","image_fulltext_caption":""}';
$url_col    = '{"urla":false,"urlatext":"","targeta":"","urlb":false,"urlbtext":"","targetb":"","urlc":false,"urlctext":"","targetc":""}';
$attrib_col = '{"show_title":"","link_titles":"","show_tags":"","show_intro":"","info_block_position":"","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":"","urls_position":"","alternative_readmore":"","article_layout":"","show_publishing_options":"","show_article_options":"","show_urls_images_backend":"","show_urls_images_frontend":""}';
$metadata   = '{"robots":"","author":"","rights":"","xreference":""}';
date_default_timezone_set('UTC');
$date = date("Y-m-d H:i:s");

	while (list(, $value) = each($pn_seccont)) {
		list($pn_artid, $pn_secid, $pn_title,$pn_content,$pn_counter,$pn_language) = $value;
                               
foreach($pncid_jassid as $pnsec => $jasset_id)
{

		if($pn_secid == $pnsec){
	
    	$jas_id = $jasset_id; // grab and assign correct asset_id for each
	}
}

// more values for joomla asset cols
$calias = preg_replace('/[^a-zA-Z0-9][ ]+/', '', $pn_title);
$calias = preg_replace('/[\']+/', '-', $calias);
$calias = preg_replace('[ ]', '-', $calias);
$calias = strtolower($calias);
$rest   = substr($calias, -1);
if($rest == "-" OR $rest == " "){$calias = substr($calias, 0, -1);}
$pn_content  = preg_replace('[<br>]', '<br />', $pn_content);
$desc_title_clean = preg_replace('/[^a-zA-Z0-9][ ]+/', '', $pn_title);


$com_content     = $dbs->get_results("SELECT * FROM jp_assets WHERE name = 'com_content'") ;
$com_content_cat = $dbs->get_results("SELECT * FROM jp_assets WHERE id = $jas_id") ;
$jcatid = jCatId($jas_id);
$cat_parentid         = $com_content_cat[0]->parent_id;

//echo "<br />".$ja_lft = $com_content_cat[0]->rgt;
//echo "<br />".$ja_rgt = $com_content_cat[0]->rgt + 1;

 
 $dbs->query("INSERT INTO jp_assets (id, parent_id, lft, rgt, level, name, title, rules)
 VALUES ('0','$jas_id','$ja_lft','$ja_rgt','3','com_content.article.temp','$desc_title_clean','". $dbs->escape($asset_rules)."')") ;
$jass_maxid = $dbs->insert_id;

 $dbs->query("INSERT INTO jp_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, xreference)
 VALUES ('0', '$jass_maxid', '$pn_title', '$calias', '', '". $dbs->escape($pn_content)."', '1', '$jcatid', '$date', '734', '', '0000-00-00 00:00:00', '0', '0', '0000-00-00 00:00:00', '$date', '0000-00-00 00:00:00', '". $dbs->escape($image_col)."', '". $dbs->escape($url_col)."', '". $dbs->escape($attrib_col)."', '1', '0', '', '', '1', '$pn_counter', '". $dbs->escape($metadata)."', '0', '*', '')");
$jcont_maxid = $dbs->insert_id;

$dbs->query("UPDATE jp_assets SET name = 'com_content.article.".$jcont_maxid."' WHERE id = '$jass_maxid'");


$com_content_pid  = $com_content[0]->parent_id;
$ccont_rgt        = $com_content[0]->rgt;
$cat_cont_id      = $com_content_cat[0]->id;


$dbs->query("UPDATE jp_assets SET rgt = rgt+2 WHERE name = 'com_content'");
//$dbs->debug();
$dbs->query("UPDATE jp_assets SET rgt = rgt+2 WHERE id = '$cat_cont_id'");
//$dbs->debug();
$dbs->query("UPDATE jp_assets SET lft=lft+2, rgt=rgt+2  WHERE lft > '$ccont_rgt' AND id != '$cat_cont_id' AND name != 'com_content' AND id != 1");
//$dbs->debug();

} // end while


// update the root assets ID 1
$max_rgt = jAssets_query_colswitch("rgt");
$max_rgt += 1;
$dbs->query("UPDATE jp_assets SET rgt = '$max_rgt' WHERE id = 1");





if ( $EZSQL_ERROR )
{
            // View the errors
            $dbs->vardump($EZSQL_ERROR);
}
else
{
            echo "<h2>Selected Postnuke _seccont records have been imported correctly to Joomla!</h2>";
}

if(!isset($_GET["start_select"])){
$start_select = 0;
$limit_select = 0;
}else{
	$start_select = $start_select+$limit_select;
$limit_select = $limit_select;
}

?>

<form action="./pnseccont_to_joomla.php" method="get">
        <input type="hidden" name="start_select" value="<?php echo $start_select;?>" />
        <input type="text" name="limit_select" value="3" />
<div class="button">
        - - - Load Articles &nbsp;&nbsp;<input type="submit"> </input>
    </div>
</form>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

<?php

$start_select = $_GET["start_select"];

$limit_select = $_GET["limit_select"];

// db connection credentials

$host0 = "127.0.0.1";

$user0 = "root";

$password0 = "password";

$db0="patriotfiles_old"; // select postnuke database

// require ezSQL library

require_once "./lib/ezSQL/shared/ez_sql_core.php"; // the core

require_once "./lib/ezSQL/mysqli/ez_sql_mysqli.php"; // the mysqli class

$dbs = new ezSQL_mysqli($user0,$password0,$db0,$host0);

function pnseccont_select($start_select,$limit_select){

global $dbs;

$b = $dbs->get_results("SELECT * FROM pn_seccont WHERE pn_secid != 0 ORDER BY pn_artid ASC LIMIT $start_select, $limit_select", ARRAY_N);

return $b;

}

function pnsecid_name(){

global $dbs;

$a = $dbs->get_results("SELECT pn_secid, pn_secname FROM pn_sections");

return $a;

}

function jCatId($assid){

global $dbs;

$j = $dbs->get_var("SELECT id FROM jp_categories WHERE asset_id = '$assid'");

return $j;

}

// Joomla assets columns

function jAssets_query_colswitch($switchCols){

global $dbs;

// MAX asset_id

if($switchCols == "jasset_id"){

$a = $dbs->get_var("SELECT MAX(id) FROM jp_assets") ;

}

// MAX asset rgt

if($switchCols == "rgt"){

$a = $dbs->get_var("SELECT MAX(rgt) FROM jp_assets") ;

}

return $a;

}

if(!isset($_GET["start_select"])){

$start_select = 0;

$limit_select = 0;

}

// where we are

echo "START = $start_select - LIMIT = $limit_select";

$pn_seccont = pnseccont_select($start_select,$limit_select);

echo"<pre>";

//print_r($pn_seccont);

echo"</pre>";

$pn_secid_name = pnsecid_name();

echo"<pre>";

//print_r($pn_secid_name);

echo"</pre>";

// set here joomla db to switch connection

$dbs->select("JoomlaDB"); // switch to joomla db to work with

// grab asset_id category

$pnsecid_arr = array();

$jassid_arr = array();

while (list($key, $value) = each($pn_secid_name)) {

$j = $dbs->get_results("SELECT id, title, asset_id FROM jp_categories WHERE title = '$value->pn_secname'");

array_push($pnsecid_arr,$value->pn_secid);

array_push($jassid_arr,$j[0]->asset_id);

}

$pncid_jassid = array_combine($pnsecid_arr, $jassid_arr);

echo"<pre>";

//print_r($pncid_jassid);

echo"</pre>";

$ja_max_rgt = jAssets_query_colswitch("rgt");

// prepare for article insertion

// default joomla asset cols values

$asset_rules = '{"core.delete":{"6":1},"core.edit":{"6":1,"4":1},"core.edit.state":{"6":1,"5":1}}';

$image_col = '{"image_intro":"","float_intro":"","image_intro_alt":"","image_intro_caption":"","image_fulltext":"","float_fulltext":"","image_fulltext_alt":"","image_fulltext_caption":""}';

$url_col = '{"urla":false,"urlatext":"","targeta":"","urlb":false,"urlbtext":"","targetb":"","urlc":false,"urlctext":"","targetc":""}';

$attrib_col = '{"show_title":"","link_titles":"","show_tags":"","show_intro":"","info_block_position":"","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":"","urls_position":"","alternative_readmore":"","article_layout":"","show_publishing_options":"","show_article_options":"","show_urls_images_backend":"","show_urls_images_frontend":""}';

$metadata = '{"robots":"","author":"","rights":"","xreference":""}';

date_default_timezone_set('UTC');

$date = date("Y-m-d H:i:s");

while (list(, $value) = each($pn_seccont)) {

list($pn_artid, $pn_secid, $pn_title,$pn_content,$pn_counter,$pn_language) = $value;

foreach($pncid_jassid as $pnsec => $jasset_id)

{

if($pn_secid == $pnsec){

$jas_id = $jasset_id; // grab and assign correct asset_id for each

}

// more values for joomla asset cols

$calias = preg_replace('/[^a-zA-Z0-9][ ]+/', '', $pn_title);

$calias = preg_replace('/[\']+/', '-', $calias);

$calias = preg_replace('[ ]', '-', $calias);

$calias = strtolower($calias);

$rest = substr($calias, -1);

if($rest == "-" OR $rest == " "){$calias = substr($calias, 0, -1);}

$pn_content = preg_replace('[<br>]', '<br />', $pn_content);

$desc_title_clean = preg_replace('/[^a-zA-Z0-9][ ]+/', '', $pn_title);

$com_content = $dbs->get_results("SELECT * FROM jp_assets WHERE name = 'com_content'") ;

$com_content_cat = $dbs->get_results("SELECT * FROM jp_assets WHERE id = $jas_id") ;

$jcatid = jCatId($jas_id);

$cat_parentid = $com_content_cat[0]->parent_id;

//echo "<br />".$ja_lft = $com_content_cat[0]->rgt;

//echo "<br />".$ja_rgt = $com_content_cat[0]->rgt + 1;

$dbs->query("INSERT INTO jp_assets (id, parent_id, lft, rgt, level, name, title, rules)

VALUES ('0','$jas_id','$ja_lft','$ja_rgt','3','com_content.article.temp','$desc_title_clean','". $dbs->escape($asset_rules)."')") ;

$jass_maxid = $dbs->insert_id;

$dbs->query("INSERT INTO jp_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, xreference)

VALUES ('0', '$jass_maxid', '$pn_title', '$calias', '', '". $dbs->escape($pn_content)."', '1', '$jcatid', '$date', '734', '', '0000-00-00 00:00:00', '0', '0', '0000-00-00 00:00:00', '$date', '0000-00-00 00:00:00', '". $dbs->escape($image_col)."', '". $dbs->escape($url_col)."', '". $dbs->escape($attrib_col)."', '1', '0', '', '', '1', '$pn_counter', '". $dbs->escape($metadata)."', '0', '*', '')");

$jcont_maxid = $dbs->insert_id;

$dbs->query("UPDATE jp_assets SET name = 'com_content.article.".$jcont_maxid."' WHERE id = '$jass_maxid'");

$com_content_pid = $com_content[0]->parent_id;

$ccont_rgt = $com_content[0]->rgt;

$cat_cont_id = $com_content_cat[0]->id;

$dbs->query("UPDATE jp_assets SET rgt = rgt+2 WHERE name = 'com_content'");

//$dbs->debug();

$dbs->query("UPDATE jp_assets SET rgt = rgt+2 WHERE id = '$cat_cont_id'");

//$dbs->debug();

$dbs->query("UPDATE jp_assets SET lft=lft+2, rgt=rgt+2 WHERE lft > '$ccont_rgt' AND id != '$cat_cont_id' AND name != 'com_content' AND id != 1");

//$dbs->debug();

} // end while

// update the root assets ID 1

$max_rgt = jAssets_query_colswitch("rgt");

$max_rgt += 1;

$dbs->query("UPDATE jp_assets SET rgt = '$max_rgt' WHERE id = 1");

if ( $EZSQL_ERROR )

{

// View the errors

$dbs->vardump($EZSQL_ERROR);

}

else

{

echo "<h2>Selected Postnuke _seccont records have been imported correctly to Joomla!</h2>";

}

if(!isset($_GET["start_select"])){

$start_select = 0;

$limit_select = 0;

}else{

$start_select = $start_select+$limit_select;

$limit_select = $limit_select;

}

- - - Load Articles   <input type="submit"> </input>

</div>

</form>

SQL Trees and Hierarchies:

Leave a Reply Cancel reply