Top Left Text cha

Web & App Development

This is useful if you need to copy all the columns of a table into your IDE, for creating a form or query, for example.  It does not include any other columns like the 'SHOW COLUMNS' statement.

SELECT column_name FROM information_schema.columns WHERE table_schema = 'DATABASE-NAME' AND table_name = 'TABLE-NAME';
Comment (0) Hits: 1425
<?php
// Connect to the mysql server
$mysqli = new mysqli('localhost', $env['username'], $env['password'], $env['database']);
if ($mysqli->connect_errno) {
    echo 'Failed to connect';
}

echo $mysqli->host_info . "\n";

// SQL query to join 3 tables based on item ID.
// This will return one row for each permutation of data.
// Note that the column 'desc' in the OPs question has been replaced with 'description'
// to avoid a naming conflict with a MYSQL keyword.
$res = $mysqli->query("select distinct a.id, a.item, a.description, b.metakey, b.metavalue, c.name from a join b on a.id = b.item_id join c on a.id = c.item_id order by a.item"); 
print_r($res);

// Transform the mysql output which contains duplicate information
// into the desired data structure as specified in the OPs question.
$output = [];
while($row = $res->fetch_assoc()) {
        // We need to use the row ID (Item ID) to process the mysql rows.

       // Only add the full row if the Item ID has not previously been added.                                                   
        if (!isset($output[$row['id']])) {
            // Form the desired data structure
            $output[$row['id']] = [
                "DATA" => [
                    // The data array is an indexed array.
                    $row['item'],
                    $row['description'],
                ],
                "META" => [
                    // The meta array is an associative array and uses key value pairs.
                    $row['metakey'] => $row['metavalue'],
                ],
                // The extra array is an indexed array.
                "EXTRA" => [  
                    $row['name'],
                ],
            ]; 
        } 
        // Here we fill in the missing data from the partially duplicated mysql rows.
        // We drill down into the output array to check which keys have been added already,
        // and if it hasn't been added we add it.
        if (!isset($output[$row['id']]['META'][$row['metakey']])){
            $output[$row['id']]['META'][$row['metakey']] = $row['metavalue'];
        }
        // Same again, but a slightly different check. This time we are dealing with
        // an indexed array so we need to see if the value has been added.
        if (!in_array($row['name'], $output[$row['id']]['EXTRA'])) {
            $output[$row['id']]['EXTRA'][] = $row['name'];
        }
}

print_r($output);
Comment (0) Hits: 1413

Using MySQL and PHP with Google Maps

Overview

This tutorial shows you how to display information from a MySQL database on a Google Map using the Google Maps JavaScript API. It suits people with intermediate knowledge of MySQL, PHP and XML.

