Gossamer Forum: Products: Gossamer Links: Discussions: Google maps to display links data

Jun 15, 2007, 11:08 AM

colintho

User (238 posts)

Jun 15, 2007, 11:08 AM

Post #1 of 9

Shortcut

Google maps to display links data

Hi

I have used the Google maps service to integrate wth Glinks to display a map location for each link on the 'detailed' page.

I have followed one of the Google map tutorials: http://code.google.com/...5622&topic=11369

Producing a Google map which displays data from all the 'links' in the database (development site currently with only 43 lnks)

See this here: http://www.listmycaravan.co.uk/phpsqlajax_map2.htm

It would be a nice to have the Google map display 'links' data for all links from individual categories / pages or search results.

How can this be achieved?

Any help appreciated.

Thanks
Colin Thompson

Jun 16, 2007, 1:04 AM

Andy

Veteran / Moderator (18441 posts)

Jun 16, 2007, 1:04 AM

Post #2 of 9

Shortcut

Re: [colintho] Google maps to display links data In reply to

Hi,

Ok, so you have the "markers" table, as per http://code.google.com/...ic=11369#createtable ? I would imagine the main problem, being able to get the script to know which category -> which listing in that table. Personally, from what I have seen - it wouldn't be that easy :(

Cheers

Andy (mod)
andy@ultranerds.co.uk

Jun 16, 2007, 2:19 AM

colintho

User (238 posts)

Jun 16, 2007, 2:19 AM

Post #3 of 9

Shortcut

Re: [Andy] Google maps to display links data In reply to

Andy

I didn't use the 'creae table' script, I modified the php script to take the data directly from the glinks_Links table.

What about using the global for <%Category%> ?
Colin Thompson

Last edited by:

colintho: Jun 16, 2007, 2:38 AM

Jun 16, 2007, 2:59 AM

Andy

Veteran / Moderator (18441 posts)

Jun 16, 2007, 2:59 AM

Post #4 of 9

Shortcut

Re: [colintho] Google maps to display links data In reply to

Hi,

Ah ok, well - wouldn't be able to say any more until I could see the script you modified :P (I'm not psychic <G>).

Cheers

Andy (mod)
andy@ultranerds.co.uk

Last edited by:

Andy: Jun 16, 2007, 2:59 AM

Jun 16, 2007, 3:16 AM

colintho

User (238 posts)

Jun 16, 2007, 3:16 AM

Post #5 of 9

Shortcut

Re: [Andy] Google maps to display links data In reply to

Sorry - I guess should have posted this earlier?

The php script which grabs the glinks_Links data & outputs as xml file for the Google map code to grab data from & display Google map:

