Technology Tales

Adventures & experiences in contemporary technology

Basic string searching in MySQL table columns

29th April 2010

Last weekend, I ended up doing a spot of file structure reorganisation on the web server for my Assorted Explorations website and needed to correct some file pointers in entries on my outdoors blog. Rather than grabbing a plugin from somewhere, I decided to edit the posts table directly. First, I needed to select the affected observations and this is where I had to pick out the affected rows and edit them in MySQL Query Browser. To accomplish that, I needed basic string searching so I opened up my MySQL e-book from Apress and constructed something like the following:

select * from posts_table where post_text like '%some_text%';

The % wildcard characters are required to pick out a search string in any part of a piece of text. There may be a more sophisticated method, but this did what I needed in a quick and dirty manner without further ado. Well, it was what I needed.

Investigating Textpattern

9th March 2009

With the profusion of Content Management Systems out there, open source and otherwise, my curiosity has been aroused for a while now. In fact, Automattic’s aspirations for WordPress (the engine powering this blog) now seem to go beyond blogging and include wider CMS-style usage. Some may even have put the thing to those kinds of uses but I am of the opinion that it has a way to go yet before it can put itself on a par with the likes of Drupal and Joomla!.

Speaking of Drupal, I decided to give it a go a while back and came away with the impression that it’s a platform for an entire website. At the time, I was attracted by the idea of having one part of a website on Drupal and another using WordPress but the complexity of the CSS in the Drupal template thwarted my efforts and I desisted. The heavy connection between template and back end cut down on the level of flexibility too. That mix of different platforms might seem odd in architectural terms but my main website also had a custom PHP/MySQL driven photo gallery too and migrating everything into Drupal wasn’t going to be something that I was planning. In hindsight, I might have been trying to get Drupal to perform a role for which it was never meant so I am not holding its non-fulfillment of my requirements against it. Drupal may have changed since I last looked at it but I decided to give an alternative a go regardless.

Towards the end of last year, I began to look at Textpattern (otherwise known as Txp) in the same vein and it worked well enough after a little effort that I was able to replace what was once a visitor dossier with a set of travel jottings. In some respects, Textpattern might feel less polished when you start to compare it with alternatives like WordPress or Drupal but the inherent flexibility of its design leaves a positive impression. In short, I was happy to see that it allowed me to achieve what I wanted to do.

If I remember correctly, Textpattern’s default configuration is that of a blog and it can be used for that purpose. So, I got in some content and started to morph the thing into what I had in mind. My ideas weren’t entirely developed so some of that was going on while I went about bending Txp to my will. Most of that involved tinkering in the Presentation part of the Txp interface though. It differs from WordPress in that the design information like (X)HTML templates and CSS are stored in the database rather than in the file system à la WP. Txp also has its own tag language called Textile and, though it contains conditional tags, I find that encasing PHP in <txp:php></txp:php> tags is a more succinct way of doing things; only pure PHP code can be used in this way and not a mixture of such in <?php ?> tags and (X)HTML. A look at the tool’s documentation together with perusal of Apress’ Textpattern Solutions got me going in this new world (it was thus for me, anyway). The mainstay of the template system is the Page and each Section can use a different Page. Each Page can share components and, in Txp, these get called Forms. These are included in a Page using Textile tags of the form <txp:output_form form=”form1″ />. Style information is edited in another section and you can have several style sheets too.

The Txp Presentation system is made up of Sections, Pages, Forms and Styles. The first of these might appear in the wrong place when being under the Content tab would seem more appropriate but the ability to attach different page templates to different sections places their configuration where you find it in Textpattern and the ability to show or hide sections might have something to do with it too. As it happens, I have used the same template for all bar the front page of the site and got it to display single or multiple articles as appropriate using the Category system. It may be a hack but it appears to work well in practice. Being able to make a page template work in the way that you require really offers a great amount of flexibility and I have gone with one sidebar rather than two as found in the default set up.

Txp also has facility to add plugins (look in the Admin section of the UI) and this is very different from WordPress in that installation involves the loading of an encoded text file, probably for sake of maintaining the security and integrity of your installation. I added the navigation facility for my sidebar and breadcrumb links in this manner and back end stuff like Tiny MCE editor and Akismet came as plugins too. There may not be as many of these for Textpattern but the ones that I found were enough to fulfill my needs. If there are plugin configuration pages in the administration interface, you will find these under the Extensions tab.