The map in this tutorial displays two kinds of markers to differentiate between the location of restaurants and bars respectively. A database in MySQL stores information about the individual marker locations like the type of place (whether it's a restaurant or a bar), its name, address and geographic coordinates. The map retrieves this information from the database, through an XML file that acts as an intermediary between the database and the map. You can use PHP statements to export marker information from the database to an XML file.

Click a marker on the map below to display an info window with the location name and address. The markers have the labels 'R' or 'B', and they show you the locations of restaurants and bars respectively.

The sample below shows the entire code you need to create this map.

 
      var customLabel = {
        restaurant: {
          label: 'R'
        },
        bar: {
          label: 'B'
        }
      };

        function initMap() {
        var map = new google.maps.Map(document.getElementById('map'), {
          center: new google.maps.LatLng(-33.863276, 151.207977),
          zoom: 12
        });
        var infoWindow = new google.maps.InfoWindow;

          // Change this depending on the name of your PHP or XML file
          downloadUrl('https://storage.googleapis.com/mapsdevsite/json/mapmarkers2.xml', function(data) {
            var xml = data.responseXML;
            var markers = xml.documentElement.getElementsByTagName('marker');
            Array.prototype.forEach.call(markers, function(markerElem) {
              var id = markerElem.getAttribute('id');
              var name = markerElem.getAttribute('name');
              var address = markerElem.getAttribute('address');
              var type = markerElem.getAttribute('type');
              var point = new google.maps.LatLng(
                  parseFloat(markerElem.getAttribute('lat')),
                  parseFloat(markerElem.getAttribute('lng')));

              var infowincontent = document.createElement('div');
              var strong = document.createElement('strong');
              strong.textContent = name
              infowincontent.appendChild(strong);
              infowincontent.appendChild(document.createElement('br'));

              var text = document.createElement('text');
              text.textContent = address
              infowincontent.appendChild(text);
              var icon = customLabel[type] || {};
              var marker = new google.maps.Marker({
                map: map,
                position: point,
                label: icon.label
              });
              marker.addListener('click', function() {
                infoWindow.setContent(infowincontent);
                infoWindow.open(map, marker);
              });
            });
          });
        }



      function downloadUrl(url, callback) {
        var request = window.ActiveXObject ?
            new ActiveXObject('Microsoft.XMLHTTP') :
            new XMLHttpRequest;

        request.onreadystatechange = function() {
          if (request.readyState == 4) {
            request.onreadystatechange = doNothing;
            callback(request, request.status);
          }
        };

        request.open('GET', url, true);
        request.send(null);
      }

      function doNothing() {}
 
<div id="map"></div>
 
/* Always set the map height explicitly to define the size of the div
 * element that contains the map. */
#map {
  height: 100%;
}
/* Optional: Makes the sample page fill the window. */
html, body {
  height: 100%;
  margin: 0;
  padding: 0;
}
 
<!-- Replace the value of the key parameter with your own API key. -->
<script async defer
src="https://maps.googleapis.com/maps/api/js?key=AIzaSyCkUOdZ5y7hMm0yrcCQoCvLwzdM6M8s5qk&callback=initMap">
</script>

Try it yourself

Hover at top right of the code block to copy the code or open it in JSFiddle.

 
<!DOCTYPE html >
 
<head>
   
<meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
   
<meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
   
<title>Using MySQL and PHP with Google Maps</title>
   
<style>
     
/* Always set the map height explicitly to define the size of the div
       * element that contains the map. */

     
#map {
       
height: 100%;
     
}
     
/* Optional: Makes the sample page fill the window. */
      html
, body {
       
height: 100%;
       
margin: 0;
       
padding: 0;
     
}
   
</style>
 
</head>

 
<body>
   
<div id="map"></div>

   
<script>
     
var customLabel = {
        restaurant
: {
          label
: 'R'
       
},
        bar
: {
          label
: 'B'
       
}
     
};

       
function initMap() {
       
var map = new google.maps.Map(document.getElementById('map'), {
          center
: new google.maps.LatLng(-33.863276, 151.207977),
          zoom
: 12
       
});
       
var infoWindow = new google.maps.InfoWindow;

         
// Change this depending on the name of your PHP or XML file
          downloadUrl
('https://storage.googleapis.com/mapsdevsite/json/mapmarkers2.xml', function(data) {
           
var xml = data.responseXML;
           
var markers = xml.documentElement.getElementsByTagName('marker');
           
Array.prototype.forEach.call(markers, function(markerElem) {
             
var id = markerElem.getAttribute('id');
             
var name = markerElem.getAttribute('name');
             
var address = markerElem.getAttribute('address');
             
var type = markerElem.getAttribute('type');
             
var point = new google.maps.LatLng(
                  parseFloat
(markerElem.getAttribute('lat')),
                  parseFloat
(markerElem.getAttribute('lng')));

             
var infowincontent = document.createElement('div');
             
var strong = document.createElement('strong');
              strong
.textContent = name
              infowincontent
.appendChild(strong);
              infowincontent
.appendChild(document.createElement('br'));

             
var text = document.createElement('text');
              text
.textContent = address
              infowincontent
.appendChild(text);
             
var icon = customLabel[type] || {};
             
var marker = new google.maps.Marker({
                map
: map,
                position
: point,
                label
: icon.label
             
});
              marker
.addListener('click', function() {
                infoWindow
.setContent(infowincontent);
                infoWindow
.open(map, marker);
             
});
           
});
         
});
       
}



     
function downloadUrl(url, callback) {
       
var request = window.ActiveXObject ?
           
new ActiveXObject('Microsoft.XMLHTTP') :
           
new XMLHttpRequest;

        request
.onreadystatechange = function() {
         
if (request.readyState == 4) {
            request
.onreadystatechange = doNothing;
            callback
(request, request.status);
         
}
       
};

        request
.open('GET', url, true);
        request
.send(null);
     
}

     
function doNothing() {}
   