<?php
require("phpsqlajax_dbinfo.php");
function parseToXML($htmlStr)
{
$xmlStr=str_replace('<','<',$htmlStr);
$xmlStr=str_replace('>','>',$xmlStr);
$xmlStr=str_replace('"','"',$xmlStr);
$xmlStr=str_replace("'",''',$xmlStr);
$xmlStr=str_replace("&",'&',$xmlStr);
return $xmlStr;
}
// Opens a connection to a mySQL server
$connection=mysql_connect (localhost, $username, $password);
if (!$connection) {
die('Not connected : ' . mysql_error());
}
// Set the active mySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die ('Can\'t use db : ' . mysql_error());
}
// Select all the rows in the markers table
$query = "SELECT * FROM glinks_Links WHERE 1";
$result = mysql_query($query);
if (!$result) {
die('Invalid query: ' . mysql_error());
}
header("Content-type: text/xml");
// Start XML file, echo parent node
echo '<markers>';
// Iterate through the rows, printing XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
// ADD TO XML DOCUMENT NODE
echo '<marker ';
echo 'Title="' . parseToXML($row['Title']) . '" ';
echo 'Price="' . parseToXML($row['Price']) . '" ';
echo 'Year="' . parseToXML($row['Year']) . '" ';
echo 'Axles="' . parseToXML($row['Axles']) . '" ';
echo 'Berths="' . parseToXML($row['Berths']) . '" ';
echo 'Latitude="' . $row['Latitude'] . '" ';
echo 'Longitude="' . $row['Longitude'] . '" ';
echo '/>';
}
// End XML file
echo '</markers>';
?>

regards

Colin
Colin Thompson

Jun 18, 2007, 3:32 AM

Andy

Veteran / Moderator (18441 posts)

Jun 18, 2007, 3:32 AM

Post #6 of 9

Shortcut

Re: [colintho] Google maps to display links data In reply to

Hi,

Totally untested, but something like this should work: (I havn't written any PHP code in over 2 years, so appologies if its not fully working =))

Code:
<?php 

     require("phpsqlajax_dbinfo.php"); 

     global $CatID;

     function parseToXML($htmlStr) { 
          $xmlStr=str_replace('<','&lt;',$htmlStr); 
          $xmlStr=str_replace('>','&gt;',$xmlStr); 
          $xmlStr=str_replace('"','&quot;',$xmlStr); 
          $xmlStr=str_replace("'",'&apos;',$xmlStr); 
          $xmlStr=str_replace("&",'&amp;',$xmlStr); 
          return $xmlStr; 
     } 

    // Opens a connection to a mySQL server 
     $connection=mysql_connect (localhost, $username, $password); 
     if (!$connection) { 
          die('Not connected : ' . mysql_error()); 
     } 

    // Set the active mySQL database 
     $db_selected = mysql_select_db($database, $connection); 
     if (!$db_selected) { 
          die ('Can\'t use db : ' . mysql_error()); 
     } 

    // Select all the rows in the markers table 
     $query = "SELECT * FROM glinks_CatLinks,glinks_Links WHERE glinks_CatLinks.CategoryID = '$CatID' ";
     $result = mysql_query($query); 
     if (!$result) { 
        die('Invalid query: ' . mysql_error()); 
     } 

    header("Content-type: text/xml"); 

   // Start XML file, echo parent node 
    echo '<markers>'; 

   // Iterate through the rows, printing XML nodes for each 
     while ($row = @mysql_fetch_assoc($result)){ 
         // ADD TO XML DOCUMENT NODE 
          echo '<marker '; 
          echo 'Title="' . parseToXML($row['Title']) . '" '; 
          echo 'Price="' . parseToXML($row['Price']) . '" '; 
          echo 'Year="' . parseToXML($row['Year']) . '" '; 
          echo 'Axles="' . parseToXML($row['Axles']) . '" '; 
          echo 'Berths="' . parseToXML($row['Berths']) . '" '; 
          echo 'Latitude="' . $row['Latitude'] . '" '; 
          echo 'Longitude="' . $row['Longitude'] . '" '; 
          echo '/>'; 
     } 
// End XML file 
echo '</markers>'; 
?>

Also, be sure to edit your HTML on the bit that call's the PHP script - so it looks like:

Code:
GDownloadUrl("phpsqlajax_genxml2.php?CatID=<%category_id%>", function(data) {

I'm hoping <%category_id%> is the correct tag - afraid I don't have time to check it out fully, as it would require me setting up a test site, with all the longitude and latitude fields.

Hope that helps.

Cheers

Andy (mod)
andy@ultranerds.co.uk

Jun 18, 2007, 2:21 PM

colintho

User (238 posts)

Jun 18, 2007, 2:21 PM

Post #7 of 9

Shortcut

Re: [Andy] Google maps to display links data In reply to

Andy

thanks for that, I've added the revised code, it appears to display the same Google map + markers for each category.

I'll pm you with access if you want to take a look?

regards

Colin
Colin Thompson

Jun 19, 2007, 4:20 AM

Andy

Veteran / Moderator (18441 posts)

Jun 19, 2007, 4:20 AM

Post #8 of 9

Shortcut

Re: [colintho] Google maps to display links data In reply to

Hi,

For anyone interested (seeing as it took me so long to come up with this working code =)), here is the code you would need to put in the phpsqlajax_genxml.php script:

Code:
<?php 
require("phpsqlajax_dbinfo.php"); 

global $CatID; 

function parseToXML($htmlStr)  
{  
$xmlStr=str_replace('<','&lt;',$htmlStr);  
$xmlStr=str_replace('>','&gt;',$xmlStr);  
$xmlStr=str_replace('"','&quot;',$xmlStr);  
$xmlStr=str_replace("'",'&apos;',$xmlStr);  
$xmlStr=str_replace("&",'&amp;',$xmlStr);  
return $xmlStr;  
}  

// Opens a connection to a mySQL server 
$connection=mysql_connect (localhost, $username, $password); 
if (!$connection) { 
  die('Not connected : ' . mysql_error()); 
} 

// Set the active mySQL database 
$db_selected = mysql_select_db($database, $connection); 
if (!$db_selected) { 
  die ('Can\'t use db : ' . mysql_error()); 
} 

$query = "SELECT * FROM glinks_CatLinks WHERE CategoryID = '$CatID' "; 

$result = mysql_query($query); 
if (!$result) { 
  die('Invalid query: ' . mysql_error()); 
} 

while ($row = @mysql_fetch_assoc($result)){ 
   $ids[] = $row['LinkID']; 
} 

$comma_separated = join(" OR ID = ", $ids); 

$sql = "SELECT * FROM glinks_Links WHERE ID = $comma_separated"; 

header("Content-Type: text/xml"); 

$result2 = mysql_query($sql); 
if (!$result2) { 
  die('Invalid query: ' . mysql_error()); 
} 

// Start XML file, echo parent node 
echo '<markers>'; 

// Iterate through the rows, printing XML nodes for each 
while ($row = @mysql_fetch_assoc($result2)){ 
  echo '<marker '; 
  echo 'Title="' . parseToXML($row['Title']) . '" '; 
  echo 'Price="' . parseToXML($row['Price']) . '" '; 
  echo 'Year="' . parseToXML($row['Year']) . '" '; 
  echo 'Axles="' . parseToXML($row['Axles']) . '" '; 
  echo 'Berths="' . parseToXML($row['Berths']) . '" ';   
  echo 'Latitude="' . $row['Latitude'] . '" '; 
  echo 'Longitude="' . $row['Longitude'] . '" '; 
  echo '/>'; 
} 

// End XML file 
echo '</markers>'; 

?>

Enjoy =)

Cheers

Andy (mod)
andy@ultranerds.co.uk

Jun 20, 2007, 1:52 AM

colintho

User (238 posts)

Jun 20, 2007, 1:52 AM

Post #9 of 9

Shortcut

Re: [Andy] Google maps to display links data In reply to

Many thanks.
Colin Thompson