Creating a SiteMap of your site - the really hard way.

There is a lot of talk on the web about blogs at present (aka Weblogs). Google has just started a Sitemap search engine that you can submit a sitemap.xml  file for submission to aid searching of your site. Bear with me, this is all new to me.

As far as I understand all of this technology, you just need an xml file, with a few specific tags added. As this is a new site, with only a few pages at present, I thought I would use this site as a simple example.

Take a look at Blog-Essex.xml which is simply an xml file starting with the

and ends ::

Notice, this is a straightforward xml file that opens and closes every tag.

i.e <item> lah lah lah </item>

Inside the item tag are tags for title, link, description and pubDate [that stands for publication date, not when I was last down the Pub]

If you look at the top and end of the file, I open the <rss> and channel tags, and later close them.

The first entry is    <?xml version="1.0" ?>

So, in summary, we have

So, how do we produce this file, and what is the point of it?

Here are the criteria for what we require:

• A list of web pages
• A title of these pages
• A description of these pages
• A creation date of these pages

We then need a program to read in this list, and create the  .xml file, as listed

Then add this .xml file to our webserver

Then add a xml.gif picture to our main / index page pointing to this file

That will do for now - it is as far as I have got.

Create the data of the web pages [I go through each step I did in detail - just read]

• I cheated here, and used Frontpage to create a report of all the web pages on my site. As there were less than thirty, it was a nice small report.
• Then right click on this report, and copy report
• Open Excel, and paste this report
• Inserted a column and typed http://dirtiboy.com/ into the first entry and then dragged this to be there for all entries.
• Selected all data, selected sort by (column D?) Directory lsting, added an / at the ned of each directory.
• Use concatenate() to add strings together, to create the correct complete link.
• Saved this file as a .csv file with the same name. This saves an excel file without all of the formulae. I then deleted all of the non-essential columns, so that there were four left for
•   <title></title>
    <link></link>
    <description></description>
    <pubDate></pubDate>
• Save the csv file, started mysql and uploaded this file into mysql
• Well, actually I used phpMyAdmin to add this to the mysql table, phpMyAdmin is running on top of an Apache server. It is available from the sourceforge site for free, a super tool.

OK, so you are completely lost now - me too! I keep thinking of other bits you need.

short cut - to get you started.

Just start mysql, and create a table with four columns. I use column names of title, link, description and pubDate - but this does not really matter. Make then all text fields, I am not using any date fields, or anything fancy. I tend to use the varchar, which means the field ends up as big as the number of characters.

In this example, I am actually using a local copy of mysql, and create a database called blog.

Mysql is completely free, and runs on all operating systems, and is very easy to install.

[For the less squeamish, get a copy of Apache, php, and phpMyAdmin installed - more to follow on this - oh, you are going to need a java jdk installed too, to run this example]

Start mysql, open a command window and start a mysql session, using the word mysql

mysql > create database blog;

mysql > use blog;

mysql > create table bloggs (title varchar(100), link varchar(100), description varchar(250), pubDate(250));

You should now have the mysql table created.

If you have the relevant data needed in an excel spreadsheet, and have saved it as a csv file, which is just a text file that also opens in excel; you can add this data to mysql using the following mysql command:

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE blog.bloggs;

This means load the data from your csv file, here called data,csv into the table bloggs, which exists in the database blog.

You could also use this example :

LOAD DATA LOCAL INFILE 'c:\\blogs\\data.csv' INTO TABLE blog.bloggs;

which is the same, but specifies where the data.csv file is to be found - notice the double \\ which are needed, else it fails.

OK, I am presuming you now have some data in the bloggs table. Here comes the cleverest part. This is going to use a clever java program, well actually it is quite simple, but really powerful.

Let me presume, just for now, that you have mysql running, and java installed on your machine. The java must be the jdk version, so that you can compile java programs.

You will need one last file, I call it the  mysql.jar for convenience, but it is a set of java classes in a java executable file, that you will need to add to your classpath to contact mysql from any java program. Here is a version of mysql.jar that you can download.

You need to download it and save it to any area on your c: drive, I have various folders for this useful stuff, a c:/download folder is a good idea. Save everything there.

Now you need the java files - they are here - ConnectionManager.java and BlogRSS.java - save them in the c:/download  folder in the first instance

Download both of these, and open in a good text editor, textpad is good, and free to download and use.

If nothing works, download this very simple program, to do some testing. It is called testAbit.java and is a very useful way of checking whether you can access the mysql database using java. Save it in the c:/download  folder.

To run any of these java programs, you do need the java jdk  installed, and the mysql.jar on your classpath. e.g.

Save this simple text as run1.bat in  the c:/download  folder

javac -classpath .;c:\download\mysql.jar

c:\download\testAbit.java

If it complains. make sure you have the   java jdk  installed correctly.

MORE tomorrow