Mysql

[William Kyngesburye]

After digging around the list archives and dealing with software  
updates and bugs, I finally figured this out yesterday (what  
timing!).  The main key is that you use GDAL, not MapServer directly  
or ODBC.

What you need then, is GDAL 1.3.1 with MySQL table support.   
MapServer version shouldn't matter.  There are a couple sticky  
problems, tho.  One is that GDAL automatically detects a feature ID  
field in the database and doesn't include it in the field list for  
the table.  I don't know the particulars for how it decides it a  
field is an 'FID', but it's something like, integer field as a  
primary key.  You should be OK with string fields.

You can check what OGR sees by duplicating the MySQL connection  
string aprt from below (without the .table part, but with tables=)  
with ogrinfo on the commandline:

ogrinfo -al -so  
'MYSQL:database,host=whatever,user=whatever,password=whatever,tables=you 
rtable'

Another problem is that GDAL curently doesn't detect tinyints or  
smallints (thinks they're strings), so if those are used for your  
relate field, it may not work.

Now for the MAP file stuff:

CONNECTIONTYPE ogr
CONNECTION "asmroad.shp"
DATA "SELECT * FROM asmroad LEFT JOIN  
'MYSQL:test,host=localhost,user=username,password=userpass'.route_na  
ON asmroad.road_name = route_na.roadjoin1"

Assuming I'm deciphering the connection/join statements right, they  
don't seem to be documented yet.  You don't need the JOIN block.

Basically, CONNECTIONTYPE will always be OGR.  CONNECTION can be one  
of 2 things: either the full relative path to the shapefile as it  
would be in a DATA statement + .shp, it must include the full file  
name, unlike in DATA statements; or if the shapefile is in subfolder  
you can list just the subfolder, because of the way OGR handles  
datasources and layers, but it can't be empty (CONNECTION "").

The OGR mysql connection string format (see also the OGR  
documentation for the MySQL format) is  
'MYSQL:database,host=,user=,password=,tables='.table.  Host defaults  
to localhost, password can be left out if there is none.  Tables  
(inside the quote) is a semicolon-seprated list of tables to restrict  
the search to (can speed things up when there are many tables), and  
table (after the dot) is the table you want.

The join part, ON shapedbf.joinfield1 = mysqltable.joinfield2 should  
be easy to figure out.

Be aware that I only tried this so far on joining by integer fields.   
Haven't tried string fields yet.

On Apr 11, 2006, at 5:08 AM, grabiel wrote:

>
>
>
>  Hi
>     I am trying to connect Mysql table for non spatial data. But in  
> the final output in the browser all the fields from the .DBF files  
> are shown, no data from mysql table are added. I also manipulated  
> the To and FROM field in the map file but its all the same. If i  
> change     the table name in the mapfile the result is same. I  
> guess its not working at all.
>
>      My mapfile is something like
>
>       ...................
>     LAYER
>     NAME "asmroad"
>     TYPE LINE
>     STATUS Off
>     DATA "asmroad"
>       CLASS
>
> 	NAME "Road"
>         TEMPLATE "road.html"		
> 	COLOR 200 50 100
>       END
>
>       HEADER "road_header.html"
>       FOOTER "road_footer.html"
>
>      JOIN
>        CONNECTIONTYPE mysql
>        CONNECTION "localhost:mysql:zxmnt:test"
>        TABLE "amroad"
>        TO "route_na"
>        NAME "roadjoin1"
>        FROM "road_name"
>     END
>  END
>
>   I am using the PHP/mapscript. Whether any DSN should be created  
> for these type of data access?
>   Hoping to get suggestion from the list members.
>   Thanks
>
>   Grabri
>
>
>
> _______________________________________________
> No banners. No pop-ups. No kidding.
> Make My Way  your home on the Web - http://www.myway.com

-----
William Kyngesburye <kyngchaos at kyngchaos.com>
http://www.kyngchaos.com/

"Oh, look, I seem to have fallen down a deep, dark hole.  Now what  
does that remind me of?  Ah, yes - life."

- Marvin