Foreign Perspectives

In common with all Spanish holidays, we’re completely full with Spanish guests this evening thanks to the Spanish holidays on December 6th (Constitution Day) and December 8th (Immaculate Conception).

Or, rather, we’re full of people from Spain but going by those that arrived last night not all of them will be Spanish. Thanks to the rising nationalism in Catalonia, the Spanish language isn’t actually taught in the schools just south of the border and it’s becoming increasingly common to have “Spanish” guests arrive who can’t speak Spanish. Unfortunately, the emphasis on Catalan means that it’s the only language that they can speak truly fluently and we’ve been reduced to hand signals on a couple of occasions in the recent past.

In fact, the introduction of Catalan only schools from around 1983 looks like it will have the effect of cutting off Catalona not only from Spain but from the rest of the world too. That date of introduction means that the first generation of workers who only speak Catalan have been coming into the workforce over the last couple of years and with that it’s becoming more and more difficult to deal with shop assistants who are, of course, at the younger end of the workforce.

Although, it’s nice to see that a local language is on the rise, the concentration on Catalán to the exclusion of other languages seems likely merely to alienate all the non-Catalans. Whereas elsewhere in Spain it’s expected that the locals will speak English, in fact most of the younger guests that we’ve had over recent months couldn’t speak English well and for that matter couldn’t speak French well and Spanish not at all.

One of the perennial problems that you have when trying to use information from someone else’s database is that they never code the information in quite the same way.

If the external database is one from an unrelated company on the Internet then the only way around that is to create a little translation routine or, if you’re lucky and it’s only the names that are different, a translation table.

From a purely practical point of view, the table is the way to go if that can be accomplished and that’s what I’ve done in stage one of the roll-out of the integration of a propery feed for my listings sites. Simple things such as my use of the proper name for the Algarve (ie “Faro”) whereas they use the more popular “Algarve” are easy to allow for like this and indeed just doing that this morning as bumped up the percentage of information that I’ve been able to include quite considerably.

Why not just rename things in my own database? I could do that at the moment but the problem is that I intend to integrate other databases in due course to improve the coverage of my own site and at some point I’ll need to be able to translate terms as there’s no common usage of terms across the various databases that I’m looking for.

Where things get much more difficult is if the basic structure of the information provided by the external system is radically different from that which your own system uses and then you can potentially be talking of quite a bit of work. Unfortunately, there’s no easy way around this.

Although I’ve been running database driven websites for a couple of years now what I’ve not done before is to access other peoples’ sites from my own until now.

A common way to offer people access to corporate databases via the Internet is by way of providing an XML feed. What’s XML? Well, it’s a way of holding offering both the data and the data structures via a webpage. It looks a little bit like normal HTML in that it uses the <angle> brackets but other than that it’s completely different.

The problem is that it’s a very general purpose format and not tied to any particular language. In my case, the site that I wanted to access provided examples in ASP but I use PHP so they weren’t a whole lot of use to me. Neither as it turns out was the big book on XML that I’d bought to get me on the road with the development.

However, as always there’s a lot of help around on the Internet if you look in the right places. After a few abortive attempts I found the PHP routine clsParseXML which provides a very simple yet powerful interface to XML data. Essentially what it does is to read the XML file and return it in an array or rather an array of arrays.

The database I wanted to access is essentially one large structured list of place names around the world each of which have a list of diffent accommodation at them. So, first of all I had to read the entry which listed all the regions ie Africa, Asia, Europe, etc., then choose the code for the region I was looking for and run the query again to get the countries in that region, and so on down to counties. Within the lowest level there’s a link to properties in that area requiring a whole new qyery.

Sounds very complicated, doesn’t it? Actually, it’s very simple and the high level queries run to only a few lines of code:

• region_id=get_region_id(“0″,”Europe”);
• region_id=get_region_id(region_id,”France”);
• region_id=get_region_id(region_id,”Languedoc-Roussillon”);
• region_id=get_region_id(region_id,”Pyrenees Orientale”);

Which gets me a region_id for the properties in the Pyrenees Orientale. With that I call another routine to scan through the properties in that region and return an array containing all the information of properties there.

With that, I was able to run up the HTML to integrate the properties on the external database with those from my own database thus giving, for example, the Languedoc-Roussillon page where the only way to identify the properties from the external database is by the “VR” prefix to their reference numbers.

How long to complete? Well, I started yesterday afternoon and had the test version running in a couple of hours with the live version completed in under an hour this morning. However, in reality it took much less than that as a lot of the time went in looking for that critical routine to read XML into a PHP array.

The game plan was to roll out this development to my sites listing properties around Europe but unfortunately the external database isn’t structured in the same way as my own outside France so I’ll need to run up some code to translate between the two structures first.

When you’re plugging away on your own with a bunch of websites, it’s easy to fall into the trap of not keeping things up to date. After all, the websites are doing quite nicely, so why bother messing with them?

Thankfully, I have a friend who runs a smaller stable of websites who acts as the competitive spur for me and, sometimes, I for him. For instance, I was quite happy with the adsense income that I was getting ’til I heard how much he was getting so I started working out why he was getting lots more and ended up having quite a jump in income from that source as a result.

Where we differ is that his sites are more general travel guides whereas mine are more concentrated in the accommodation listing area which means that it was fairly simple for him to slip in affiliate links supplying accommodation listings to his sites but it’s much more difficult for me. Or, at least I thought so. He’s moved on to his second generation version of integrating the accommodation feeds into his sites which looks like being a lot easier than I had expected it to be.

Sooo, I’m just about to start work on the “second generation” integration level that he’s already using which should ultimately let me popular my sites in most of the countries around the world.

Funnily enough, it will be simpler for me to do that integration as my little stable is database driven whereas his are hand written pages for the most part. This means that adding one bit of code to a single page will be enough to populate all of the pages of each series of sites that I apply it to. Now, “all” I need to do is to get that one bit of code written and working!

It’s always interesting to look at statistics on the websites. They never fail to turn up surprises.

For one thing, if you strip out ourselves, the majority of the visitors to this site are from America with the UK coming in second place. Or at least that’s what google says. For alexa on the other hand, it’s abot 35% from America and under 5% for the UK which reflects the greater use of the alexa toolbar in America as compared to the UK.

But that’s not too accurate either as the number of readers that arrive via Reuters dwarfs everything else yet isn’t counted by either of the above systems.

In theory, that means that I should be targetting the articles towards the Reuters audience but I’ve not worked out how to do that just yet (suggestions welcome!).