Script to display pages or posts for GetSimple CMS in sidebar

I was trying out the GetSimple CMS and although it looks pretty good, I was gutted that it didn’t display the pages/posts that you write. Usually the latest posts that you write appears in the sidebar but with GetSimple, you have to add the links manually. There’s the News Manager (or Extended News Manager) plugin that you can get but I couldn’t get it to work because of lack of documentation for the installation process.

Anyway I decided to look at the code and I managed to do a quick hack to display the pages in the sidebar of GetSimple. The code is as follows:


<?php
//display all pages except index page and private pages
$path 		= GSDATAPAGESPATH;
$filenames = getFiles($path);

$count="0";
$pagesArray = array();
$heading = "<h2>Topics</h2>";
$pages_list;
if (count($filenames) != 0)
{
	foreach ($filenames as $file)
	{
		if (isFile($file, $path, 'xml'))
		{
			$data = getXML($path .$file);
			$status = $data->menuStatus;
			$pagesArray[$count]['title'] = html_entity_decode($data->title, ENT_QUOTES, 'UTF-8');
			$pagesArray[$count]['parent'] = $data->parent;
			$pagesArray[$count]['menuStatus'] = $data->menuStatus;
			$pagesArray[$count]['private'] = $data->private;
			if ($data->parent != '')
			{
				$parentdata = getXML($path . $data->parent .'.xml');
				$parentTitle = $parentdata->title;
				$pagesArray[$count]['sort'] = $parentTitle .' '. $data->title;
			}
			else
			{
				$pagesArray[$count]['sort'] = $data->title;
			}
			$pagesArray[$count]['url'] = $data->url;
			$pagesArray[$count]['date'] = $data->pubDate;
			$parentTitle = '';

			// do not show the index page as well as private pages
			if ($pagesArray[$count]['url'] != 'index' && $pagesArray[$count]['private'] != 'Y')
			{
				$pages_list .= "<li><a href=\"/" . $pagesArray[$count]['url'] . "/\">" . $pagesArray[$count]['title'] . "</a></li>";
				$count++;
			}
		}
	}

	if ($count > 0)
		$pages_list = $heading . "<ul>" . $pages_list . "</ul>";

	echo $pages_list;
}
?>

You will need to paste the code where you want the list of pages to appear. I placed mine directly in the sidebar by going to “Edit Theme”. I don’t have time to make this into a GetSimple plugin but feel free to use it as you wish. The script will list all your pages except the index (homepage) and private pages. You can customise the heading and you can also easily list only 10 pages instead of everything by stopping the loop.

System.Data.InvalidConstraintException: Parent Columns and Child Columns don’t have type-matching columns

If you encounter the error System.Data.InvalidConstraintException: Parent Columns and Child Columns don’t have type-matching columns, then the columns you’re trying to link do not have the same data type. In my case, I was trying to set a relationship between an “articles” table with a “categories” table. The “articles” table has a cat_id field as a foreign key and the “categories” table had its cat_id field as the primary key.

However the cat_id column in the “categories” table was defined as a tinyint(4) while the cat_id in “articles” was an int(11) data type. I changed cat_id in “articles” to tinyint(4) and the problem disappeared.

Script to transfer custom CMS into WordPress

This is a script I wrote to import my blog posts from a custom CMS into WordPress. Some things have been hard coded because I needed to do this quickly but you can easily change the script to adapt to your needs. A couple of things worth noting:

  1. You need to be using Windows and have IIS server
  2. I was importing from a MySql database and I have the MySql .NET connector installed on my machine
  3. This is a quick and dirty script and it grabs only the posts and comments (no images etc)

Here’s the wordpress importer script (it’s an asp.net http handler):

