Author Archives: lornajane

03 Dec
2007

Filtering Tables in Joins in MySQL

I had a situation recently where I was outer joining one table onto another. I needed all rows from the first table, plus any matching rows from the second table. So:

SELECT * 
FROM table1 t1
LEFT JOIN table2 t2 ON (t1.col_a = t2.col_a)

So far, so good, right? In this particular instance though I wanted to filter my results on a particular criteria so I added something like:

SELECT * 
FROM table1 t1
LEFT JOIN table2 t2 ON (t1.col_a = t2.col_a)
WHERE t2.col_b <> 42

This is where it all started to go a bit weird. I wanted all the rows from the first table, plus anything from the second table that matched but didn’t have col_b equal to 42 (this is a rather contrived example I know but my real-world example was so complicated I’d still be explaining it if I started). The syntax above was causing a problem in the case that there was a single matching row in the second table which matched the criteria in the where clause. In this instance, the entry from the first table would appear once, and then immediately get filtered out by the constraint in the where clause.

What I actually needed to do was filter the second table before the join happens. So here’s the syntax:

SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON (t1.col_a = t2.col_a AND t2.col_b <> 42)

It turns out that you can use the ON clause when you join to include anything that you would usually put in a where clause to restrict the rows on the table that is being joined. Hope this makes sense and is useful to someone, add your comments if you have any!

29 Nov
2007

PHPWomen Podcast

I’m wildly excited about a virtual event scheduled for next week, its the PHPWomen PHP|Abstract. Some of the main members, Elizabeth, Elizabeth, Ligaya and Sara are all going to chat with Cal Evans about the group, with live participation on IRC.

Actually I wonder if Cal knows what he is taking on here, he’s going to have his hands full trying to keep these four ladies under control :) He’s also running a competition – an elephpant for the best question asked in the IRC channel while the ‘cast is happening … I really want one of these critters so if you have any question suggestions then please pop them in the comments for me.

Its at 6PM CT which I think its midnight here in GMT (or UTC). You can listen live by dialling a toll-free number, only available from within the US however Skype’s documentation seems to think I can call a US toll-free number over skype from here in the UK so this will hopefully prove to allow access for anyone outside the US who would like to listen. Very importantly there will also be a real-time interaction with the channel on irc.freenode.net so drop in if you’d like to. If you’re new to IRC then find instructions on the IRC primer I wrote for PHPWomen. Hope to see some new faces joining us on this one!

25 Nov
2007

Happy Half House-Year

Today marks six months since we picked up the keys to our first home, a lot has happened! I thought this anniversary (is it strictly an anniversary, if its less than a year?) would be a good time to have a quick recap on what’s been going on here.

The first thing we did was ring a locksmith and get the locks changed. Probably an unneccessary expense but we’d completed on the house and bought the contents at short notice so it seemed prudent. Since we’d taken out house insurance with the mortgage we were happily covered from day one without having to mess about with any more paperwork.

We didn’t expect to be buying the house contents, and we certainly didn’t expect to get as much as we did. Although we don’t have a lot of furniture we had too much to fit into a house that was already full of a dining table, a king size bed, a king size wardrobe (its big anyway), a single bed, another triple wardrobe, a dresser, the entire contents of a kitchen … you get the picture (and remember those wardrobes were full of clothes!). We spent four or five days with all our stuff stacked in the front room and no idea how to solve the jigsaw puzzle of unfolding everything out into the house.

We held the housewarming 5 weeks after moving in and I think the house looked its best that day. We’d had the roof and guttering mended and patched up, and we’d chopped the weeds back to a manageable height (short enough to stand the barbecue on top anyway). Lots of friends joined us and almost unanimously described the house as “having potential” – by the end of the day we were wondering whether everyone thought we were mad.