</script>
   
<script async defer
   
src="https://maps.googleapis.com/maps/api/js?key=YOUR_API_KEY&callback=initMap">
   
</script>
 
</body>
</html>

Getting started

Install, set up and configure a MySQL server with PHP on your machine.

Creating a table in MySQL

Create a table in MySQL containing attributes of the markers on the map, like the marker id, name, address, lat, lng, and type.

The id attribute serves as the primary key, and the type attribute distinguishes between restaurants and bars.

To keep the storage space for your table at a minimum, you can specify the lat and lng attributes to be floats of size (10,6). This allows the fields to store 6 digits after the decimal, plus up to 4 digits before the decimal.

You can interact with the MySQL datbase through the phpMyAmin interface. The screenshot below displays the table setup in phpMyAdmin.

You can also use SQL commands to create the table, as in the SQL statement 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 ;

Populating the table

You can import the marker data into the SQL database using the 'Import' functionality of the phpMyAdmin interface which allows you to import data in various formats.

Below is the marker data for the map in this tutorial, in the .csv format.

 
1,Love.Fish,"580 Darling Street, Rozelle, NSW",-33.861034,151.171936,restaurant
2,Young Henrys,"76 Wilford Street, Newtown, NSW",-33.898113,151.174469,bar
3,Hunter Gatherer,"Greenwood Plaza, 36 Blue St, North Sydney NSW ",-33.840282,151.207474,bar
4,The Potting Shed,"7A, 2 Huntley Street, Alexandria, NSW",-33.910751,151.194168,bar
5,Nomad,"16 Foster Street, Surry Hills, NSW",-33.879917,151.210449,bar
6,Three Blue Ducks,"43 Macpherson Street, Bronte, NSW",-33.906357,151.263763,restaurant
7,Single Origin Roasters,"60-64 Reservoir Street, Surry Hills, NSW",-33.881123,151.209656,restaurant
8,Red Lantern,"60 Riley Street, Darlinghurst, NSW",-33.874737,151.215530,restaurant

You can also use the SQL commands below to import the marker data into the SQL table.

 
INSERT INTO `markers` (`id`, `name`, `address`, `lat`, `lng`, `type`) VALUES ('1', 'Love.Fish', '580 Darling Street, Rozelle, NSW', '-33.861034', '151.171936', 'restaurant');
INSERT INTO
`markers` (`id`, `name`, `address`, `lat`, `lng`, `type`) VALUES ('2', 'Young Henrys', '76 Wilford Street, Newtown, NSW', '-33.898113', '151.174469', 'bar');
INSERT INTO
`markers` (`id`, `name`, `address`, `lat`, `lng`, `type`) VALUES ('3', 'Hunter Gatherer', 'Greenwood Plaza, 36 Blue St, North Sydney NSW', '-33.840282', '151.207474', 'bar');
INSERT INTO
`markers` (`id`, `name`, `address`, `lat`, `lng`, `type`) VALUES ('4', 'The Potting Shed', '7A, 2 Huntley Street, Alexandria, NSW', '-33.910751', '151.194168', 'bar');
INSERT INTO
`markers` (`id`, `name`, `address`, `lat`, `lng`, `type`) VALUES ('5', 'Nomad', '16 Foster Street, Surry Hills, NSW', '-33.879917', '151.210449', 'bar');
INSERT INTO
`markers` (`id`, `name`, `address`, `lat`, `lng`, `type`) VALUES ('6', 'Three Blue Ducks', '43 Macpherson Street, Bronte, NSW', '-33.906357', '151.263763', 'restaurant');
INSERT INTO
`markers` (`id`, `name`, `address`, `lat`, `lng`, `type`) VALUES ('7', 'Single Origin Roasters', '60-64 Reservoir Street, Surry Hills, NSW', '-33.881123', '151.209656', 'restaurant');
INSERT INTO
`markers` (`id`, `name`, `address`, `lat`, `lng`, `type`) VALUES ('8', 'Red Lantern', '60 Riley Street, Darlinghurst, NSW', '-33.874737', '151.215530', 'restaurant');