<%@ WebHandler Language="C#" %>
using System;
using System.Web;
using System.IO;
using System.Xml;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
public class GisToWP : IHttpHandler {
public void ProcessRequest (HttpContext context)
{
string website = "http://www.example.co.uk/";
string username = "MrCoder";
string title = "Blog Title";
string desc = "Another wordpress blog";
// dataset
System.Data.DataSet db = new System.Data.DataSet();
string conString = "Database=database;Data Source=example.com;User Id=username;Password=password;";
// get the articles/blogposts
using (MySqlConnection con = new MySqlConnection(conString))
{
con.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "select * from articles where article_status = 1"; // change this
cmd.Connection = con;
MySqlDataAdapter dr = new MySqlDataAdapter();
dr.SelectCommand = cmd;
dr.Fill(db, "articles");
con.Close();
}
// get the comments
using (MySqlConnection con = new MySqlConnection(conString))
{
con.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "select * from tblComments where StatusID = 1"; // change this
cmd.Connection = con;
MySqlDataAdapter dr = new MySqlDataAdapter();
dr.SelectCommand = cmd;
dr.Fill(db, "comments");
con.Close();
}
// get the users
using (MySqlConnection con = new MySqlConnection(conString))
{
con.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "select * from tblUsers"; // change this
cmd.Connection = con;
MySqlDataAdapter dr = new MySqlDataAdapter();
dr.SelectCommand = cmd;
dr.Fill(db, "users");
con.Close();
}
// get the categories
using (MySqlConnection con = new MySqlConnection(conString))
{
con.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "select * from article_categories where status = 1"; // change this
cmd.Connection = con;
MySqlDataAdapter dr = new MySqlDataAdapter();
dr.SelectCommand = cmd;
dr.Fill(db, "categories");
con.Close();
}
// link articles to comments
db.Relations.Add("article_comment", db.Tables["articles"].Columns["article_id"], db.Tables["comments"].Columns["ArticleID"], false);
// link articles to categories
db.Relations.Add("article_category", db.Tables["articles"].Columns["cat_id"], db.Tables["categories"].Columns["cat_id"], false);
// link comments to users
db.Relations.Add("comment_user", db.Tables["comments"].Columns["UserID"], db.Tables["users"].Columns["UserID"], false);
// set primary keys
db.Tables["articles"].PrimaryKey = new System.Data.DataColumn[1] { db.Tables["articles"].Columns["article_id"] };
db.Tables["comments"].PrimaryKey = new System.Data.DataColumn[1] { db.Tables["comments"].Columns["CommentID"] };
db.Tables["users"].PrimaryKey = new System.Data.DataColumn[1] { db.Tables["users"].Columns["UserID"] };
db.Tables["categories"].PrimaryKey = new System.Data.DataColumn[1] { db.Tables["categories"].Columns["cat_id"] };
// write the xml file
using (MemoryStream ms = new MemoryStream()) // use memory stream
{
using (TextWriter textWriter = new StreamWriter(ms, System.Text.Encoding.UTF8))
{
XmlTextWriter writer = new XmlTextWriter(textWriter);
writer.Formatting = Formatting.Indented;
writer.WriteStartDocument();
writer.WriteStartElement("rss");
writer.WriteAttributeString("version", "2.0");
writer.WriteAttributeString("xmlns:excerpt", "http://wordpress.org/export/1.0/excerpt/");
writer.WriteAttributeString("xmlns:content", "http://purl.org/rss/1.0/modules/content/");
writer.WriteAttributeString("xmlns:wfw", "http://wellformedweb.org/CommentAPI/");
writer.WriteAttributeString("xmlns:dc", "http://purl.org/dc/elements/1.1/");
writer.WriteAttributeString("xmlns:wp", "http://wordpress.org/export/1.0/");
writer.WriteStartElement("channel");
writer.WriteElementString("title", title);
writer.WriteElementString("link", website);
writer.WriteElementString("description", desc);
writer.WriteElementString("pubDate", DateTime.Now.ToString("ddd, dd MMM yyyy hh:mm:ss +0000"));
writer.WriteElementString("generator", "http://wordpress.org/?v=2.9.2");
writer.WriteElementString("language", "en");
writer.WriteElementString("wp:wxr_version", "1.0");
writer.WriteElementString("wp:base_site_url", website);
writer.WriteElementString("wp:base_blog_url", website);
// categories
foreach (System.Data.DataRow cat in db.Tables["categories"].Rows)
{
writer.WriteStartElement("wp:category");
writer.WriteElementString("wp:category_nicename", cat["cat_name"].ToString());
writer.WriteElementString("wp:category_parent", String.Empty);
writer.WriteStartElement("wp:cat_name");
writer.WriteCData(cat["cat_real_name"].ToString());
writer.WriteEndElement(); // wp:cat_name
writer.WriteEndElement(); // wp:category
}
writer.WriteStartElement("xhtml:meta");
writer.WriteAttributeString("xmlns:xhtml", "http://www.w3.org/1999/xhtml");
writer.WriteAttributeString("name", "robots");
writer.WriteAttributeString("content", "index");
writer.WriteEndElement();
// loop through all articles
foreach (System.Data.DataRow article in db.Tables["articles"].Rows)
{
writer.WriteStartElement("item");
// category name
string categorySlug = db.Tables["categories"].Rows.Find(article["cat_id"])["cat_name"].ToString();
string categoryName = db.Tables["categories"].Rows.Find(article["cat_id"])["cat_real_name"].ToString();
writer.WriteElementString("title", article["article_title"].ToString());
writer.WriteElementString("link", String.Format("{0}{1}/{2}/", website, categorySlug, article["article_permalink"].ToString()));
writer.WriteElementString("pubDate", ((DateTime)article["post_datetime"]).ToString("ddd, dd MMM yyyy hh:mm:ss +0000"));
writer.WriteStartElement("dc:creator");
writer.WriteCData(username);
writer.WriteEndElement();
writer.WriteStartElement("category");
writer.WriteCData(categorySlug);
writer.WriteEndElement();
writer.WriteStartElement("category");
writer.WriteAttributeString("domain", "category");
writer.WriteAttributeString("nicename", categoryName);
writer.WriteCData(categoryName);
writer.WriteEndElement(); // category
writer.WriteStartElement("guid");
writer.WriteAttributeString("isPermalink", "false");
writer.WriteString(String.Format("{0}?p={1}", website, article["article_id"].ToString()));
writer.WriteEndElement(); // guid
writer.WriteElementString("description", String.Empty);
writer.WriteStartElement("content:encoded");
writer.WriteCData(FormatContent(article["article_content"].ToString()));
writer.WriteEndElement();
writer.WriteStartElement("excerpt:encoded");
writer.WriteCData(String.Empty);
writer.WriteEndElement();
writer.WriteElementString("wp:post_id", article["article_id"].ToString());
writer.WriteElementString("wp:post_date", ((DateTime)article["article_datetime"]).ToString("yyyy-MM-dd hh:mm:ss"));
writer.WriteElementString("wp:post_date_gmt", ((DateTime)article["article_datetime"]).AddHours(6).ToString("yyyy-MM-dd hh:mm:ss"));
writer.WriteElementString("wp:comment_status", "closed");
writer.WriteElementString("wp:ping_status", "closed");
writer.WriteElementString("wp:post_name", article["article_permalink"].ToString());
writer.WriteElementString("wp:status", "publish");
writer.WriteElementString("wp:post_parent", "0");
writer.WriteElementString("wp:menu_order", "0");
writer.WriteElementString("wp:post_type", "post");
writer.WriteElementString("wp:post_password", String.Empty);
writer.WriteElementString("wp:is_sticky", "0");
// meta tags
writer.WriteStartElement("wp:postmeta");
writer.WriteElementString("wp:meta_key", "title");
writer.WriteElementString("wp:meta_value", article["article_page_title"].ToString());
writer.WriteEndElement();
writer.WriteStartElement("wp:postmeta");
writer.WriteElementString("wp:meta_key", "description");
writer.WriteElementString("wp:meta_value", article["article_meta_desc"].ToString());
writer.WriteEndElement();
// comments for this article
System.Data.DataRow[] theComments = article.GetChildRows("article_comment");
foreach (System.Data.DataRow comment in theComments)
{
writer.WriteStartElement("wp:comment");
string commenterName = db.Tables["users"].Rows.Find(comment["UserID"])["Fullname"].ToString();
string commenterEmail = db.Tables["users"].Rows.Find(comment["UserID"])["EmailAddress"].ToString();
string commenterIP = db.Tables["users"].Rows.Find(comment["UserID"])["IPAddress"].ToString();
writer.WriteElementString("wp:comment_id", comment["CommentID"].ToString());
writer.WriteElementString("wp:comment_author", commenterName);
writer.WriteElementString("wp:comment_author_email", commenterEmail);
writer.WriteElementString("wp:comment_author_url", String.Empty);
writer.WriteElementString("wp:comment_author_IP", commenterIP);
writer.WriteElementString("wp:comment_date", ((DateTime)comment["CreateDate"]).ToString("yyyy-MM-dd hh:mm:ss"));
writer.WriteElementString("wp:comment_date_gmt", ((DateTime)comment["CreateDate"]).AddHours(6).ToString("yyyy-MM-dd hh:mm:ss"));
writer.WriteStartElement("wp:comment_content");
writer.WriteCData(FormatContent(comment["Content"].ToString()));
writer.WriteEndElement();
writer.WriteElementString("wp:comment_approved", "1");
writer.WriteElementString("wp:comment_type", String.Empty);
writer.WriteElementString("wp:comment_parent", "0");
writer.WriteElementString("wp:comment_user_id", "0");
writer.WriteEndElement(); // wp:comment
}
writer.WriteEndElement(); // item
}
writer.WriteEndElement(); // channel
writer.WriteEndElement(); // rss
// To be safe, flush the document to the memory stream.
writer.Flush();
// Convert the memory stream to an array of bytes.
byte[] byteArray = ms.ToArray();
// Send the XML file to the web browser for download.
context.Response.Clear();
context.Response.AppendHeader("Content-Disposition", "filename=wordpress.xml");
context.Response.AppendHeader("Content-Length", byteArray.Length.ToString());
context.Response.ContentType = "text/xml";
context.Response.BinaryWrite(byteArray);
}
}
}
public string FormatContent(string text)
{
return text.Replace("<p>", "").Replace("</p>", "");
}
public bool IsReusable {
get {
return false;
}
}
}

