mySQL query solution

I had been trying very hard to avoid having to do a nested query, but in the end, I really couldn't figure out a way around it. This is the query that gets the job done:


mysql> select tnid from fr_strings where sid NOT IN (SELECT sid from fr_tr_by_uid where uid = 1 and language = 'es');


If you have a non-nested solution for this problem, I'd still love to see it!

mySQL query trouble

I'm having a hellish time with a sql statement. I need to get nodes ("tnid" here) where a given user (uid = 1) has not translated a given language (language = "he", or rather, != as you'll see)

fr_strings has sid and tnid (those are the only ones important to us now, anyway)
fr_tr_by_uid has sid, uid and language

When someone translates a string, it pops in their user id, the string id and the language that they translated the string to. So, say, if I am translating into es or he (spanish or hebrew) I should no longer see the string pop up for translation in es if I've already done that translation, but the request for the string translation would still pop up when I'm working on the he translation. Get it? OK.

So, I want to select the tnid's where uid != 1 and language != 'he'. Something like that. Let's look at some queries and responses:


mysql> select distinct * from fr_strings as st natural left join fr_tr_by_uid as ui where sid > 279;
+-----+------+------+----------+
| sid | tnid | uid | language |
+-----+------+------+----------+
| 280 | 870 | 2 | es |
| 280 | 870 | 1 | es |
| 281 | 871 | 1 | es |
| 283 | 873 | NULL | NULL |
+-----+------+------+----------+


What I want out of this bunch here is tnid 283. That's all. That's the ONLY one I want. But if I try this:


mysql> select distinct tnid from fr_strings as st natural left join fr_tr_by_uid as ui where sid > 279 AND uid != 1;
+------+
| tnid |
+------+
| 870 |
+------+

That's clearly not what I wanted.

How about This:

mysql> select distinct tnid from fr_strings as st natural left join fr_tr_by_uid as ui where sid > 279 AND uid != 1 OR uid is null;
+------+
| tnid |
+------+
| 870 |
| 873 |
+------+


Still not there. As you can see, since the tnid shows up once in the table under a different uid, I still get that tnid back, even though one record with that tnid does have the uid=1.

I'm sure that this is something really stupid. (I'm always sure when I can't figure something out that the solution is something really stupid.) But, I can't shake the answer out of my head. Any of you know what I'm doing wrong?

*edit* I got a message suggesting that I ought to just do uid is null and ignore the uid != 1 part. But here is why that doesn't work for me...

Let's say that I want user 2 to get all the strings that she hasn't translated into Spanish yet.


mysql> select distinct tnid from fr_strings as st natural left join fr_tr_by_uid as ui where sid > 279 AND (uid is null OR language != "es");
+------+
| tnid |
+------+
| 872 |
| 873 |
+------+


This doesn't work. You see, uid 2 DID translate 870 into Spanish, but not 871 (see the first results table). I need her to get 871 for translation still, but this query won't give it to her. I'm still stuck.

Finding a host I couldn't see

Last week I finally broke down and asked IT if they would mind if I switched over to a Linux desktop for my daily development work. Functioning in Windows was driving me absolutely bonkers. He wasn't so sure about it at first. He suggested that I run Windows and then have Linux running in virtualization. That just seemed like a waste of resources to me, though. So, I suggested that I set the computer up for dual boot, and if things went badly I could always go back to Windows.

The IT guy's concern was that, while I might be able to do my programming in Linux, I might not have access to all the company shares, the exchange server for mail, and all that other Windows-based stuff sitting on the company network. And he was right to be concerned. In the past, all those things have been a major pain in the backside to fix.

The good news is that Samba, the tool that lets you hook up Linux or Unix machines to a Windows network, has come a very long way, and the whole thing turned out to be pathetically easy. Not only that, but Evolution for mail kicks the proverbial butt, and I was able to get mail, tasks, contacts, and calendar from exchange working perfectly with the Exchange server with just a few button clicks and no sweat at all.

But, then I need to look something up on the company wiki, and that's when I hit trouble. Firefox turned my http://companywiki/ url into http://www.companywiki.com. Woops! I tried a couple of times before it sunk in to my thick skull that the problem was that my computer wasn't recognizing the internal domain names. Clearly I need another Domain Name Server in my list, but the problem is that I don't want to go back to that IT guy just now. He may just tell me it's all Linux's fault, and that I have to switch back. I don't want that. So, I have a work around.

I checked in Windows what the ip address for companywiki is supposed to be, and then, back in Linux, I edited my hosts file to point the name companywiki to that address. Poof! I have normal access to the wiki using the domain name now. Easy peasy.

There are other uses for the hosts file, too. For instance, if I have server settings in a Web application that I'm working on, I can tell my hosts file that those server names are really my computer. My computer will look where the hosts file tells it to look before asking the Domain Name Server for information, so I can test the application without having to change those server name variables in the code.

Linux is not the only system with a hosts file that can let you find servers by a certain name, either. You can find a list of where to find the hosts file on different operating systems at Wikipedia.

A bit of fring development trivia

