1 | 2

1 - 5   [7]

BT Broadband Very Slow Bandwidth

Relates to Blogging

When trying to download a large file from a client yesterday afternoon I couldn't help but get impatient at the slow bandwidth and shockingly poor internet connection I was getting. In fact it was so slow that the download speed was rarely exceeding 10Kbps (that is bits not bytes)! A disgraceful level of performance from my BT Total Broadband upto 8Mbps. Now everyone knows the spiel these ISPs dish out about their upto claims but I had previously been reaching speeds of 3Mbps and the hub software had always shown my connection to be 3,520 Kbps. When I hoped over to the hub (at http://192.168.1.254) I was appalled to see the downstream speed now being reported at 1024Kbps! What is going on with BT service - do they just arbitrarily cap users service or something…

Fortunately I have been forgiven the anguish of dealing with an inept and robotic support team over the telephone since a reboot of the hub has restored the reported speed to 3Mbps and I am getting accepatable download speeds once again. Not sure what this says about service capping on an IP basis and exactly what information BT are extracting and storing from our web usage.

But, before you waste your breath (and money) try a reset - just switch the hub off and back on again. It worked for me!

Posted on Jan 27, 2009 at 12:44:26.

2009 Oscar Nominations!

Relates to Movies

The nominations are out for the 2009 Oscars to be held on February 22nd and it looks like Britain is in with a hope - perhaps! Unless of course we see a LOTR whitewash of the gongs by the Curious Case of Benjamin Button which is up for 13 awards. Danny Boyle's Slumdog Millionaire is snapping at its heels with 10 nominations and both will face off for the coveted best picture along with Milk, The Reader and Frost/Nixon.

Looks like it is time to catch up on a bit of movie watching and see a few of these shortlisted films!

Great to see Heath Ledger's nomination for his riveting portrayal of the Joker in The Dark Knight. Finally watching the Bayle Batman sequel last weekend I couldn't help but be impressed by his portrayal of a character showing sociopathic tendencies glossed over with an unnerving frailty and desire to self-murder. Yet despite this gloom in the dark world of Batman, Ledger's Joker entertained moments of unexpected comic genius almost dragging the character straight off the pages of the comic book - the hospital scene a laugh out loud example!

See the full list of Oscar nominations for 2009.

Posted on Jan 22, 2009 at 14:47:26.

String Concatenation in MySQL Aggregate Queries

Relates to MySQL

For a minute I was thinking about building a stored procedure to handle this until I had a quick read up on the aggregate functions in the MySQL manual!

In brief I wanted to get a list of names from a one-to-many relationship - in this case multiple owners for a single property. These needed to be displayed as a single record in a form field and ideally the information should be drawn from the database in the same query as all the other information for the property. When dealing with numeric data, summation or unit count of the many side of a one-to-many relationship is quite simple using the aggregation functions (eg SUM() and COUNT()). As it turns out a similar function is available for strings (as of MySQL 4.1) and this is GROUP_CONCAT().

So for example I have:


====== data_properties ======
prop_id     10
prop_name   "This House"
=============================

======= data_owners =========
owner_id     12
owner_name   "Joe Blogs"
=============================
owner_id     13
owner_id     "Phil Blogs"
=============================

= data_properties_to_owners =
prop_id      10
owner_id     12
=============================
prop_id      10
owner_id     13
=============================

And I want to get a result set along the following lines:


Object {
  prop_id => 10
  prop_name => "The House"
  owners => "Joe Blogs, Phil Blogs"
}

In a single query we can do this using the following DML query:


SELECT 
  a.prop_id, a.prop_name, 
  GROUP_CONCAT(
    DISTINCT b.owner_name 
    ORDER BY ASC
    SEPARATOR ", "
  ) as owners
FROM 
  data_properties as a, 
  data_owners as b,
  data_properties_to_owners as c
WHERE 
  a.prop_id = c.prop_id and c.owner_id = b.owner_id
GROUP BY a.prop_id

So this query would take the following dataset:


=== prop_id ==  prop_name  ==  owner_name   ===
===    10   == "The House" ==  "Joe Blogs"  ===
===    10   == "The House" ==  "Phil Blogs" ===

And groups it on the last field to produce the required resultset. This reduces the demands on the PHP API to manipulate the data from the resultset prior to delivery to the templates.

GROUP_CONCAT() function has several parameters which can help further manipulate the resultset to get exactly the data required. See the aggregate functions in the MySQL manual for more information.

Posted on Jan 21, 2009 at 11:19:48.

Breadcrumbs Trail

[ Home ] -> TW Blog -> Archives for January 2009
Site Map

The Severn Solutions website achieves the following standards:

[ XHTML 1.0 ] [ CSS 2 ] [ WAI AA ] [ Bobby AA ]

Page compiled in 0.009 seconds