The only really effective units are two piece - where the heat is taken outside of the space that you are cooling and shed via a heat exchanger elsewhere.. any of the one-piece solutions cannot actually take away any heat from the room for you..
The units at B&Q are a proper fitted unit with a cool air blower fitted inside and the the condenser and heat exchanger needing to be mounted outside, and the relevant plumbing between. given the price Ive paid at some of our sites for this kind of kit, the B&Q stuff is superb at the price (less than £600 even at full price) but you need to be sure you want it and the hassle of fitting it.. Saw it running though, (they had one fitted in a conservatory I was looking at there) and it was very effective and very quiet.. I am considering them for one or two of our offices..
We have some of the cool air blowers like IanL is talking about at work.. you put the ice blocks in, and top up the reservior of water. There is a band of cloth that has one end submerged in the resulting cool water, which then slowly rotates so that the cool wet bit is behind the fan blowing the air into the room.. yes its a cool breeze, but on the basis of the four we have in a fairly large space I would agree with ianL, and would suggest that they are not even as good as a decent fan near a window!