cgi mapserver query

[Pagurek,Debbie [NCR]]

This is really an SQL question - if you can produce an SQL statement
that links all of your tables together and view the correct results in
your query intereface (phpmyadmin for example) you should be able to
just insert that same SQL query in your OVF file.

I have done the same, but using Microsoft SQL Server.

A sample query that I use in my OVF file:
<SrcSQL>SELECT StationFedNumber as Site_number, StationLocationDesc_e as
Site_location_description, StationLatitude as Latitude, StationLongitude
as Longitude, ProvinceName_e as Province, WaterBodyTypeName_e as
Water_body_type_sampled, WaterBodyName_e as Water_body_name,
cast(YEAR(WQIBegDate) as char) + ' to ' + cast(YEAR(WQIEndDate) as char)
as Sampling_dates, mapquery_e as wat_query_map FROM dbo.WQIStation INNER
JOIN dbo.WQI ON dbo.WQIStation.WQIId = dbo.WQI.WQIId RIGHT OUTER JOIN
dbo.Station ON dbo.WQIStation.StationId = dbo.Station.StationId LEFT
OUTER JOIN dbo.WaterBodyType INNER JOIN dbo.WaterBody ON
dbo.WaterBodyType.WaterBodyTypeId = dbo.WaterBody.WaterBodyTypeId ON
dbo.Station.WaterBodyId = dbo.WaterBody.WaterBodyId LEFT OUTER JOIN
dbo.Mapquery2 ON dbo.Station.StationId = dbo.Mapquery2.stationId LEFT
OUTER JOIN dbo.Province ON dbo.Station.ProvinceId =
dbo.Province.ProvinceId</SrcSQL>

I recommend starting with your database interface to work out the proper
query.
Hope this helps.

D. Pagurek

-----Original Message-----
From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] On
Behalf Of Greg Luker
Sent: Wednesday, August 23, 2006 12:14 AM
To: MAPSERVER-USERS at LISTS.UMN.EDU
Subject: [UMN_MAPSERVER-USERS] cgi mapserver query

Hi Mapserver-Users,

I asked this question a week ago, but I have received no replies, so
I'll ask again:

I'm using cgi mapserver 4.8.3 (beginner) and I'm trying to do a
"relational"
query on a MySQL database. I have displayed points from a table using
the inline CONNECTION '<OGRVRTDataSource... <SrcSQL>SELECT... command in
the .map file, and I can do a simple query on these points (eg display
x, y, id, etc), but now I want to display all related records from
another table.
eg I have a Sites table and a Facilities table in MySQL. The Sites table
contains x,y locations of camping grounds. The Facilities table contains
facilities at each campsite (eg showers, cooking, shelter, etc). I can
display Sites as points on a map, but how do I get a list of all
Facilities at each Site? Do I use a JOIN statement? If so, how?
I have tried a few different things, but I can't make it work.

Any help or pointers to where I might find help would be greatly
appeciated!

Thanks,
Greg Luker.