Educating the world

Our blog has over 10,000 readers a month

Searching for backslashes in MySQL with PHP

March 5th, 2012

Came across an interesting problem over the weekend. I had a MySQL database table that contains a column of MS-DOS directory paths. I was writing a PHP program to search this column and it took a few minutes to work out how many delimiting backslashes I needed. It was a bit fiddly and ought to be the type of thing you can just look up so here it is!

My table:

| id | folder            |
|  1 | \series\Black\004 |
|  2 | \series\White\012 |

The best way to show this is by doing it wrong and explaining why.

SELECT id, folder FROM folder
WHERE folder LIKE “%\White\%";

This won’t work because “\W” will be converted into a single character which won’t be a “W” any more and “\%” will be converted in to a single character that won’t be a “%” any more. We need to delimit the text to preserve the backslashes so the command can be submitted.

If we add another set of backslashes to form the following then we cam drop into the next level - the SQL engine parser.

SELECT id, folder FROM folder
WHERE folder LIKE “%\\White\\%";

The SQL engine parser reads the statement as “%\White\%” and so we have the same problem we had with the command line stage.

So lets add another set of backslashes to the statement.

SELECT id, folder FROM folder
WHERE folder LIKE “%\\\White\\\%";

Still not enough backslashes. This time we’ll get “\” followed by a single character “\W", which still won’t match.

So to get to our quarry we must add another set of backslashes.

SELECT id, folder FROM folder
WHERE folder LIKE “%\\\\White\\\\%";

The backslashes survive all the way through the command line, through the search engine to allow us to search for a single backslash in a string.

Now comes the fun bit. I needed to embed this SQL line into a PHP command, so I need to delimit all the backslashes i.e. double them all up.

$sql = “SELECT id, folder FROM folder
WHERE folder LIKE \"%\\\\\\\\White\\\\\\\\%\"";

..and don’t forget we need an extra set of backslashes to protect the quotation marks around the search filter.

b2evolution plugin for Google Analytics

March 2nd, 2012

All the b2evolution plugins that provide Google Analytics were old or dead. So I have written this one to replace them all.

This page is to provide comments and suggestion.

Source and instructions may be obtained from the download page.

Reading & Mid-Berkshire CAMRA - Real Ale Trail 2012 on plotted on Google Maps

March 1st, 2012

The Real Ale Trail is a campaign to get people to sample the delights of real ale in a pub they might not normally go to. Some of the pubs might not be in the Good Pub Guide but they’ll definitely be in the Good Beer Guide so you can be sure that your adventure will be satisfying.

You can get a booklet from any of the 28 pubs which you keep with you on your travels. Each time you buy a half or a pint of real ale in these pubs they give you a little sticker so you can mark it as done. If you complete all 28 pubs you are allowed to jump the queue for the 18th Reading CAMRA Beer and Cider Festival which is worth about 2 to 3 hours of queuing possibly in the rain!

I had a look at the Reading & Mid Berkshire Real Ale Trail page in order to plan my route around. This should have been a 5 minute job but it took me almost 2 hours to find them all, there just wasn’t enough information to locate them easily. The blame is not all Reading CAMRA’s web sites fault, a lot of the pubs weren’t easy to find by searching Google Maps. I suspect that Google Rankings aren’t at the top of the list when it comes to running a pub.

I intend to organise a pub crawl so wanted to plan my route. I didn’t want anyone else to go through all this trauma so I thought I’d share it with the rest of you real ale lovers out there.

If you click this link for Reading & Mid-Berkshire CAMRA - Real Ale Trail 2012 it will open a new window showing a bigger version of the map at Google.

The business of busking

February 23rd, 2012

I was walking through Reading town centre and passed a busker playing the violin. He was accompanied by a piano music recording and I have to say he was pretty good. Just out of hear shot there was another busker, this time a pianist playing on his own.

I went and had a chat with the pianist. I suggested to him that if they teamed up with the violinist there would probably be a greater likely hood of making more money as they could duet together and make better music. I also explained that having only one set of buskers in the centre would be better than one and each end of the high street because their combined catchment areas would increase. There were no other buskers in town on this day. There were a couple of Big Issue “salesmen” but I’m not sure they count as they are selling a different product.

The pianist explained that you’d have thought that it would work like that but it just doesn’t. He said it didn’t make any difference if you played in a group or individually. People will put money in the box because they want to help you rather than because they like the music. In fact making 1 group out of 2 is more likely to half profits because the public will only be able to donate once and you’ll have to split that into 2 pieces. He also commented that the violinist probably wouldn’t want to join up because his piano was slightly out of tune. The pianist wasn’t just there as a busker, he was also getting a kickback from the street vendors to who wanted people to leave the Oracle shopping centre and buy stuff from them.

Stack Overflow B2evolution plugin

February 8th, 2012

I spent quite a while looking for a B2Evolution plugin that places a Stack Overflow Flair widget on my blog. I couldn’t find one so I started writing one. If the Flair widget just required your user name then it would’ve been ok because everyone can remember that. Unfortunately the widget needs your user id which you will almost certainly not know. The only way to find this out is to log into Stack Overflow and go to the Flair’s page. The user identifier is embedded in the HTML code. So once you’ve got to this point you might as well just cut and paste the HTML code you want. A plugin isn’t worth writing if it doesn’t save you any time or effort.

So here is how you create a B2Evolution Stack Overflow widget on your blog.

  1. Log into B2Evolution back office.
  2. Go to Blog -> Settings.
  3. Select the Widgets tab.
  4. Choose the area you want to install the widget. (In my case Sidebar).
  5. Click Add widget.
  6. Click the Free HTML link.
  7. Add Stack Overflow to the Block title so that this widget shows in the list as Stack Overflow (Free HTML).
  8. Paste your Flair code in the Block content.
  9. I have checked Allow caching because the caching caches the code and not the image at the end of the code.
  10. Finish with Update.

Currently I’m using a hand rolled version of evopress which is one of B2Evolution’s built in skins. I had to adjust the width of the image from 206 to 205 so that it didn’t overlap the edge of the page.