title: Creating a GeoJson in php from MySql to use with MapBox javascript API
body: <p>What I'm trying to do is very simple; get marker's data from a Mysql table with my PHP code, convert that that to a geoJson ( used by MapBox ), send that geoJson to javascript and then populate those marker into my map.</p>
  
  <p>I've been reading very carefully the following two links, that apparently contains all the info I need to resolve this, but I'm not sure what I'm missing.</p>
  
  <p><a href="http://www.mapbox.com/mapbox.js/example/v1.0.0/markers-as-links/" rel="noreferrer">Here</a> you can see an example on how to populate markers from a geoJson in MapBox;</p>
  
  <p><a href="https://github.com/bmcbride/PHP-Database-GeoJSON/blob/master/mysql_geojson.php" rel="noreferrer">Here</a> you can see how to create a geoJson from a MySql table;</p>
  
  <p>My sql table look like this, here is the creation code plus some dummy data;</p>
  
  <pre><code>-- phpMyAdmin SQL Dump
  -- version 4.0.4.1
  -- http://www.phpmyadmin.net
  --
  -- Host: localhost
  -- Generation Time: Jul 21, 2013 at 03:26 PM
  -- Server version: 5.5.32-cll-lve
  -- PHP Version: 5.5.0
  
  SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  SET time_zone = "+00:00";
  
  --
  -- Database: `pgmq`
  --
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `mapa`
  --
  
  CREATE TABLE IF NOT EXISTS `mapa` (
    `contrato` int(11) NOT NULL,
    `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,
    PRIMARY KEY (`contrato`),
    UNIQUE KEY `contrato` (`contrato`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `mapa`
  --
  
  INSERT INTO `mapa` (`contrato`, `name`, `address`, `lat`, `lng`, `type`) VALUES
  (217, 'DIVANIR  BRASIL DA SILVA', '47 joao carolino da silva, guaruja - sao paulo, brazil', -23.950968, -46.289585, '11'),
  (233, 'ADEMIR  VIEIRA', '180 dois j, guaruja - sao paulo, brazil', -23.932041, -46.206879, '11'),
  (241, 'MARIA CECILIA  DOS SANTOS', '81 professor carvalho pinto, guaruja - sao paulo, brazil', -23.946516, -46.290428, '11'),
  (252, 'ELIETE  COSTA SANTOS', '387 maria de melo rodrigues, guaruja - sao paulo, brazil', -23.667521, -46.747810, '11'),
  (271, 'ROBERTO  SANTOS COUTO', '62 tapajos, guaruja - sao paulo, brazil', -23.949146, -46.284588, '11'),
  (275, 'UMBERTO FERREIRA  SOUZA NETO', '88 tapajos, guaruja - sao paulo, brazil', -23.949162, -46.284821, '11'),
  (276, 'SERGIO RICARDO  DOS SANTOS', '418 joana menezes de mello faro, guaruja - sao paulo, brazil', -23.994600, -46.256866, '11'),
  (278, 'MARIA APARECIDA  NUNES', '80 andre reboucas, guaruja - sao paulo, brazil', -23.945040, -46.297462, '11'),
  (285, 'WALTECES SOUZA  DA CONCEICAO', '298 maranhao, guaruja - sao paulo, brazil', -23.942638, -46.304131, '11'),
  (286, 'ROBERTO AUGUSTO  DE JESUS SOUZA', '38 dois c  caic cinquenta e cinco , guaruja - sao paulo, brazil', -23.994600, -46.256866, '11');
  </code></pre>
  
  <p>So here is my php code, where I get the data from the DB and create a GeoJson.</p>
  
  <pre><code>&lt;?php
  
  $connect = mysql_connect("localhost","user","password");
  
  $mapa = "SELECT * FROM pgmq.mapa ";
  
  $dbquery = mysql_query($mapa,$connect);
  
  $geojson = array( 'type' =&gt; 'FeatureCollection', 'features' =&gt; array());
  
  while($row = mysql_fetch_assoc($dbquery)){
  
  $marker = array(
                  'type' =&gt; 'Feature',
                  'features' =&gt; array(
                      'type' =&gt; 'Feature',
                      'properties' =&gt; array(
                          'title' =&gt; "".$row[name]."",
                          'marker-color' =&gt; '#f00',
                          'marker-size' =&gt; 'small'
                          //'url' =&gt; 
                          ),
                      "geometry" =&gt; array(
                          'type' =&gt; 'Point',
                          'coordinates' =&gt; array( 
                                          $row[lat],
                                          $row[lng]
                          )
                      )
                  )
      );
  array_push($geojson['features'], $marker['features']);
  }
  ?&gt;
  </code></pre>
  
  <p>Just to test this, if a "echo json_encode($marker)" here is a sample output;</p>
  
  <pre><code>{"type":"Feature","features":{"type":"Feature","properties":{"title":"DIVANIR  BRASIL DA SILVA","marker-color":"#f00","marker-size":"small"},"geometry":{"type":"Point","coordinates":"[-23.950968, -46.289585]"}}};
  </code></pre>
  
  <p>Now on this is what the javascript code looks like;</p>
  
  <pre><code>&lt;script&gt;
  var map = L.mapbox.map('map', 'examples.map-20v6611k').setView([-23.948714, -46.295508], 1);
  
  // The GeoJSON representing the two point features
  
  var geoJson = &lt;?php echo json_encode($marker,JSON_NUMERIC_CHECK); ?&gt;;
  
  // Pass features and a custom factory function to the map
  map.markerLayer.setGeoJSON(geoJson);
  map.markerLayer.on('click', function(e) {
      e.layer.unbindPopup();
      window.open(e.layer.feature.properties.url);
  });
  &lt;/script&gt;
  </code></pre>
  
  <p>And finally the complete HTML that actually shows the map, but not the markers;</p>
  
  <pre><code>&lt;?php
      $connect = mysql_connect("localhost","user","pass");
  
      $mapa = "SELECT * FROM pgmq.mapa ";
  
      $dbquery = mysql_query($mapa,$connect);
  
      $geojson = array( 'type' =&gt; 'FeatureCollection', 'features' =&gt; array());
  
      while($row = mysql_fetch_assoc($dbquery)){
  
      $marker = array(
                      'type' =&gt; 'Feature',
                      'features' =&gt; array(
                          'type' =&gt; 'Feature',
                          'properties' =&gt; array(
                              'title' =&gt; "".$row[name]."",
                              'marker-color' =&gt; '#f00',
                              'marker-size' =&gt; 'small'
                              //'url' =&gt; 
                              ),
                          "geometry" =&gt; array(
                              'type' =&gt; 'Point',
                              'coordinates' =&gt; array( 
                                              $row[lat],
                                              $row[lng]
                              )
                          )
                      )
          );
      array_push($geojson['features'], $marker['features']);
      }
  ?&gt;
  
  &lt;!DOCTYPE html&gt;
  &lt;html&gt;
  &lt;head&gt;
    &lt;meta name='viewport' content='width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no' /&gt;
    &lt;script src='//api.tiles.mapbox.com/mapbox.js/v1.3.1/mapbox.js'&gt;&lt;/script&gt;
    &lt;link href='//api.tiles.mapbox.com/mapbox.js/v1.3.1/mapbox.css' rel='stylesheet' /&gt;
    &lt;style&gt;
      body { margin:0; padding:0; }
      #map { position:absolute; top:0; bottom:0; width:100%; }
    &lt;/style&gt;
  &lt;/head&gt;
      &lt;body&gt;
      &lt;div id='map'&gt;&lt;/div&gt;
          &lt;script&gt;
          var map = L.mapbox.map('map', 'examples.map-20v6611k').setView([-23.948714, -46.295508], 1);
  
          // The GeoJSON representing the two point features
  
          var geoJson = &lt;?php echo json_encode($marker,JSON_NUMERIC_CHECK); ?&gt;;
  
          // Pass features and a custom factory function to the map
          map.markerLayer.setGeoJSON(geoJson);
          map.markerLayer.on('click', function(e) {
              e.layer.unbindPopup();
              window.open(e.layer.feature.properties.url);
          });
          &lt;/script&gt;
      &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>So, what am I missing? one thing I notice is that the output of my geoJson has " in the geometry array, while the one used in the MapBox example don't;</p>
  
  <pre><code>var geoJson = {
      type: 'FeatureCollection',
      features: [{
          type: 'Feature',
          properties: {
              title: 'Washington, D.C.',
              'marker-color': '#f00',
              'marker-size': 'large',
              url: 'http://en.wikipedia.org/wiki/Washington,_D.C.'
          },
          geometry: {
              type: 'Point',
              coordinates: [-77.03201, 38.90065]
          }
      },
      {
          type: 'Feature',
          properties: {
              title: 'Baltimore, MD',
              'marker-color': '#f00',
              'marker-size': 'large',
              url: 'http://en.wikipedia.org/wiki/Baltimore'
          },
          geometry: {
              type: 'Point',
              coordinates: [-76.60767, 39.28755]
          }
      }]
  };
  </code></pre>
  
  <p>Can anyone help me? there hasn't any real complication, i think it's just a data formatting problem or the way that I'm sending the GeoJson to the JS. </p>
  
  <p>Thanks in advance!</p>
  
  <p>I've just notice that this line;</p>
  
  <pre><code>var geoJson = &lt;?php echo json_encode($marker,JSON_NUMERIC_CHECK); ?&gt;;
  </code></pre>
  
  <p>should be;</p>
  
  <pre><code>var geoJson = &lt;?php echo json_encode($geojson,JSON_NUMERIC_CHECK); ?&gt;;
  </code></pre>
  
  <p>and in that case the result is;</p>
  
  <pre><code>    var map = L.mapbox.map('map', 'examples.map-20v6611k').setView([-23.948714, -46.295508], 1);
  
      // The GeoJSON representing the two point features
  
      var geoJson = ;
  
      // Pass features and a custom factory function to the map
      map.markerLayer.setGeoJSON(geoJson);
      map.markerLayer.on('click', function(e) {
          e.layer.unbindPopup();
          window.open(e.layer.feature.properties.url);
      });
  </code></pre>