Outputting data as XML using PHP

At this point, you should have a table named markers containing the map marker data. This section shows you how to export the table data from the SQL database in an XML format, using PHP statements. The map can use the XML file to retrieve the marker data through asynchronous JavaScript calls.

Using an XML file as an intermediary between your database and your Google map allows for faster initial page load, and a more flexible map application. It makes debugging easier as you can independently verify the XML output from the database, and the JavaScript parsing of the XML. You can also run the map entirely based on static XML files only, and not use the MySQL database.

If you have never used PHP to connect to a MySQL database, visit php.net and read up on mysql_connect, mysql_select_db, my_sql_query, and mysql_error.

You can use any of the three different techniques below to output the SQL table data as XML with PHP.

When using a public browser to access a database using PHP files, it's important to ensure that your database credentials are secure. You can do this by putting your database connection information in a separate PHP file to that of the main PHP code. The file with your credentials should look like the one below, but containing your own database information.

 
<?php
$username
="username";
$password
="password";
$database
="username-databaseName";
?>

Using PHP's DOM XML functions to output XML

The DOM XML functions of PHP take care of subtleties such as escaping special entities in the XML, and make it easy to create XML with more complex structures. You can use DOM XML functions to create XML nodes, append child nodes, and output an XML document to the screen. To determine if your server's PHP has the DOM XML functionality enabled, check your configuration or try initializing a domxml_new_doc().

Below is the PHP file that connects to the MySQL database, and dumps the XML to the browser.

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

// Start XML file, create parent node
$doc
= domxml_new_doc("1.0");
$node
= $doc->create_element("markers");
$parnode
= $doc->append_child($node);

// 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 markers WHERE 1";
$result
= mysql_query($query);
if (!$result) {
 
die('Invalid query: ' . mysql_error());
}

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

// Iterate through the rows, adding XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
 
// Add to XML document node
  $node
= $doc->create_element("marker");
  $newnode
= $parnode->append_child($node);

  $newnode
->set_attribute("id", $row['id']);
  $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
;

?>

In the file above, the PHP code first initializes a new XML document and creates the "markers" parent node. It then connects to the database, executes a SELECT * (select all) query on the markers table, and iterates through the results. For each row in the table (each location), the code creates a new XML node with the row attributes as XML attributes, and appends it to the parent node. The last part of code then dumps the XML to the browser screen.

Note: If your database contains international characters or you otherwise need to force a UTF-8 output, you can use utf8_encode on the data output.

Using PHP's echo to output XML

