[mapserver-users] mapfile filter & variable substitution question

[pcreso at pcreso.com]

Hi Jennifer,

Outside of using mapscript to parse the the variable and generate the appropriate sql, there is a simple Postgres solution. Store your "year" column as a char(4) instead of int & index it. (So "like" will work on it without casting)

Have OpenLayers return a "%" for the "ALL YEARS"  selection (instead of a single year) and have your query in the mapfile use like as well as  "=" as below.

Individual years will be identified with the "=" operator & all years by "like", as below, where I pass the sql both a '%' & the year '2000' as examples.

HTH,

 Brent Wood


test=# create table test (id serial primary key , year char(4));
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
test=# insert into test values (default, '1999');
INSERT 0 1
test=# insert into test values (default, '2000');
INSERT 0 1
test=# insert into test values (default, '2001');
INSERT 0 1
test=# select * from test where year='%' or year like '%';
 id | year
----+------
  1 | 1999
  2 | 2000
  3 | 2001
(3 rows)

test=# select * from test where year='2000' or year like '2000';
 id | year
----+------
  2 | 2000
(1 row)






--- On Sun, 10/11/09, Jennifer Shanks <shanksje at yahoo.com> wrote:

> From: Jennifer Shanks <shanksje at yahoo.com>
> Subject: [mapserver-users] mapfile filter & variable substitution question
> To: mapserver-users at lists.osgeo.org
> Date: Sunday, October 11, 2009, 6:29 AM
> Hi,
> 
> I have a question about using the mapfile Filter option
> with variable 
> substitution.  In my user interface (OpenLayers), I
> have drop down boxes 
> that allow users to filter the data display based on
> selected year and 
> selected category.  Accordingly, I have two filters
> set up in my mapfile: 
> 
>   FILTER "year = %selected_year%" 
>   FILTER "category=%selected_category%" 
> 
> What if the user wants to see data for all years or for all
> categories?  
> Is there a way that I can submit a wildcard in my mapserv
> URL instead?  
> Or is there some other way to do this?  My data source
> is a PostGIS.
> 
> Regards,
> Jennifer
> 
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>