I would like to allow users to query a point on a map and return data values for unemployment rates for 12 months of the selected year.<br><br><br>My code looks like this:<br><br><br>$sql = "SELECT uc.the_geom, uc.gid
, at.value, at.date<br> FROM geom_usa_counties AS uc, <br> (SELECT fo.value AS value, fc.state_fips, fc.county_fips, fo.period_start_date as date<br> FROM fred_category_to_state_county_fips AS fc, fred_series_in_category AS fsic, fred_observation AS fo
<br> WHERE fc.cat_id=fsic.cat_id<br> AND fsic.series_id=fo.series_id<br> AND fsic.series_id LIKE '%$_GET['series']'<br> AND fc.cat_id IN (SELECT cat_id FROM fred_category_ancestor WHERE ancestor_id BETWEEN 195 AND 201)
<br> AND fo.period_start_date BETWEEN '{$_GET['year']}-01-01' AND '{$_GET['year']}-12-31' <br> ) AS at<br> WHERE uc.state_fips=at.state_fips<br> AND uc.cnty_fips=at.county_fips
";<br><br>$fred_layer = $map->getLayer(1);<br>$fred_layer->set("data" , "the_geom from ($sql) as foo using unique gid using SRID=4269");<br><br>$fred_layer->queryByPoint($query_point_obj , MS_MULTIPLE , -1);
<br>$num_results = $fred_layer->getNumResults();<br><br>$fred_layer->open();<br><br>for($x = 1 ; $x <= $num_results ; $x++)<br>{<br> $result = $fred_layer->getResult($x);<br> $shape = $fred_layer->getShape($result->tileindex,$result->shapeindex);
<br> if($shape) <br> {<br> echo $shape->getValue($fred_layer , 'date') . ': ' . $shape->getValue($fred_layer , 'value') . '<br>'<br> }<br>}<br><br>$fred_layer->close();<br><br><br>It correctly retrieves 12 results But when it prints out the date and value for each result they are exactly the same.
<br><br>1990-01-01: 9.9<br>1990-01-01: 9.9<br>1990-01-01: 9.9<br>1990-01-01: 9.9<br>1990-01-01: 9.9<br>1990-01-01: 9.9<br>1990-01-01: 9.9<br>1990-01-01: 9.9<br>1990-01-01: 9.9<br>1990-01-01: 9.9<br>1990-01-01: 9.9<br>1990-01-01:
9.9<br><br><br><br>I took a look at the postgresql log, and the query being used is:<br><br><br><br>SELECT gid::text,value::text,date::text,asbinary(force_collection(force_2d(the_geom)),'NDR') <br>FROM (<br> SELECT uc.the_geom
, uc.gid, at.value, at.date<br> FROM geom_usa_counties AS uc, <br> (SELECT fo.value AS value, fc.state_fips, fc.county_fips, fo.period_start_date as date<br> FROM fred_category_to_state_county_fips AS fc, fred_series_in_category AS fsic, fred_observation AS fo
<br> WHERE fc.cat_id=fsic.cat_id<br> AND fsic.series_id=fo.series_id<br> AND fsic.series_id LIKE '%URN'<br> AND fc.cat_id IN (SELECT cat_id FROM fred_category_ancestor WHERE ancestor_id BETWEEN 195 AND 201)
<br> AND fo.period_start_date BETWEEN '1990-01-01' AND '1990-12-31' <br> ) AS at<br> WHERE uc.state_fips=at.state_fips<br> AND uc.cnty_fips=at.county_fips<br>) as foo WHERE gid = 2362<br><br><br>
<br>Which run by itself in pgsql correctly returns the data values for 12 months.<br><br> 2362 | 9.900 | 1990-01-01 |<br> 2362 | 8.800 | 1990-02-01 |<br> 2362 | 7.900 | 1990-03-01 |<br> 2362 | 7.200 | 1990-04-01 |<br> 2362 |
6.700 | 1990-05-01 |<br> 2362 | 8.000 | 1990-06-01 |<br> 2362 | 7.000 | 1990-07-01 |<br> 2362 | 6.700 | 1990-08-01 |<br> 2362 | 7.100 | 1990-09-01 |<br> 2362 | 6.300 | 1990-10-01 |<br> 2362 | 7.400 | 1990-11-01 |<br> 2362 |
9.200 | 1990-12-01 |<br><br>Mapserver obviously sees 12 results, but it is returning the first result 12 times. Does anyone know what's going on or have any suggestions?<br><br>Thanks!<br>Julie