I posted this on the drupal.org forum yesterday and have had an underwhelming response. :-)
Is it possible to construct a sql statement that does what I'm looking for?
(to save you clicking, reprinted below) ________________________________________________
I am interested in being able to attach what I'm calling a Hierarchical Geographical Code (HGC) either to nodes or to taxonomy terms and to use it to present more locally relevant content to site users. Here's how the code is constructed:
First 2 numbers for region/continent (01 for N.America, 02 for Europe, 03 for Asia, etc) Next 2 numbers - country (01 for USA, 02 for Canada, etc) Next 3 numbers - state/province (001 for Alabama, 002 for Alaska, etc) We need three digits as some countries (UK is one example) has more than 99 state-level entities. Next 3 numbers - city/town within a province/state (001 for Allentown, 002 for Astonbury, etc) The HGC is always 10 digits long. The US (whole) is therefore 0101000000 and so on.
so Dallas, Texas might have a HGC of 01|01|047|048 (would be entered as 0101047048)
Here's how I would like it to work:
I want blocks to show users content that is relevant to them. And moreover, relevant to the location they've selected in the nodeprofile "Location" field.
If a user from Dallas logs in, I want to show a block showing links/pages from Dallas or (and here's the trick), if there aren't pages from Dallas, then from Texas, then from the USA, then from North America.
Basically, the block would be constructed with sql which looks first to the right side of the HGC, then works its way along leftwards until it finds a match.
So the two components needed are:
1. the CCK field in my nodeprofile. The HGC is automatically added into the user's nodeprofile entry using computed field. The main locations db table has five columns:
region - country - state - town - hgc
This means every member who fills out their nodeprofile has a HGC automatically assigned to them.
This is already in place on my site.
2. The HGC tagged onto content.
Initially, my wish is to automatically tag a certain customised content type myself, but I imagine we could work out something to tag other common content types such as blog, forum, video content types. I could use a taxonomy drop-down on every node and automatically attach the HGC at the same time in the background.
Now, my big question is about the blog sql itself. Could someone tell me if it's possible to produce a block that "scans" for locally relevant content in the way I've outlined above. So, to paraphrase (I no speak sql!), it would be:
SHOW 'city' content type WHERE location EQUALS 0101047048 OR 0101047000 OR 0101000000 OR 0100000000
I hope someone understand the type of thing I'm looking for. Throw in some other ideas if you want. __________________________________________
I'm hoping someone on the support list will at least let me know if this type of content filtering is possible.
Neil