Since then we’ve become an auntie and uncle which is fun, and we’ve had a lot more building work done in the house. There was no central heating so that has been added, and the boiler moved out of the living room (we think that room may have been the kitchen at one time, its not as random as it seems). In addition we’ve had the whole house rewired. Both these jobs have been disruptive, destructive and very messy!! Having not changed the bag in our vacuum cleaner for about three years, we’re now on our fourth bag in six months, that statistic goes some way to describing how much mess we’ve dealt with. When we first bought the house and I blogged about it with some photos, someone who had been here said to me “what doesn’t come across in the photos is the DIRT”! The only way to describe how filthy this place was when we bought it, and then again when we had the heating done, and then again when we had the wiring done, is to say that its the only time I’ve blown my nose and observed that my snot has changed colour…

Today the house is a bit of a disaster. Its clean (we had visitors earlier) but every single room has at least one hole in the wall/floor/coving/all of the above. There are floorboards damaged in quite a few places and lots of disused sockets and switches hanging about the place. It looks a LOT worse than it did for the housewarming but strangely I feel a lot better. Less than a year ago I blogged about pulling out of a second house sale and I was feeling really low about it. 12 months on and we’ve bought a much nicer (and bigger) place, paid 10% deposit and fees, had the roof sorted, survived the trauma of having the place flooded, put in central heating and had the place rewired. I really feel, despite the mess that we’ve done really well to get this far. And now, the workmen are gone, and we can make the place our own. We can start decorating and putting up shelves in our lovely deep alcoves. We can choose new floors to replace the ones that got damaged. Perhaps over the winter I’ll find time to sew the bedroom curtains I bought the material for in about July.

The plan for now though is to start repairing and sorting everything out. We’re starting with what will be a little study – mostly so we can practise on it before moving into more high-profile rooms. We’ll then decorate the living room and replace the (currently missing) floors in the hall and living room. That’ll take us well into the new year and will still be lots more holes to repair and fill … so here’s hoping for many more happy half house-years to come!

18 Nov
2007

BarCampLeeds Recap

I did it! I went and spoke at BarCampLeeds, there’s flickr evidence to prove it as well :)

My talk was “Source Control Saves Lives”, a primer on what source control and why you should use it, with a definite subversion twist because that’s what I know the most about and I’m not a fan of talking about stuff I don’t know about! For posterity, here are the slides – I haven’t done any presentations for years and I couldn’t remember how to work powerpoint svn_saves_lives.ppt

I borrowed Kevin’s mac and downloaded the demo of iWork. Its a 30 day trial and after its expired I can still use it as a player. Which is excellent as using the mac remote in the talk worked really well and got around the awkward layout of the room I was allocated.

The event as a whole was fabulous – I met some amazing new people and have a stack of business cards of people to email and catch up with. I don’t have my own cards which is proving really awkward at this kind of event, perhaps I will get some in the future when I’m attending something like this since they’re not expensive. The new contacts I made could turn out to be really interesting in a whole load of ways – and the free beer and chatter with like-minded people was very welcome as well. Talking over experiences and projects with others has left me refreshed and inspired, in a way I didn’t expect. What more could a girl want?

15 Nov
2007

Thinking of BarCamp Leeds

Recently I found out about a phenomenon called BarCamp, where a bunch of people, mostly geeks, turn up and run their own un-conference. Every attendee speaks, at least that’s the theory, and there are a lot of concurrent sessions – so you just attend what you want to. It looks interesting and although nobody is making all attendees speak, I figure its good to join in with these things.

Bar Camp Leeds is this weekend, Saturday 17th November 2007, and its wildly oversubscribed. I know a few people who were hoping to go and haven’t been able to get places. At the time of writing, I’m still planning to speak but there’s less than 48 hours to go and I haven’t got further than deciding I may speak about source control, and subversion specifically. The nerves are starting to kick in …

14 Nov
2007

PDO for PHP on Ubuntu Edgy Eft

Today I was asked to add PDO support to PHP on our development box, which I thought was running Ubuntu Feisty Fawn. The first problem is that it’s not running Feisty Fawn, its running Edgy Eft, which is an older version. The next problem is that the PDO libraries weren’t bundled with Eft.