To get the content in, I went with the more laborious copy, paste and amend route. Given that I was coming from the plain PHP/XHTML way of doing things, the import functionality was never going to do much for me with its focus on Movable Type, WordPress, Blogger and b2. The fact that you only import content into a particular section may displease some too. Peculiarly, there is no easy facility for Textpattern to Textpattern apart from doing a MySQL database copy. Some alternatives to this were suggested but none seemed to work as well as the basic MySQL route. Tiny MCE made editing easier once I went and turned off Textile processing of the article text. This was done on a case by case basis because I didn’t want to have to deal with any unintended consequences arising from turning it off at a global level.

While on the subject of content, this is also the part of the interface where you manage files and graphics along with administering things like comments, categories and links (think blogroll from WordPress). Of these, it is the comment or link facilities that I don’t use and even have turned comments off in the Txp preferences. I use categories to bundle together similar articles for appearance on the same page and am getting to use the image and file management side of things as time goes on.

All in all, it seems to work well even if I wouldn’t recommend it to many to whom WordPress might be geared. My reason for saying that is because it is a technical tool and is used best if you are prepared to your hands dirtier from code cutting than other alternatives. I, for one, don’t mind that at all because working in that manner might actually suit me. Nevertheless, not all users of the system need to have the same level of knowledge or access and it is possible to set up users with different permissions to limit their exposure to the innards of the administration. In line with Textpattern’s being a publishing tool, you get roles such as Publisher (administrator in other platforms), Managing Editor, Copy Editor, Staff Writer, Freelancer, Designer and None. Those names may mean more to others but I have yet to check out what those access levels entail because I use it on a single user basis.

There may be omissions from Txp like graphical presentation of visitor statistics in place of the listings that are there now and the administration interface might do with a little polish but it does what I want from it and that makes those other considerations less important. That more cut down feel makes it that little more useful in my view and the fact that I have created A Wanderer’s Miscellany may help to prove the point. You might even care to take a look at it to see what can be done and I am sure that it isn’t even close to exhausting the talents of Textpattern. I can only hope that I have done justice to it in this post.

What about a book on WordPress development?

7th October 2007

Here’s something that I would really appreciate: a book on code cutting for WordPress customisation and extension. Having the Codex is all very fine but having a dead tree compendium that you can peruse at your leisure is a definite bonus. If there was a way to get the Codex on PDF and print it all off for easier reading, that would be progress. I believe that a publisher did plan to bring to the market something like what I want but author and publisher parted company, a pity. It would be great to see something like Apress’ Pro Drupal Development for WordPress. What about it, Automattic folks?

The joys of eBooks

3rd April 2007

One of the nice things about eBooks is the saving that you can make on buying one instead of the dead tree edition. And if you get one from Apress, it is the full article that you get and they keep it available so that you can download another version if you need it. You can also print the thing off if you want too but a laser printer producing double-sided prints is an asset if you don’t want your space invaded by a hoard of lever arch binders. Having a copious supply of inexpensive toner helps too as does cheap paper. Otherwise, you could spend your savings on printing the thing yourself.

The ever pervasive Safari does things a little differently from the likes of Apress. Mind you, the emphasis there is on the library aspect of the operation and not eBook selling. The result is that you can only ever download chapters, so no index or overall table of contents. You still can buy all of the chapters for a particular book, though some publishers don’t seem to allow this for some reason, but finding anything in there after you have had a read becomes an issue, especially when it’s the hard copy that you are using. Take yesterday, for instance, when trying to relocate the formatting parameters for the UNIX date function. I eventually found them in the chapters of UNIX in a Nutshell that I have downloaded and printed off but I spent rather longer looking in Learning the Korn Shell than I should have done. I know that you can search in the PDF’s themselves but that is more laborious when there is a number of files to search rather than just the one. I suppose that the likes of O’Reilly prefers you to buy paper copies of its books for more extensive use, and they have a point, but having the electronic version all in one file does make life so much easier.

  • All the views that you find expressed on here in postings and articles are mine alone and not those of any organisation with which I have any association, through work or otherwise. As regards editorial policy, whatever appears here is entirely of my own choice and not that of any other person or organisation.

  • Please note that everything you find here is copyrighted material. The content may be available to read without charge and without advertising but it is not to be reproduced without attribution. As it happens, a number of the images are sourced from stock libraries like iStockPhoto so they certainly are not for abstraction.

  • With regards to any comments left on the site, I expect them to be civil in tone of voice and reserve the right to reject any that are either inappropriate or irrelevant. Comment review is subject to automated processing as well as manual inspection but whatever is said is the sole responsibility of the individual contributor.