How to use the wordpress importer script?

You will need to change the sql statements where I’ve put the comments (change this) to select your posts, comments, users and categories from your database. When you run the script, it will prompt you to download an xml file (or show you an output in your browser which you can “View Source” and save to your PC. This will be your wordpress WRX xml file which you will use to import your blog into wordpress.

For wordpress 3.2, you will have to go to Tools -> Import and then select WordPress from the list. It will then ask you to install the WordPress Importer plugin and activate it. Once this is done, you can then upload this WRX file and your blogposts will be imported in your new blog.

WordPress blog posts being truncated during import

If you happen to see truncated data in your posts, it might be caused by character encoding problems. To fix mine, I opened the WRX file in Notepad++ and went to Encoding -> Encode in UTF-8 and it showed me invalid characters. Instead of replacing the bad characters, I ran the script again, copy the data from view source, pasted in Notepad++, selected the encoding to be UTF-8 before saving the WRX file. When I imported the xml file this time, no blog posts were truncated.

How to test your website on a new server before changing nameservers

Sometimes moving to a new server is a must for a number of reasons:

  1. You need more resources because your traffic is growing bigger and your current hosting package cannot cope with the demand
  2. Your current hosting provider is not reliable, so you want to move your website to a new webhost
  3. You have changed your codebase, so you need another OS (eg if you had a site in php but have converted it to .net, you will have to move from a linux webserver to a windows one)

Once everything has been tested locally (on the development machine), you want to make sure that it’s working properly on the remote machine as well, that is, on the new webhost. What you do is to configure the DNS on the webhost for the website. Let’s call the website www.example.com and deal with a linux server where you’ll get CPanel. When you sign up with the webhost, they normally ask you whether you want to host an existing website or a new one. If you chose an existing one, the DNS will already be configured for you. If you wanted to have another domain on the same account, you can use addon domain functionality in CPanel to do that. Plesk on Windows servers will allow you to create the dns zone for a new website as well.

Once you’ve added the DNS, you can edit your hosts file if you’re using Windows OS. It’s located at C:\Windows\System32\Drivers\etc and you’ll probably have to open it in Notepad with administrator’s priviledge to be able to make changes to it. All you have to do it add an entry as follows:

100.101.102.103 www.example.com

Replace 100.101.102.103 with the IP address of your new server. You will then need to clear the cache of your browser. When you try to browse to www.example.com then, you will see your website on your new server (new webhost). If there are any problems, you can fix it there and then. This allows you to properly test your website on a new webhost before actually changing your nameservers.

Once you are satisfied that the website will be working properly on the new webhost, you can then change your nameservers with your domain registrar so that your live website is pointed to the new web host. Changing the nameservers is like saying “hey, I’m not on the old server anymore, I’ve changed address, and this is my new address”.

Note that testing the website the above way is not 100% accurate because there are certain things which require the right nameservers changes before they work properly. An example is wordpress cron jobs (wp-cron.php) which did not work using this method.

How to import your blog into wordpress

If you already have an existing blog on the well known sites like blogger, livejournal, typepad etc, you will easily be able to import your blog into wordpress. However if like me, you have a custom Content Management System (CMS) but need getting the data into wordpress, then things get a bit tricky.

Here’s the WordPress Importer for custom CMS or blog

Initially when I created my own CMS, I did that because I wanted more control over things. I wanted to do things my own way and then there’s the sense of achievement that you’re running your own blog and you’re not like everyone else using wordpress or another famous blogging platform. However php is not really my cup of tea. Yes I can code in php but I don’t really like it. I prefer C# and after converting my CMS to .NET, I decided I no longer wanted to maintain the codebase for the CMS coded in Php. I chose wordpress as my blogging platform because I’m more familiar with it and there’s tons of plugins nowadays to do just about anything and there’s loads of themes to choose from (and they’re free!!!) and the platform is well maintained and secure. I couldn’t convert them to .NET because I’d need Windows server to run them on and let’s face it, Linux is much cheaper and more available than Windows.

So the time comes for me to convert my existing CMS to wordpress and because it my a tailor-made CMS, I have to do a manual conversion. There are 2 options for that:

Use a database script

When wordpress has been installed, the database and tables would have been created. You can look at the table definition and insert records for the posts and comments from your old tables. The database script would copy the records for the posts and comments to a temporary (holding) table and then insert the data into wordpress tables.

I decided not to go down this path because I would need to properly analyse the tables in wordpress before I was sure I was putting data into the correct fields and not breaking any constraints or relationships.

Use the WordPress eXtended RSS file format (WXR)

This is similar to an RSS file which is an xml document with the posts and comments. All you have to do is create this xml file and go to wordpress and from Tools->Import, you will get the option to upload the WXR xml file and all your posts and comments will be copied over to your new wordpress blog.

This was a better way for me to convert my old blog to wordpress. I made an ASP.NET Http Handler create the xml file for me to use on the new blog. You will have to understand what elements are required in the WordPress file format for a successful import though. I was worried about something though – I have a page title and a page subject as well as a description field for any given webpage. I wanted to preserve these when moving over to wordpress and since the subject line and title did not match, it can have drastic effect in terms of SEO. However when the import was done, I had to enable a plugin (Platinum SEO pack) and the titles and subject were displayed correctly. Note that the wordpress importer is a plugin which needs to be installed before you can upload your WXR file to import your old blog.

For those interested, I’ll be posting the code for generating the wordpress xml file (WXR format). Code already posted above!