Using a bunch of PHP and MySQL with Google Maps
Gag
And here is the translation of the third lesson on the Google Maps API. This lesson - as the name implies - is devoted to creating maps using data stored in the database. Like the previous - cross post from my blog .
This lesson is intended for people who are familiar with a bunch of PHP / MySQL and who want to learn how to use the Google Maps API with MySQL databases. After completing this lesson, you will have a database based map. The map will be located markers of different colors. Some of them will point to restaurants, others - to bars. When clicking on a marker, an info-window will appear showing the name of the place and its address.
The lesson is divided into the following paragraphs:
- Creating a database table;
- Filling the table;
- Displaying information in XML using PHP;
- Create a map.
CREATING A TABLE
When creating a table, first of all, you should pay attention to the
lat (latitude) and lng (longitude) card parameters. With the current resolution of Google Maps, we only need 6 digits after the decimal point. To reduce the disk space occupied by the database to a minimum, for lat (latitude) and lng (longitude) values, it is recommended to select the FLOAT data type with parameter 10.6. This type allows you to store up to 6 decimal places and up to 4 before the comma, for example -123.456789 degrees. Your table should have an id field, which will be the primary key for access to the records, as well as a type field in which the institution type will be recorded (restaurant or bar).Note: in this lesson, places are used whose latitude and longitude values ​​are already known and are used to draw markers on the map. If you try to use your information without having data on the coordinates of a place, then use the geocoding service to convert the address of the place to its coordinates. Here is a list of some geocoding services: http://groups.google.com/group/Google-Maps-API/web/resources-non-google-geocoders .
If you prefer to create tables in the database using phpMyAdmin, then below is a screenshot of the table creation:
If you do not have access to phpMyAdmin or give preference to pure SQL, the SQL query code is shown below:
CREATE TABLE `markers` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR (60) NOT NULL ,
`address` VARCHAR (80) NOT NULL ,
`lat` FLOAT (10, 6) NOT NULL ,
`lng` FLOAT (10, 6) NOT NULL ,
`type` VARCHAR (30) NOT NULL
) ENGINE = MYISAM;
FILLING TABLE
After creating the table, it's time to fill it with information. Data on 10 places in Seattle are shown below. In phpMyAdmin, you can use the IMPORT option to insert data stored in various formats into the table, including CSV. MS Excel and Google Spreadsheets applications support exporting data to CSV format. So you can easily transfer data from these applications to the MySQL database using the export / import options.
Here is the information provided in CSV format:
Pan Africa Market, "1521 1st Ave, Seattle, WA", 47.608941, -122.340145, restaurant
Buddha Thai & Bar, "2222 2nd Ave, Seattle, WA", 47.613591, -122.344394, bar
The Melting Pot, "14 Mercer St, Seattle, WA", 47.624562, -122.356442, restaurant
Ipanema Grill, "1225 1st Ave, Seattle, WA", 47.606366, -122.337656, restaurant
Sake House, "2230 1st Ave, Seattle, WA", 47.612825, -122.34567, bar
Crab Pot, "1301 Alaskan Way, Seattle, WA", 47.605961, -122.34036, restaurant
Mama's Mexican Kitchen, "2234 2nd Ave, Seattle, WA", 47.613975, -122.345467, bar
Wingdome, "1416 E Olive Way, Seattle, WA", 47.617215, -122.326584, bar
Piroshky Piroshky, "1908 Pike pl, Seattle, WA", 47.610127, -122.342838, restaurant
And here is a screenshot showing the import of data from CSV to the database table:
If you do not use phpMyAdmin, then below is the query code to the database in SQL language:
INSERT INTO `markers` (` name`, `address`,` lat`, `lng`,` type`) VALUES ( 'Pan Africa Market' , '1521 1st Ave, Seattle, WA' , '47 .608941 ' , ' - 122.340145 ' , ' restaurant ' );
INSERT INTO `markers` (` name`, `address`,` lat`, `lng`,` type`) VALUES ( 'Buddha Thai & Bar' , '2222 2nd Ave, Seattle, WA' , '47 .613591 ' , ' -122.344394 ' , ' bar ' );
INSERT INTO `markers` (` name`, `address`,` lat`, `lng`,` type`) VALUES ( 'The Melting Pot' , '14 Mercer St, Seattle, WA ' , '47 .624562' , '- 122.356442 ' , ' restaurant ' );
INSERT INTO `markers` (` name`, `address`,` lat`, `lng`,` type`) VALUES ( 'Ipanema Grill' , '1225 1st Ave, Seattle, WA' , '47 .606366 ' , ' -122.337656 ' , ' restaurant ' );
INSERT INTO `markers` (` name`, `address`,` lat`, `lng`,` type`) VALUES ( 'Sake House' , '2230 1st Ave, Seattle, WA' , '47 .612825 ' , ' -122.34567 ' , ' bar ' );
INSERT INTO `markers` (` name`, `address`,` lat`, `lng`,` type`) VALUES ( 'Crab Pot' , '1301 Alaskan Way, Seattle, WA' , '47 .605961 ' , ' -122.34036 ' , ' restaurant ' );
INSERT INTO `markers` (` name`, `address`,` lat`, `lng`,` type`) VALUES ( 'Mama Mexican Kitchen' , '2234 2nd Ave, Seattle, WA' , '47. 613975 ' , ' - 122.345467 ' , ' bar ' );
INSERT INTO `markers` (` name`, `address`,` lat`, `lng`,` type`) VALUES ( 'Wingdome' , '1416 E Olive Way, Seattle, WA' , '47 .617215 ' , ' -122.326584 ' , ' bar ' );
INSERT INTO `markers` (` name`, `address`,` lat`, `lng`,` type`) VALUES ( 'Piroshky Piroshky' , '1908 Pike pl, Seattle, WA' , '47 .610127 ' , ' -122.342838 ' , ' restaurant ' );
CONCLUSION OF INFORMATION IN XML C WITH THE HELP OF PHP
At this point, you should have a database table (called markers) filled with data. Now you need to write some PHP code that allows you to connect to the database, take the necessary information from it and export it to an XML file with which the map will interact using asynchronous JavaScript requests. If you have never written to PHP code that allows you to connect to the database, then you need to visit the site php.net and read about the functions
mysql_connect() , mysql_select_db() , my_sql_query() , and mysql_error() .Note: Some lessons suggest that you create a page with a map as a PHP file and use JavaScript to output each marker individually. Such an approach can be problematic. Using the XML file as an intermediary between the database and the map, you speed up the initial loading of the page and make it easier for you to debug the application. You can check the data recorded from the database to an XML file and the result of the analysis of this file in JavaScript. And at any stage you can do without the database, managing the map only using a static XML file.
First of all, you should put the information about the connection to the database in a separate file. Below is the PHP code in which you must enter your own username, password and database name:
<?
$username="username";
$password="password";
$database="username-databaseName";
?>Using PHP's XML functions to create an XML file
Check the PHP configuration or try using the
domxml_new_doc() function to test the dom_xml functionality. If you have access to dom_xml functions, you can use them to create XML nodes and display the XML document on the screen. Using dom_xml functions dom_xml you can create XML documents of almost any complexity.In PHP, for starters, you need to initialize a new XML document and create a parent node “marlers”. Then you need to connect to the database and select the necessary data using the query of the type
SELECT * (select all) to the table named “markers” and go through all the results of this sample. For each record in the table (each establishment), a node will be created in the XML document, the attributes of which will be fields from the corresponding table entry - this node will be attached to the parent node. After that you will receive a ready-made XML document.Note: if you use international characters in the database, then you need to apply UTF-8 encoding to the output data. Here is the transcoding function in UTF-8 .
Your PHP code should be similar to the one below:
<?php
require("phpsqlajax_dbinfo.php");
// XML-
$doc = domxml_new_doc(" 1.0 ");
$node = $doc->create_element("markers");
$parnode = $doc->append_child($node);
// MySQL-
$connection=mysql_connect (localhost, $username, $password);
if (!$connection) {
die(' : ' . mysql_error());
}
//
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die (' : ' . mysql_error());
}
// markers
$query = "SELECT * FROM markers WHERE 1"
$result = mysql_query($query);
if (!$result) {
die(' : ' . mysql_error());
}
header("Content-type: text/xml");
// ;
while ($row = @mysql_fetch_assoc($result)){
// XML
$node = $doc->create_element("marker");
$newnode = $parnode->append_child($node);
$newnode->set_attribute("name", $row['name']);
$newnode->set_attribute("address", $row['address']);
$newnode->set_attribute("lat", $row['lat']);
$newnode->set_attribute("lng", $row['lng']);
$newnode->set_attribute("type", $row['type']);
}
$xmlfile = $doc->dump_mem();
echo $xmlfile;
?>Using the echo php function to create an xml file
If you do not have access to the
dom_xml functions, you can create the XML file using the echo function. When using the echo function, you will need to write an assistant function (for example, parseToXML ) that will decode some special characters (>, <, ', ”) so that no XML errors occur.In PHP, first of all, you need to connect to the database and select the necessary data using a
SELECT * query (select all) to a table named “markers” and go through all the results of this sample. Then you need to display the parent node using the echo function and walk through the results of the sample. In this case, you must use the parseToXML function parseToXML we parseToXML . The execution of this script should end with the closing markers tag.Note: if you use international characters in the database, then you need to apply UTF-8 encoding to the output data. Here is the transcoding function in UTF-8 .
Your PHP code should be similar to the one below:
<?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;
}
// MySQL-
$connection=mysql_connect (localhost, $username, $password);
if (!$connection) {
die(' : ' . mysql_error());
}
//
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die (' : ' . mysql_error());
}
// markers
$query = "SELECT * FROM markers WHERE 1"
$result = mysql_query($query);
if (!$result) {
die(' : ' . mysql_error());
}
header("Content-type: text/xml");
// XML-,
echo '<markers>';
// ;
while ($row = @mysql_fetch_assoc($result)){
// XML
echo '<marker ';
echo 'name="' . parseToXML($row['name']) . '" ';
echo 'address="' . parseToXML($row['address']) . '" ';
echo 'lat="' . $row['lat'] . '" ';
echo 'lng="' . $row['lng'] . '" ';
echo 'type="' . $row['type'] . '" ';
echo '/>';
}
// XML-
echo '</markers>';
?>Using PHP DOM Functions to Create an XML File
First, you need to make sure that you are using PHP version 5. If your version of PHP is lower, use any of the previous methods.
In PHP, for starters, you need to initialize a new XML document and create a parent node “markers”. Then you need to connect to the database and select the necessary data using the query of the type
SELECT * (select all) to the table named “markers” and go through all the results of this sample. For each record in the table (each establishment), a node will be created in the XML document, the attributes of which will be fields from the corresponding table entry - this node will be attached to the parent node. After that you will receive a ready-made XML document.Note: if you use international characters in the database, then you need to apply UTF-8 encoding to the output data. Here is the transcoding function in UTF-8 .
<?php
require("phpsqlajax_dbinfo.php");
// XML-
$dom = new DOMDocument(" 1.0 ");
$node = $dom->createElement("markers");
$parnode = $dom->appendChild($node);
// MySQL-
$connection=mysql_connect (localhost, $username, $password);
if (!$connection) { die(' : ' . mysql_error());}
//
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die (' : ' . mysql_error());
}
// markers
$query = "SELECT * FROM markers WHERE 1"
$result = mysql_query($query);
if (!$result) {
die(' : ' . mysql_error());
}
header("Content-type: text/xml");
// ;
while ($row = @mysql_fetch_assoc($result)){
// XML
$node = $dom->createElement("marker");
$newnode = $parnode->appendChild($node);
$newnode->setAttribute("name",$row['name']);
$newnode->setAttribute("address", $row['address']);
$newnode->setAttribute("lat", $row['lat']);
$newnode->setAttribute("lng", $row['lng']);
$newnode->setAttribute("type", $row['type']);
}
echo $dom->saveXML();
?>XML Output Verification
Run this script to make sure the output is correct. If there are any problems with connecting to the database, you can try to delete the line with the content header (text / xml), since It may prevent your browser from displaying error messages.
If the script did its job correctly, then you should see the following:
< markers >
< marker name = "Pan Africa Market" address = "1521 1st Ave, Seattle, WA" lat = "47.608940" lng = "-122.340141" type = "restaurant" />
< marker name = "Buddha Thai & Bar" address = "2222 2nd Ave, Seattle, WA" lat = "47.613590" lng = "-122.344391" type = "bar" />
< marker name = "The Melting Pot" address = "14 Mercer St, Seattle, WA" lat = "47.624561" lng = "-122.356445" type = "restaurant" />
< marker name = "Ipanema Grill" address = "1225 1st Ave, Seattle, WA" lat = "47.606365" lng = "-122.337654" type = "restaurant" />
< marker name = "Sake House" address = "2230 1st Ave, Seattle, WA" lat = "47.612823" lng = "-122.345673" type = "bar" />
< marker name = "Crab Pot" address = "1301 Alaskan Way, Seattle, WA" lat = "47.605961" lng = "-122.340363" type = "restaurant" />
< marker name = "Mama's Mexican Kitchen" address = "2234 2nd Ave, Seattle, WA" lat = "47.613976" lng = "-122.345467" type = "bar" />
< marker name = "Wingdome" address = "1416 E Olive Way, Seattle, WA" lat = "47.617214" lng = "-122.326584" type = "bar" />
< marker name = "Piroshky Piroshky" address = "1908 Pike pl, Seattle, WA" lat = "47.610126" lng = "-122.342834" type = "restaurant" />
</ markers >
CREATION OF THE CARD
Once the XML file is correctly displayed in the browser, then it is time to create a map using JavaScript. If you have never dealt with the Google Maps API, then please learn the basics with the help of official documentation.
XML file upload
In order to upload an XML file to a page, you must use the API
GDownloadURL . GDownloadURL is like a wrapper for the XMLHttpRequest method, which is used to send requests in an XML form. The first parameter of the GDownloadURL function is the path to the XML file. The second parameter is a function that will be executed when receiving a response from XML.Note: it is very important to understand that
GDownloadURL works asynchronously - the function, which is the second parameter of GDownloadURL , will not work once you have applied this method. The larger your XML file, the longer it will take to call this parameter function. You should not put any code responsible for placing markers on the map, after the method GDownloadURL - it must be placed in the function-parameter.The function parameter is needed in order to find all the “markers” elements in XML. For each marker, we restore the values ​​of the name, address, type and coordinates, and then we pass these values ​​to the
creatMarker function, which places the markers on the map.GDownloadUrl ( "phpsqlajax_genxml.php" , function (data) {
var xml = GXml.parse (data);
var markers = xml.documentElement.getElementsByTagName ( "marker" );
for ( var i = 0; i <markers.length; i ++) {
var name = markers [i] .getAttribute ( "name" );
var address = markers [i] .getAttribute ( "address" );
var type = markers [i] .getAttribute ( "type" );
var point = new GLatLng (parseFloat (markers [i] .getAttribute ( "lat" )),
parseFloat (markers [i] .getAttribute ( "lng" )));
var marker = createMarker (point, name, address, type);
map.addOverlay (marker);
}
});
Create icons for markers
You can use the
GIcon class to define arbitrary images that will be displayed as a marker. First we define two objects: iconBlue and iconRed.Warning: You may not define as many properties as shown in the example, but by doing so, you run the risk of encountering various errors later.
You can also create an associative array that will associate each
GIcon object with a specific type of institution: a restaurant or a bar. This approach will help you in the future to easily understand the markers created using data from an external XML file.var iconBlue = new GIcon ();
iconBlue.image = 'http://labs.google.com/ridefinder/images/mm_20_blue.png' ;
iconBlue.shadow = 'http://labs.google.com/ridefinder/images/mm_20_shadow.png' ;
iconBlue.iconSize = new GSize (12, 20);
iconBlue.shadowSize = new GSize (22, 20);
iconBlue.iconAnchor = new GPoint (6, 20);
iconBlue.infoWindowAnchor = new GPoint (5, 1);
var iconRed = new GIcon ();
iconRed.image = 'http://labs.google.com/ridefinder/images/mm_20_red.png' ;
iconRed.shadow = 'http://labs.google.com/ridefinder/images/mm_20_shadow.png' ;
iconRed.iconSize = new GSize (12, 20);
iconRed.shadowSize = new GSize (22, 20);
iconRed.iconAnchor = new GPoint (6, 20);
iconRed.infoWindowAnchor = new GPoint (5, 1);
')
var customIcons = [];
customIcons [ "restaurant" ] = iconBlue;
customIcons [ "bar" ] = iconRed;
Creating bullets and info windows
You must put all the code you need to create markers in the
createMarker function. Since the customIcons array was defined globally, we can transfer its elements to the constructor of the GMarker class without interference. Then you need to write the HTML code that will be displayed in the info window.Hint: Some lessons advise you to store information formatted as HTML. But this is not good, since you cannot freely manipulate HTML formatting.
After writing the displayed HTML code, you need to add events for the markers so that when you click on the marker, the info window appears.
function createMarker (point, name, address, type) {
var marker = new GMarker (point, customIcons [type]);
var html = "<b>" + name + "</ b> <br/>" + address;
GEvent.addListener (marker, 'click' , function () {
marker.openInfoWindowHtml (html);
});
return marker;
}
Putting it all together
Below is the code that links everything that we wrote above into a coherent whole. As soon as the page loads, the load map
load function is activated. This function displays a map, and then calls the GDownloadUrl method. Make sure that the GDownloadUrl function performs its work correctly and the XML file is displayed in the browser correctly.And here is the link to the finished example.
Happy Codding to you!
Source: https://habr.com/ru/post/38290/
All Articles