So, if you are a developer of things webbish or things mobile phone-ish, you may already know that the company I work for, fring released an API for creating various applications and mashups inside our software platform. The name of our special XML-based language is FAXL, pronounced "Fah-zel". The server that runs the fring applications is called the FIS. One of the FIS servers we used for development was named "inigomontoya".

In the last few days leading up to launch, there was a bastardized quote that rang through the air at seemingly random times, "My name is Inigo Montoya. You killed my FAXL. Prepare to die!"

Yeah, software development deadlines are not conducive to mental stability. Need I say more?

PHP and AJAX *head desk*

So, Thursday afternoon I broke my brain for the weekend trying to figure out why the heck a script that is working perfectly well on one server isn't working right on another server. Day one was wasted trying to figure out HOW to figure out what the heck was going on. The logs told me nothing. The page itself was not giving any hints. Since the whole thing was happening in AJAX land, behind the scenes and without giving any useful output to the browser, I couldn't use debugging tricks like echo and print_r to figure out what was going on with my variables. This morning, however, my brain was in better gear than it was Thursday afternoon, so I started the day off by actually doing something useful.

When you need a script to give you useful information in the browser, and the page in question is using AJAX to send information (but not, at the moment, receive information), then (DUH!) use AJAX to give you the information you need!!!

In my case I had a handy editor box in which to plunk the response text from the AJAX request.

xmlhttp.onreadystatechange = function(){
if (xmlhttp.readyState == 4) {

document.forms["editorbox"].editorBox.value = xmlhttp.responseText;
}
}

At one point, I used alert(xmlhttp.responseText) instead of the editorbox, but that wasn't as useful because the alert box doesn't let you select and copy stuff if you need it. Also, my editorbox had a handy-dandy scroll bar, and the errors coming out of my responseText were numerous.

In order to make the responseText useful, you may want to echo out certain variables and then exit; so that the info you want -- and only the info you want -- will show up where you need it.

Now, as to what was actually wrong with the script I was messing with today... It appears that a difference in php.ini files was my problem. In the version of the script that was NOT working, all the quotes were getting escaped as they got soaked up into the PHP script. When I then tried to dom->loadXML($myXML) the script was complaining that my XML was no good. Well, of course it's no good, there's all these darned escape characters before the quotes!

I don't want to change the php.ini on the server, so I have to get rid of those escapes some other way. preg_replace to the rescue!


$pat = '/\\\"/';
$rep = "'";
$myXML = preg_replace($pat, $rep, $myXML);


All better. :)

Good User Interfaces

I have a friend who loves to complain about the user interfaces in current technology. He has a million great things to say about how things should be made, and he says them with a cantankerous grumble. I adore him for so many reasons, but this is definitely one of them. He opens my eyes again and again to my own tendency to develop for geeks like me instead of the person on the street who really should be getting a better thought out and better built product. Because of him, I also notice even more when someone does something really right.

A couple of weeks ago he and I were in an elevator and he hit the wrong button by accident. He grumbled and hit the correct button and then said, "You know, if elevators were made correctly, I'd be able to cancel that wrong choice. I should be able to hit that button again and make it turn off."

Today, when I got into the elevator at work, someone else got in, pressed the wrong button, noticed the mistake and pressed the button again to cancel the request. Then they pressed the floor they really wanted. Oo! I was so excited I had to twitter it right there in the elevator.

It turns out that all new Mitsubishi elevators have that functionality. Way to go Mitsubishi.

Another one of my friend's pet peeves is that you have to save files. Why should you have to do an action to save a file? It should be automatic, right? Why would I create a file if I didn't want to save it? Well, it's getting to be automatic in some places like Google Docs for instance. Well, today I found another really cool app that deals with the saving thing right along with at least a dozen other major UI annoyances.

Skitch is unfortunately a Mac only application and it's still in beta, but it looks absolutely awesome. They get so many things right. They have a great way to share things, to snap screenshots, to draw and manipulate images, and you don't have to save your file from a menu or a keystroke. You can just drag it to where you want it to live, or drag it to an email to share it. Very, very nice.

Having Trouble Importing Drupal Translation Packs?

OMG I just spent way too long pulling my hair out over the Drupal 6 translation import feature. To put it simply, the handbook and help information is very UNCLEAR about what you are supposed to do.

The instructions say to download the translation pack into your install directory and unzip it. So, if, like me, you don't have access to the server that will allow you to unzip a folder on the server, logic says that you should be able to unzip the folder on your own machine and simply ftp the folder into place, right? Wrong. Well, at least sort of wrong.

Let's say you have the he-6.x-1.0 translation pack. You've unzipped it, and now it's sitting on your PC's desktop. You want to move it to your server. DO NOT copy that whole folder over to the server so that you end up with something like /webroot/drupal/he-6.x-10 on your server.

What you DO want to do is copy the CONTENTS of he-6.x-10 over onto the server so that the internal directory structures plop into place inside the existing directory structure in drupal. That way you will have something like /webroot/drupal/modules/aggregator/translations/modules-aggregator.he.po and so forth.

As you can see here, each directory that has translations (modules, themes, etc. anything that has t() strings, really) gets it own translations directory and language po files go in there.

The easiest way to get all of those files to plonk into the right place is, in fact, to un-zip the file insitu at your server rather than trying to ftp the files over post-expansion.