title: PHP & MySQL and Leaflet API
body: <p>I hit a bit of a wall here regarding using PHP &amp; MySQL with Leaflet API. I started with PHP &amp; MYSQL several months ago and I'm kind a newbie in that field but I'm willing to learn so please give me few pointers regarding my problem.</p>
  
  <p>Question is kind a similar to that one:
  <a href="https://stackoverflow.com/questions/17775627/creating-a-geojson-in-php-from-mysql-to-use-with-mapbox-javascript-api">Creating a GeoJson in php from MySql to use with MapBox javascript API</a></p>
  
  <p>So, I'm trying to get markers from a MySQL table using PHP and render it using Leaflet API</p>
  
  <p>First, I created MySQL Table with some data:</p>
  
  <pre><code>-- phpMyAdmin SQL Dump
  -- version 4.4.6
  -- http://www.phpmyadmin.net
  --
  -- Host: localhost
  -- Generation Time: Jan 17, 2016 at 08:36 PM
  -- Server version: 5.6.24
  -- PHP Version: 5.6.8
  
  SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  SET time_zone = "+00:00";
  
  
  /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  /*!40101 SET NAMES utf8 */;
  
  --
  -- Database: `web_gis`
  --
  CREATE DATABASE IF NOT EXISTS `web_gis` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
  USE `web_gis`;
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `baza`
  --
  
  DROP TABLE IF EXISTS `baza`;
  CREATE TABLE IF NOT EXISTS `baza` (
    `id` int(11) NOT NULL,
    `operator` varchar(100) NOT NULL,
    `lokacija` varchar(100) NOT NULL,
    `x` float NOT NULL,
    `y` float NOT NULL,
    `prijavljen` date NOT NULL
  ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
  
  --
  -- Dumping data for table `baza`
  --
  
  INSERT INTO `baza` (`id`, `operator`, `lokacija`, `x`, `y`, `prijavljen`) VALUES
  (1, 'Tele 2', 'OiV stup Hum na Sutli', 46.2135, 15.672, '2016-01-14'),
  (2, 'T-Mobile HR', 'OiV stup Straža', 46.2179, 15.6999, '2016-01-03'),
  (3, 'T-Mobile HR', 'Lupinjak', 46.2016, 15.7412, '2016-01-23'),
  (4, 'T-Mobile HR', 'Klenovec Humski 89\\1', 46.2169, 15.7268, '2016-01-01');
  --
  -- Indexes for dumped tables
  --
  --
  -- Indexes for table `baza`
  --
  ALTER TABLE `baza`
    ADD PRIMARY KEY (`id`);
  
  --
  -- AUTO_INCREMENT for dumped tables
  --
  --
  -- AUTO_INCREMENT for table `baza`
  --
  ALTER TABLE `baza`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=8;
  /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  </code></pre>
  
  <p>Next, here is my PHP (<em>bazneStanice_geojson.php</em>) code which I copied from this site and adjusted a little (changed the name of base to be more precise):</p>
  
  <p><a href="https://github.com/bmcbride/PHP-Database-GeoJSON/blob/master/simple_points/mysql_points_geojson.php" rel="nofollow noreferrer">https://github.com/bmcbride/PHP-Database-GeoJSON/blob/master/simple_points/mysql_points_geojson.php</a></p>
  
  <pre><code>&lt;?php
  /**
   * Title:   SQLite to GeoJSON (Requires https://github.com/phayes/geoPHP)
   * Notes:   Query a SQLite table or view (with a WKB GEOMETRY field) and return the results in GeoJSON format, suitable for use in OpenLayers, Leaflet, etc. Use QGIS to OGR to convert your GIS data to SQLite.
   * Author:  Bryan R. McBride, GISP
   * Contact: bryanmcbride.com
   * GitHub:  https://github.com/bmcbride/PHP-Database-GeoJSON
   */
  
  
  # Connect to SQLite database
  $conn = new PDO('mysql:host=localhost;dbname=web_gis','neven','gis');
  
  # Build SQL SELECT statement and return the geometry as a GeoJSON element
  $sql = 'SELECT *, x AS x, y AS y FROM baza';
  
  
  # Try query or error
  $rs = $conn-&gt;query($sql);
  if (!$rs) {
      echo 'An SQL error occured.\n';
      exit;
  }
  
  # Build GeoJSON feature collection array
  $geojson = array(
     'type'      =&gt; 'FeatureCollection',
     'features'  =&gt; array()
  );
  
  # Loop through rows to build feature arrays
  while ($row = $rs-&gt;fetch(PDO::FETCH_ASSOC)) {
      $properties = $row;
      # Remove x and y fields from properties (optional)
      unset($properties['x']);
      unset($properties['y']);
      $feature = array(
          'type' =&gt; 'Feature',
          'geometry' =&gt; array(
              'type' =&gt; 'Point',
              'coordinates' =&gt; array(
                  $row['x'],
                  $row['y']
              )
          ),
          'properties' =&gt; $properties
      );
      # Add feature arrays to feature collection array
      array_push($geojson['features'], $feature);
  }
  
  header('Content-type: application/json');
  echo json_encode($geojson, JSON_NUMERIC_CHECK);
  $conn = NULL;
  // print_r($geojson);
  ?&gt;
  </code></pre>
  
  <p>And here is script block of code. (<em>skripta_mysql.js</em>) I must point out that I successfully rendered map and the only thing that I'm missing are the points/markers from MySQL table. </p>
  
  <pre><code>var karta = L.tileLayer('https://api.tiles.mapbox.com/v4/{id}/{z}/{x}/{y}.png?access_token=pk.eyJ1IjoibWFwYm94IiwiYSI6IjZjNmRjNzk3ZmE2MTcwOTEwMGY0MzU3YjUzOWFmNWZhIn0.Y8bhBaUMqFiPrDRW9hieoQ', {
  maxZoom: 18,
  attribution: 'Map data &amp;copy; &lt;a href="http://openstreetmap.org"&gt;OpenStreetMap&lt;/a&gt; contributors, ' +
  '&lt;a href="http://creativecommons.org/licenses/by-sa/2.0/"&gt;CC-BY-SA&lt;/a&gt;, ' +
  'Imagery © &lt;a href="http://mapbox.com"&gt;Mapbox&lt;/a&gt;',
  id: 'mapbox.streets'
  });
  
  bazne_stanice = new L.geoJson(null, {
      pointToLayer: function (feature, latlng) {
      return L.marker(latlng, {
  
      });
  },
  onEachFeature: function (feature, layer) {
  if (feature.properties) {
      var content = '&lt;table border="1" style="border-collapse:collapse;" cellpadding="2"&gt;' +
      '&lt;tr&gt;' + '&lt;th&gt;ID&lt;/th&gt;' + '&lt;td&gt;' + feature.properties.operator + '&lt;/td&gt;' + '&lt;/tr&gt;' +
      '&lt;tr&gt;' + '&lt;th&gt;Name&lt;/th&gt;' + '&lt;td&gt;' + feature.properties.lokacija + '&lt;/td&gt;' + '&lt;/tr&gt;' +
      '&lt;tr&gt;' + '&lt;th&gt;Address&lt;/th&gt;' + '&lt;td&gt;' + feature.properties.y + '&lt;/td&gt;' + '&lt;/tr&gt;' +
      '&lt;tr&gt;' + '&lt;th&gt;Town&lt;/th&gt;' + '&lt;td&gt;' + feature.properties.prijavljen + '&lt;/td&gt;' + '&lt;/tr&gt;' +
      '&lt;table&gt;';
  layer.bindPopup(content);}}
  });
  
  $.getJSON("bazneStanice_geojson.php", function (data) {
          bazne_stanice.addData(data);
      });
  
  var map = L.map('map', {
          center: [46.15796, 15.75336],
          zoom: 9,
          layers: [karta, bazne_stanice]
      });
  
  var baseLayers = {
          "Podloga": karta
      };
  
      var overlays = {
          "Bazne stanice": bazne_stanice
      };
  
      L.control.layers(baseLayers, overlays).addTo(map);
  </code></pre>
  
  <p>When I print variable $geojson; from php code I get array like this</p>
  
  <pre><code>Array
  (
      [type] =&gt; FeatureCollection
      [features] =&gt; Array
          (
              [0] =&gt; Array
                  (
                      [type] =&gt; Feature
                      [geometry] =&gt; Array
                          (
                              [type] =&gt; Point
                              [coordinates] =&gt; Array
                                  (
                                      [0] =&gt; 46.2135
                                      [1] =&gt; 15.672
                                  )
  
                          )
  
                      [properties] =&gt; Array
                          (
                              [id] =&gt; 4
                              [operator] =&gt; Tele 2
                              [lokacija] =&gt; OiV stup Hum na Sutli
                              [prijavljen] =&gt; 2016-01-14
                          )
  
                  )....
  </code></pre>
  
  <p>To be fair I'm a bit confused. I really don't know what am I missing, followed everthing step by step but still without any luck rendering markers. Is it problem in <code>$geojson</code> variable?</p>
  
  <p>Should I be getting combined coordinates in [0]row? 
  Something like this?</p>
  
  <pre><code> [coordinates] =&gt; Array
  (
   [0] =&gt; 46.2135, 15.672
  )
  </code></pre>
  
  <p>============================</p>
  
  <p>UPDATE:</p>
  
  <p>When I try to echo  </p>
  
  <pre><code>echo json_encode($geojson, JSON_NUMERIC_CHECK); 
  </code></pre>
  
  <p>I didn't get any results 
  and I should be getting something like this if I'm not mistaken </p>
  
  <pre><code>{ "type": "Feature", "properties": { "Operator": "T-Mobile HR", "Lokacija": "Poljana Sutlanska 8, Zagorska Sela", "Prijavljen": "21.12.2010.", "Odjavljeno": null }, "geometry": { "type": "Point", "coordinates": [ 431501.48819855711, 5110280.408429144 ] } }
  </code></pre>