<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.6000.16608" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>I posted this on the drupal.org forum yesterday and
have had an underwhelming response. :-)</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Is it possible to construct a sql statement that
does what I'm looking for?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2><A
href="http://drupal.org/node/235292">http://drupal.org/node/235292</A></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>(to save you clicking, reprinted
below)</FONT></DIV>
<DIV><FONT face=Arial
size=2>________________________________________________<BR></FONT></DIV>
<DIV>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.</DIV>
<DIV>
<DIV class=content>
<P>Here's how the code is constructed:</P>
<P>First 2 numbers for region/continent (01 for N.America, 02 for Europe, 03 for
Asia, etc)<BR>Next 2 numbers - country (01 for USA, 02 for Canada, etc)<BR>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.<BR>Next 3 numbers - city/town within a province/state (001 for
Allentown, 002 for Astonbury, etc)<BR>The HGC is always 10 digits long. The US
(whole) is therefore 0101000000 and so on.</P>
<P>so Dallas, Texas might have a HGC of 01|01|047|048 (would be entered as
0101047048)</P>
<P>Here's how I would like it to work:</P>
<P>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.</P>
<P>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.</P>
<P>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.</P>
<P>So the two components needed are:</P>
<P>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:</P>
<P>region - country - state - town - hgc</P>
<P>This means every member who fills out their nodeprofile has a HGC
automatically assigned to them.</P>
<P>This is already in place on my site.</P>
<P>2. The HGC tagged onto content.</P>
<P>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.</P>
<P>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:</P>
<P>SHOW 'city' content type WHERE location EQUALS 0101047048 OR 0101047000 OR
0101000000 OR 0100000000</P>
<P>I hope someone understand the type of thing I'm looking for. Throw in some
other ideas if you want.<BR>__________________________________________</P>
<P><FONT face=Arial size=2>I'm hoping someone on the support list will at least
let me know if this type of content filtering is possible.</FONT></P>
<P><FONT face=Arial size=2>Neil</FONT></P></DIV></DIV></BODY></HTML>