If you don't have access to PHP's dom_xml functions, then you can output the XML with the echo function. Use a helper function (e.g. parseToXML) when using just the echo function to correctly encode a few special entities (<,>,",') to be XML friendly.

Below is the PHP file that connects to the MySQL database, and dumps the XML to the browser.

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

function parseToXML($htmlStr)
{
$xmlStr
=str_replace('<','&lt;',$htmlStr);
$xmlStr
=str_replace('>','&gt;',$xmlStr);
$xmlStr
=str_replace('"','&quot;',$xmlStr);
$xmlStr
=str_replace("'",'&#39;',$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 markers 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
'id="' . $ind . '" ';
  echo
'name="' . parseToXML($row['name']) . '" ';
  echo
'address="' . parseToXML($row['address']) . '" ';
  echo
'lat="' . $row['lat'] . '" ';
  echo
'lng="' . $row['lng'] . '" ';
  echo
'type="' . $row['type'] . '" ';
  echo
'/>';
}

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

?>

The code above connects to the database and executes the SELECT * (select all) query on the markers table. It then echoes the parent markers node, and iterates through the query results. The code then echoes the XML node for the marker in each row of the table (for each location). This sends the name and address fields through the parseToXML function first, in case there are any special entities in them. The script ends by echoing out the closing markers tag.

Note: You can use utf8_encode on the XML data output if your database contains international characters, or if you need to force an UTF-8 output.

Using PHP's DOM functions to output XML

Below is the PHP file that connects to the MySQL database, and dumps the XML to the browser.

 
<?php

require("phpsqlajax_dbinfo.php");

// Start XML file, create parent node

$dom
= new DOMDocument("1.0");
$node
= $dom->createElement("markers");
$parnode
= $dom->appendChild($node);

// 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 markers WHERE 1";
$result
= mysql_query($query);
if (!$result) {
 
die('Invalid query: ' . mysql_error());
}

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

// Iterate through the rows, adding XML nodes for each

while ($row = @mysql_fetch_assoc($result)){
 
// Add to XML document node
  $node
= $dom->createElement("marker");
  $newnode
= $parnode->appendChild($node);
  $newnode
->setAttribute("id",$row['id']);
  $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();

?>

The code above initializes a new XML document and creates the "markers" parent node. It then connects to the database, executes a SELECT * (select all) query on the markers table, and iterates through the results. The code then creates a XML node for each row in the table (for each location), with the row attributes as XML attributes, and appends it to the parent node. It then dumps the output XML to the browser screen.

Note: If your database contains international characters, or you need to force a UTF-8 output, you can useutf8_encode on the data output.

Checking that XML output works

To confirm that the PHP script is producing valid XML, call the php script file you created from your browser to see the XML output as below.

 
<markers>
<marker id="1" name="Love.Fish" address="580 Darling Street, Rozelle, NSW" lat="-33.861034" lng="151.171936" type="restaurant"/>
<marker id="2" name="Young Henrys" address="76 Wilford Street, Newtown, NSW" lat="-33.898113" lng="151.174469" type="bar"/>
<marker id="3" name="Hunter Gatherer" address="Greenwood Plaza, 36 Blue St, North Sydney NSW" lat="-33.840282" lng="151.207474" type="bar"/>
<marker id="4" name="The Potting Shed" address="7A, 2 Huntley Street, Alexandria, NSW" lat="-33.910751" lng="151.194168" type="bar"/>
<marker id="5" name="Nomad" address="16 Foster Street, Surry Hills, NSW" lat="-33.879917" lng="151.210449" type="bar"/>
<marker id="6" name="Three Blue Ducks" address="43 Macpherson Street, Bronte, NSW" lat="-33.906357" lng="151.263763" type="restaurant"/>
<marker id="7" name="Single Origin Roasters" address="60-64 Reservoir Street, Surry Hills, NSW" lat="-33.881123" lng="151.209656" type="restaurant"/>
<marker id="8" name="Red Lantern" address="60 Riley Street, Darlinghurst, NSW" lat="-33.874737" lng="151.215530" type="restaurant"/>
</markers>

If your browser isn't displaying the marker data from your database as an XML output, try debugging by removing the line in the file that sets the header to the text/xml content type. This line may cause your browser to try to parse XML and make it difficult to see your debugging messages.

Creating the map

This section shows you how to develop the map example in this tutorial using JavaScript, and the output XML file. Read the documentation to learn more about the basics of creating a Google map.

Create a new file in a text editor and save it as index.html. Read the sections that follow to understand the code that you can add to this file.

Loading the XML file

To load the XML file into the page, you can take advantage of the browser-provided XMLHttpRequest object. This object allows you retrieve a file that resides on the same domain as the requesting webpage, and is the basis of "AJAX" programming.

Define your own function for loading the file, and call it downloadUrl(). The function takes two parameters:

  1. url specifies the path to either your XML file or to the PHP script that generates the file, depending on whether you want to dynamically update the XML file when your database changes. This map in this tutorial calls a static XML file for the marker data.
    It is usually easiest to have this XML file reside in the same directory as the HTML file so that you can just refer to it by filename.
  2. callback indicates the function that the script calls when the XML returns to the JavaScript.

The code below shows you the function declaration.

 
function downloadUrl(url,callback) {
 
var request = window.ActiveXObject ?
     
new ActiveXObject('Microsoft.XMLHTTP') :
     
new XMLHttpRequest;

 request
.onreadystatechange = function() {
   
if (request.readyState == 4) {
     request
.onreadystatechange = doNothing;
     callback
(request, request.status);
   
}
 
};

 request
.open('GET', url, true);
 request
.send(null);
}

Note: Since the XMLHttpRequest is asynchronous, the callback function initiates the downloadURL function based on the size of the XML file. The bigger your XML file, the longer it may take. For this reason, it would be best not to put any code after the downloadUrl that relies on the markers inside the callback function. Instead, such code can be put inside the callback function.

Now that you have defined the function, you can call it from your code, passing in the name of your PHP file and callback function. The map in this tutorial calls a static XML file for the marker data, as in the code below.

 

downloadUrl
('https://storage.googleapis.com/mapsdevsite/json/mapmarkers2.xml', function(data) {
 
var xml = data.responseXML;
 
var markers = xml.documentElement.getElementsByTagName('marker');
 
Array.prototype.forEach.call(markers, function(markerElem) {
   
var id = markerElem.getAttribute('id');
   
var name = markerElem.getAttribute('name');
   
var address = markerElem.getAttribute('address');
   
var type = markerElem.getAttribute('type');
   
var point = new google.maps.LatLng(
        parseFloat
(markerElem.getAttribute('lat')),
        parseFloat
(markerElem.getAttribute('lng')));

   
var infowincontent = document.createElement('div');
   
var strong = document.createElement('strong');
    strong
.textContent = name
    infowincontent
.appendChild(strong);
    infowincontent
.appendChild(document.createElement('br'));

   
var text = document.createElement('text');
    text
.textContent = address
    infowincontent
.appendChild(text);
   
var icon = customLabel[type] || {};
   
var marker = new google.maps.Marker({
      map
: map,
      position
: point,
      label
: icon.label
   
});

Creating custom markers

The code below shows you how to add custom labels for your markers by first creating an associative array. This associates your labels with the marker type strings: restaurant or bar. This makes the labels easy to reference when you create markers from the XML file.

 
var customLabel = {
  restaurant
: {
    label
: 'R'
 
},
  bar
: {
    label
: 'B'
 
}
};

Creating markers and info windows

When creating markers, you can retrieve the appropriate marker labels by using the type as a key for the customLabel associated array. Forward the .label property as a google.maps.Marker constructor option.

Next, create the HTML that you want to show up in the info window by concatenating the name, address, and HTML tags to emphasize the name.

If you store HTML-formatted descriptions in your database, you may have to deal with escaping HTML entities and be bound to that HTML output. By waiting until you have retrieved each attribute separately in the JavaScript, you are free to play around with the HTML on the client side and can quickly preview new formatting.

After constructing the HTML string, the code below adds an event listener to the marker which displays an info window on click.

 
marker.addListener('click', function() {
  infoWindow
.setContent(infowincontent);
  infoWindow
.open(map, marker);
});

Putting it all together

Open the index.html file in a browser. When the page loads, the initMap function sets up the map and then calls the downloadUrl function. This function iterates through all the marker elements, and retrieves the name, address, type, and latLng attributes for each marker element.
The code then creates markers, adds the markers to the map, and binds an info window to each marker to display a description on click.

More information

If you want to plot markers on a map using your own location data, you can use a batch geocoding service to convert addresses into latitudes and longitudes for your markers.

Comment (0) Hits: 11094



Thanks to Source: Visual-Representation-of-SQL-Joins explained in detail by C.L. Moffatt !!!
Comment (0) Hits: 3215
From: http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html

Table 12.13 Date/Time Functions

NameDescription
ADDDATE() Add time values (intervals) to a date value
ADDTIME() Add time
CONVERT_TZ() Convert from one timezone to another
CURDATE() Return the current date
CURRENT_DATE(),CURRENT_DATE Synonyms for CURDATE()
CURRENT_TIME(),CURRENT_TIME Synonyms for CURTIME()
CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP Synonyms for NOW()
CURTIME() Return the current time
DATE_ADD() Add time values (intervals) to a date value
DATE_FORMAT() Format date as specified
DATE_SUB() Subtract a time value (interval) from a date
DATE() Extract the date part of a date or datetime expression
DATEDIFF() Subtract two dates
DAY() Synonym for DAYOFMONTH()
DAYNAME() Return the name of the weekday
DAYOFMONTH() Return the day of the month (0-31)
DAYOFWEEK() Return the weekday index of the argument
DAYOFYEAR() Return the day of the year (1-366)
EXTRACT() Extract part of a date
FROM_DAYS() Convert a day number to a date
FROM_UNIXTIME() Format UNIX timestamp as a date
GET_FORMAT() Return a date format string
HOUR() Extract the hour
LAST_DAY Return the last day of the month for the argument
LOCALTIME(), LOCALTIME Synonym for NOW()
LOCALTIMESTAMP,LOCALTIMESTAMP() Synonym for NOW()
MAKEDATE() Create a date from the year and day of year
MAKETIME() Create time from hour, minute, second
MICROSECOND() Return the microseconds from argument
MINUTE() Return the minute from the argument
MONTH() Return the month from the date passed
MONTHNAME() Return the name of the month
NOW() Return the current date and time
PERIOD_ADD() Add a period to a year-month
PERIOD_DIFF() Return the number of months between periods
QUARTER() Return the quarter from a date argument
SEC_TO_TIME() Converts seconds to 'HH:MM:SS' format
SECOND() Return the second (0-59)
STR_TO_DATE() Convert a string to a date
SUBDATE() Synonym for DATE_SUB() when invoked with three arguments
SUBTIME() Subtract times
SYSDATE() Return the time at which the function executes
TIME_FORMAT() Format as time
TIME_TO_SEC() Return the argument converted to seconds
TIME() Extract the time portion of the expression passed
TIMEDIFF() Subtract time
TIMESTAMP() With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD() Add an interval to a datetime expression
TIMESTAMPDIFF() Subtract an interval from a datetime expression
TO_DAYS() Return the date argument converted to days
TO_SECONDS() Return the date or datetime argument converted to seconds since Year 0
UNIX_TIMESTAMP() Return a UNIX timestamp
UTC_DATE() Return the current UTC date
UTC_TIME() Return the current UTC time
UTC_TIMESTAMP() Return the current UTC date and time
WEEK() Return the week number
WEEKDAY() Return the weekday index
WEEKOFYEAR() Return the calendar week of the date (0-53)
YEAR() Return the year
YEARWEEK() Return the year and week

Here is an example that uses date functions. The following query selects all rows with a date_col value from within the last 30 days:

mysql> SELECT something FROM tbl_name
    -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
Comment (0) Hits: 3225
X