I have a MySQL database with many-to-many relationships. For example
I have a Location table with a one-to-many relate to a Topo_Loc table
which has a many-to-one relate to a Topo table. This is due a mine
location, periodically, being located on more than one 7.5 minute topo
quadrangle.
I'm using PHP to select the data like so...
$sql = "SELECT location.SITENAME, location.LAT, location.LNG,
location.LOCATIONID, topo.Map_Name, topo_loc.ID
FROM (topo_loc INNER JOIN location ON topo_loc.LOCATIONID =
location.LOCATIONID)
INNER JOIN topo ON topo_loc.TOPOID = topo.TOPOID
;";
I then convert the data to xml....
$result = mysql_query($sql);
if ($result != 0){
header("Content-type: text/xml");
echo '<wells>';
$num_results = mysql_num_rows($result);
for($i=0;$i<$num_results;$i++){
$row = mysql_fetch_array($result);
$name = $row['SITENAME'];
$lat = $row['LAT'];
$lng = $row['LNG'];
$topo = $row['Map_Name'];
$id = $row['LOCATIONID'];
echo'<pt name="'.$name.'" lat="'.$lat.'" lng="'.$lng.'"
topo="'.$topo.'" id="'.$id.'" />';
}
echo'</wells>';
Which creates....
<pt name="QUARRY" lat="36.259998" lng="-93.010002" topo="BERGMAN"
id="258" />
<pt name="TEST" lat="33.619999" lng="-92.059998" topo="WARREN NE"
id="259" />
<pt name="TEST" lat="33.619999" lng="-92.000000" topo="WARREN"
id="260" />
<pt name="TEST" lat="33.619999" lng="-92.000000" topo="WILMAR SOUTH"
id="260" />
<pt name="TEST" lat="33.619999" lng="-92.000000" topo="WARREN NE"
id="260" />
<pt name="TEST" lat="33.619999" lng="-92.000000" topo="WILMAR NORTH"
id="260" />
<pt name="TEST" lat="33.610001" lng="-92.050003" topo="WARREN"
id="261" />
<pt name="TEST" lat="33.560001" lng="-92.029999" topo="WARREN"
id="262" />
<pt name="TEST" lat="33.430000" lng="-92.000000" topo="SUMPTER"
id="263" />
<pt name="TEST" lat="33.430000" lng="-92.000000" topo="LONGVIEW"
id="263" />
<pt name="TEST" lat="33.430000" lng="-92.169998" topo="HERMITAGE"
id="264" />
<pt name="TEST" lat="33.340000" lng="-92.010002" topo="VICK"
id="265" />
In this example I get 4 results for id 260, because it's on 4
different quads. Is there a way to retrieve one record with all 4
topos inside the topo tag?
Thanks!
--
You received this message because you are subscribed to the Google Groups
"Google Maps API" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/google-maps-api?hl=en.