PDO is available in PECL so, using these instructions from Rich Buggy I grabbed the mysql client development libraries from aptitude and then got the PDO libraries I needed.

13 Nov
2007

The Tradesman Cometh

The house, as I may have mentioned, has been in disarray lately. Since buying the place at the end of May we’ve had the guttering and verge boards replaced, central heating fitted (involving moving the boiler), and the entire house rewired including the bonus addition of smoke detectors, outside lights, switches that are halfway logical, and a bonus extractor fan. I think its probably best if the stories of an electricity supply which has no earth, sockets were seen to be melted inside, junction boxes in every wall and floor, and the moment we discovered that turning off our electricity at source turns off next door’s as well, are all saved for another day.

All the building work has meant that we’ve done almost nothing to the interior of the house, knowing there would be mending to do when it was finished. So now I have channels in the walls:

Unfortunately due to the complications of doing the wiring (the electrician says he’s done hotels faster than he did our place), there has been some damage that we didn’t anticipate. Not all the the floors are going to back the way they were, so we may have to live with floorboards for a while. I was surprised to find that the floorboards in the hallway are painted:

We’ve done well to get all the work done quite early on, but its going to be a long winter of learning to DIY – wish us luck!!

PS If the title doesn’t mean anything, then you need to know of The Gas Man Cometh.

09 Nov
2007

Haywire Rewire

I’ve been away for five days, and got back home yesterday (Thursday) early afternoon, due to work Friday as usual and then head off a weekend away. While we were away the electrician was coming in to rewire our house – an expensive operation but a very much-needed one. The inside of some of the sockets had melted and the consumer unit regularly emitted sparks!!

Picture the scene, I flew back into the country yesterday, got back to Leeds, got a taxi home, and was wandering up the road looking in my bag for my keys when I realised I could hear something. Hammering. Coming out of my open front door into the street accompanied with a lot of brick dust.

We went in to the house to be met by an agitated electrician. He’s had a nightmare of a time with our house, the existing wiring was a mess and had junction boxes everywhere so he’s ended up drilling into more of our (solid brick, even on the internal) walls and pulling up more of our floors than he’d expected. So the floors are mostly up on the ground floor and they haven’t started the basement yet, i.e. they’re a bit behind schedule, and putting all the floors back down and making the house safe for habitation wasn’t going to help.

So I went in the house, opened my suitcase, removed the bag of dirty washing, replaced its void with clean underwear and t-shirts, shut the suitcase again and put the suitcase, the rucksack I had with me when I got home, and my laptop into the car. My employers were relatively relaxed about me working remotely today so we headed off a day early for our weekend in Northumberland and today I’ve been working from my parents’ place in Alnwick.

Fingers crossed when we get home on Sunday things will have calmed down a bit … although I suspect the mess will still be eye-watering!

07 Nov
2007

No Blogroll

I’ve had a few requests recently to exchange links or to add people to my blogroll, its a way of giving a bit of kudos to people that you admire and whose work you read. The thing is, I don’t have a blogroll, and if I did I fear it would cause more problem than it solves. As a minimum, I’d need to consider:

  • where to put it on the site, its already quite cluttered
  • who should go on it
  • how to maintain it to reflect my reading habits as they evolve – maybe a direct link to my feed reader?
  • how do I prune it if it gets too big? Its like deleting friends from your phone but much much more public!

Its not that I don’t want to share some link love around either – there are many blogs I read that I’d like to own up to loving. Including (but not limited to) GirlsCantWhat, PHPWomen, Lig, Sara, Davey, Matthew, Jon, Ubuntu Tutorials, DevZone, Simon, DevChix and of course XKCD and Dilbert.

Is it a problem if I don’t have a blogroll? I quite like my site the way it is, I enjoy reading other people’s sites too and I share my appreciation usually through commenting. I’m not sure I should add something like this when I clearly don’t have the enthusiasm for it, what do you think?