I have a demo of the latest version up now !
See your lovely query in action :)
http://free-host.no-ip.org/clive
I made your query a subquery on the locations table like:
$query .= "SELECT * FROM xmb_gm_locations WHERE region_id IN (
"SELECT d.region_id ".
"FROM xmb_gm_regions r ".
"INNER JOIN xmb_gm_regions d ".
"ON r.lft <= d.lft ".
"AND r.rgt >= d.rgt ".
"WHERE r.region_id = '".$region."' ".
"AND d.zoom <= $zoom ".
"AND NOT EXISTS ( ".
"SELECT NULL ".
"FROM xmb_gm_regions a ".
"WHERE a.lft < d.lft ".
"AND a.rgt > d.rgt ".
"AND r.lft <= a.lft ".
"AND r.rgt >= a.rgt ".
"AND a.zoom <= $zoom ".
"AND a.zoom >= d.zoom ))";
When a user is selecting regions, I don't actually use your code, as each time I only need to pull the children of the selected region, I just use the parent_id of the region (Is it bad to have an adjacency list parent_id AND nested set lft, rgt fields ?)
like so:
SELECT name, parent_id FROM xmb_gm_regions WHERE region_id='$curr_id'
I have another question...
How would I modify that query to add an extra field on the end of the returned data that included a COUNT of all locations in that region (the table xmb_gm_locations has a region_id field in it)
Ideally the field should contain the number of locations in that region and it's child regions. (ie for the row UK, it should contain a count of all locations with a region id that is a child of UK, not just in the UK region itself)
I tried solving this one for like 5 or 6 hours, fried my brain and gave up :(