I noticed a few things with the original code which were not right ( using mysql, error in parseXml, a search box which is not used and ... others ) so as I had a little spare time I had a play and put together a somewhat different version which, if studied and adapted, should help solve the issues faced.
An example table of the same structure......
mysql> describe markers;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| address | varchar(512) | YES | | NULL | |
| lat | float(10,6) | NO | | NULL | |
| lng | float(10,6) | NO | | NULL | |
| type | int(11) | YES | | 1 | |
+---------+--------------+------+-----+---------+----------------+
An example of the data.......
mysql> select * from markers limit 10;
+----+-------------------------+-------------------------------------------------------------+-----------+-----------+------+
| id | name | address | lat | lng | type |
+----+-------------------------+-------------------------------------------------------------+-----------+-----------+------+
| 42 | Nathro 17 x 135m | 1 Goynd Steading, Glenogil, Forfar, Angus DD8 3SW, UK | 56.793251 | -2.862310 | 1 |
| 43 | Ark Hill - 8 x 81m | 3 Dryburn Cottages, Glenogilvy, Forfar, Angus DD8 1UP, UK | 56.570656 | -3.051173 | 1 |
| 44 | Dodd Hill - 5 x 125m | 4 Backmuir Rd, Duntrune, Tealing, Dundee, Angus DD4 0PT, UK | 56.542511 | -2.905154 | 1 |
| 45 | Govals - 6 x 87m | B9127, Forfar, Angus DD8, UK | 56.582321 | -2.950902 | 1 |
| 46 | Carsegownie - 1 x78m | B9134, Forfar, Angus DD8, UK | 56.679512 | -2.806298 | 1 |
| 47 | North Tarbrax - 1 x 45m | 4 Nether Finlarg Farm Cottages, Forfar, Angus DD8 1XQ, UK | 56.571445 | -2.924766 | 1 |
| 48 | The Carrach - 9 x 84m | B951, Kirriemuir, Angus DD8, UK | 56.693844 | -3.131382 | 1 |
| 49 | Glaxo - 2 x 132m | 5 Meridian St, Montrose, Angus DD10 8DS, UK | 56.704315 | -2.466087 | 1 |
| 50 | Craignathro 1 x 24m | Dundee Rd, Forfar, Angus DD8, UK | 56.624084 | -2.884898 | 1 |
| 51 | Craignathro 2 - 1x41m | A90, Forfar, Angus DD8, UK | 56.620872 | -2.880220 | 1 |
+----+-------------------------+-------------------------------------------------------------+-----------+-----------+------+
A mysql Stored Procedure which I had written previously - minor modification to suit this particular question/problem.
CREATE PROCEDURE `spFindMarkers`(
IN `param_name` VARCHAR(64),
IN `param_lat` DOUBLE,
IN `param_lng` DOUBLE,
IN `param_radius` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Find markers based upon given starting lat/lng, a name and a radius'
begin
/* user supplied parameters / variables */
declare lat double default 0;
declare lng double default 0;
declare radius integer default 0;
declare _name varchar(64);
/* default parameters / variables */
declare earth_radius integer default 0;
declare lon1 float;
declare lat1 float;
declare lon2 float;
declare lat2 float;
/* create variables from input parameters */
set @lat=param_lat;
set @lng=param_lng;
set @radius=cast( param_radius as signed );
set @earth_radius=3956;
set @_name=cast(param_name as char character set utf8);
/* internal variables ~ do not edit */
set @lon1 = @lng - @radius/abs( cos( radians(@lat) ) * 69 );
set @lon2 = @lng + @radius/abs( cos( radians(@lat) ) * 69 );
set @lat1 = @lat - (@radius/69);
set @lat2 = @lat + (@radius/69);
select distinct
m.`id`,
m.`name`,
m.`address`,
m.`lat`,
m.`lng`,
round( @earth_radius * 2 * asin( sqrt( power( sin( ( @lat - m.`lat` ) * pi() / 180 / 2 ), 2 ) + cos( @lat * pi() / 180 ) * cos( m.`lat` * pi() / 180 ) *power( sin( ( @lng - m.`lng` ) * pi() / 180 / 2 ), 2 ) ) ),2) as `distance`
from `markers` m
where
( m.`lat` between @lat1 and @lat2 )
and
( m.`lng` between @lon1 and @lon2 )
/*
the `having` clause might not suit your requirements... edit as appropriate!
*/
having `distance` <= @radius or `name` like concat( '%', @_name, '%' ) or `address` like concat( '%', @_name, '%' )
order by `distance`
/*
A dynamic limit is possible but causes many issues with the PHP prepared statement
as you need to do a prepared statement within the stored procedure.
*/
limit 0,20;
end
An example of the xml generated ( db dependant of course )
<?xml version="1.0" encoding="utf-8"?>
<markers>
<marker id="230" name="Fletcherfield 2x45m turbines?" address="1 Dameye Farm Cottages, Logie, Kirriemuir, Angus DD8 5PR, UK" lat="56.660805" lng="-2.978641" distance="1.37"/>
<marker id="143" name="Longbank Kirriemuir - height not known" address="2 Crieff Terrace, Kirriemuir, Angus DD8 4LU, UK" lat="56.693573" lng="-2.961416" distance="2.04"/>
<marker id="229" name="Auchindorie 1x46m turbine" address="Auchindorie, Kirriemuir, Angus DD8, UK" lat="56.651802" lng="-3.039208" distance="2.10"/>
<marker id="97" name="Reedie Farm 2x45.6m turbines" address="A926, Kirriemuir, Angus DD8, UK" lat="56.658123" lng="-3.055901" distance="2.31"/>
<marker id="232" name="Cairnleith Farm 1x19.9m turbine" address="Ghenty, Kirriemuir, Angus DD8 5HF, UK" lat="56.669613" lng="-3.081222" distance="2.97"/>
<marker id="87" name="Genty, Airlie 1x34.5m turbine" address="Ghenty, Kirriemuir, Angus DD8, UK" lat="56.662685" lng="-3.084762" distance="3.20"/>
<marker id="66" name="Cortachy - Gallow Hill 1 x 46m turbine" address="B955, Kirriemuir, Angus DD8, UK" lat="56.731060" lng="-2.968154" distance="4.09"/>
<marker id="99" name="Sprottie Fauld 20m turbine" address="A94, Forfar, Angus DD8, UK" lat="56.610363" lng="-3.037190" distance="4.65"/>
<marker id="75" name="East Memus Farm 1x76.5m turbine" address="4 Church Terrace, Memus, Forfar, Angus DD8 3TY, UK" lat="56.731243" lng="-2.932820" distance="4.71"/>
<marker id="58" name="South Leckaway 1x78m turbine" address="3 Mains of Brigton Cottages, Forfar, Angus DD8 1TH, UK" lat="56.621323" lng="-2.923994" distance="4.79"/>
<marker id="145" name="Miltonbank Farm - height not known" address="B957, Forfar, Angus DD8, UK" lat="56.705330" lng="-2.886529" distance="4.90"/>
<marker id="48" name="The Carrach - 9 x 84m turbines" address="B951, Kirriemuir, Angus DD8, UK" lat="56.693844" lng="-3.131382" distance="5.02"/>
<marker id="144" name="East Memus Farm 1x76.5m turbine 0.8MW" address="1 Goynd Steading, Glenogil, Forfar, Angus DD8 3SW, UK" lat="56.734554" lng="-2.922835" distance="5.12"/>
<marker id="223" name="The Welton 2x47m turbines" address="B951, Kirriemuir, Angus DD8, UK" lat="56.694519" lng="-3.134095" distance="5.13"/>
<marker id="211" name="Kalulu House 1x45.8m turbine" address="1 Whig Street, Forfar, Angus DD8, UK" lat="56.711098" lng="-2.878921" distance="5.34"/>
<marker id="124" name="W Mains of Whitewell 2x46m turbines" address="1 Whig St, Forfar, Angus DD8, UK" lat="56.705421" lng="-2.866838" distance="5.59"/>
<marker id="50" name="Craignathro 1 x 24m turbine" address="Dundee Rd, Forfar, Angus DD8, UK" lat="56.624084" lng="-2.884898" distance="5.72"/>
<marker id="85" name="Ladenford 1x 46.5 turbine" address="1 Craignathro Farm Cottages, Forfar, Angus DD8 2LE, UK" lat="56.628838" lng="-2.877588" distance="5.75"/>
<marker id="128" name="Castleton of Eassie 3x25m turbines" address="A94, Forfar, Angus DD8, UK" lat="56.604778" lng="-3.086371" distance="5.79"/>
<marker id="53" name="Lour Farm 1x35m turbine" address="Dundee Rd, Forfar, Angus DD8, UK" lat="56.628407" lng="-2.875971" distance="5.82"/>
</markers>
The PHP & HTML - combined for simplicity into one page for testing. Best practise when dealing with user-supplied data in combination with database access in PHP is to use a prepared statement, either in mysqli or PDO, to avoid sql injection attacks. The code below uses PDO for the database layer but could quite as easily have been done with mysqli.
The sql itself in this example is written as a stored procedure ( as seen above ), the map's initial viewport is centred on Scotland rather than Australia as my database primarily contains locations in and around the area shown. I found a couple of minor errors in the javascript but with luck corrected those here.
<?php
/*
https://stackoverflow.com/questions/52495882/receipt-absent-data-from-the-database-store-locator-tutorial-google-php
This PHP code emulates the code that would be used in `storelocator.php`
and is presented inline for demo/test purposes only
*/
error_reporting( E_ALL );
ini_set( 'display_errors', 1 );
if( $_SERVER['REQUEST_METHOD']=='GET' && !empty( $_GET['lat'] ) && !empty( $_GET['lng'] ) ){
ob_clean();
header( 'Content-type: text/plain' );
$xml='no results';
/* PDO Database connection */
/*
database connection could very easily be an external file
called using `require` or `include` etc
*/
$dbport = 3306;
$dbhost = 'localhost';
$dbuser = 'root';
$dbpwd = 'xxx';
$dbname = 'xxx';
$options=array(
PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL,
PDO::ATTR_PERSISTENT => false,
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
PDO::ATTR_EMULATE_PREPARES => true,
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'utf8mb4\' COLLATE \'utf8mb4_unicode_ci\', @@sql_mode = STRICT_ALL_TABLES, @@foreign_key_checks = 1'
);
/* generate db connection string and PDO connection object */
$dsn='mysql:host='.$dbhost.';port='.$dbport.';dbname='.$dbname.';charset=UTF8';
$db = new PDO( $dsn, $dbuser, $dbpwd, $options );
/* sql statement to call the stored procedure in a prepared statement */
$sql='call `spFindMarkers`( :name, :lat, :lng, :radius );';
/* create the prepared statement */
$stmt=$db->prepare( $sql );
if( $stmt ){
/* filter incoming GET variables */
$args=array(
'lat' => FILTER_SANITIZE_NUMBER_FLOAT,
'lng' => FILTER_SANITIZE_NUMBER_FLOAT,
'radius' => FILTER_SANITIZE_NUMBER_INT,
'limit' => FILTER_SANITIZE_NUMBER_INT,
'name' => FILTER_SANITIZE_STRING
);
filter_input_array( INPUT_GET, $args );
$_GET = array_filter( $_GET );
/* create variables from GET parameters */
extract( $_GET );
/* create the SQL bound parameters array */
$params=array(
':name' => $name,
':lat' => $lat,
':lng' => $lng,
':radius' => $radius
);
/* execute the prepared statement */
$res = $stmt->execute( $params );
if( $res ){
/* Generate the XML response */
$dom = new DOMDocument('1.0','utf-8');
$root = $dom->appendChild( $dom->createElement('markers') );
/* process the recordset */
while( $rs=$stmt->fetch( PDO::FETCH_OBJ ) ){
$marker = $dom->createElement('marker');
$marker->setAttribute( 'id', $rs->id );
$marker->setAttribute( 'name', urldecode( $rs->name ) );
$marker->setAttribute( 'address', urldecode( $rs->address ) );
$marker->setAttribute( 'lat', $rs->lat );
$marker->setAttribute( 'lng', $rs->lng );
$marker->setAttribute( 'distance', $rs->distance );
$root->appendChild( $marker );
}
$xml=$dom->saveXML();
header( 'Content-type: application/xml' );
}
} else {
$xml='error';
}
$dom = $root = $db = null;
exit( $xml );
}
?>
<!DOCTYPE html >
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8"/>
<meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
<title>Creating a Store Locator on Google Maps</title>
<style>
#map {
height:calc(100vh - 2.6rem);
width:100%;
}
html, body {
height:100vh;
width:100%;
margin: 0;
padding: 0;
}
form{
height:2.5rem;
display:flex;
flex-direction:row;
flex-align:flex-start;
justify-content:flex-start;
align-items:center;
background:gold;
border-bottom:2px solid black;
font-family:calibri,verdana,arial;
}
form *{ padding:0.25rem;}
option[selected]{background:gold;}
</style>
<script>
var map;
var marker;
var circle;
var markers = [];
var infoWindow;
var oSelect;
function initMap() {
var uluru = {
lat: -25.344,
lng: 131.036
};
var default_location = {
lat:56.646577,
lng:-2.888609
};
map = new google.maps.Map( document.getElementById('map'), {
zoom: 10,
center: default_location,/* change to uluru ? */
mapTypeId:'roadmap',
mapTypeControlOptions:{ style: google.maps.MapTypeControlStyle.DROPDOWN_MENU }
});
/*
https://groups.google.com/forum/#!msg/Google-Maps-API/2k3T5hwI5Ck/RRso0D2jB1oJ
for icons....
*/
marker = new google.maps.Marker( { position: default_location, map: map, icon:'//maps.google.com/mapfiles/ms/icons/blue-pushpin.png' });
infoWindow = new google.maps.InfoWindow();
oRadius=document.getElementById('radiusSelect');
oBttn=document.getElementById('bttn');
oBttn.onclick = searchLocations;
oSelect=document.getElementById('locationSelect');
oSelect.onchange=function(event){
if( this.value != 'none' ){
oSelect.value=this.value;
google.maps.event.trigger( markers[ this.value ], 'click' );
}
};
circle=createcircle( default_location, oRadius.value );
oRadius.addEventListener( 'change',function(){
circle.setRadius( this.value * 1000 );
},false);
}
function searchLocations() {
var address = document.getElementById("addressInput").value;
var geocoder = new google.maps.Geocoder();
if( address=='' ){
alert('No point searching for a blank address now is there?');
return false;
}
geocoder.geocode({address: address}, function(results, status) {
if (status == google.maps.GeocoderStatus.OK) {
searchLocationsNear( results[0].geometry.location );
marker.setPosition( results[0].geometry.location );
circle.setCenter( results[0].geometry.location );
circle.setMap( map );
} else {
alert( address + ' not found' );
}
});
}
function downloadUrl(url,callback) {
var request = window.ActiveXObject ? new ActiveXObject('Microsoft.XMLHTTP') : new XMLHttpRequest;
request.onreadystatechange = function() {
if( this.readyState == 4 && this.status==200 ) callback( this.responseText, this.status );
};
request.open('GET', url, true);
request.send(null);
}
const createcircle=function(position,radius){
circle = new google.maps.Circle({
center:position,
radius:parseFloat( radius ) * 1000,
fillColor:'red',
fillOpacity:0.05,
strokeColor:'red',
strokeWeight:1,
clickable:true,
geodesic:true,
map:map
});
google.maps.event.addListener( circle, 'click', function(e){
this.setMap( null )
}.bind( circle ) );
return circle;
}
function searchLocationsNear( center ) {
clearLocations();
var radius = document.getElementById('radiusSelect').value;
var name = document.getElementById('addressInput').value;
var searchUrl = 'storelocator.php?lat=' + center.lat() + '&lng=' + center.lng() + '&radius=' + radius+'&name='+name;
/*
*
*
*
*
*
*
*****************************************/
/* !!! FOR TESTING ONLY - SAME PAGE !!! */
searchUrl ='?lat='+center.lat()+'&lng='+center.lng()+'&radius='+radius+'&name='+name
/* !!! FOR TESTING ONLY - SAME PAGE !!! */
/*****************************************
*
*
*
*
*
*
*
*/
downloadUrl( searchUrl, function( data ) {
var xml = parseXML( data );
var nodes = xml.documentElement.getElementsByTagName('marker');
var bounds = new google.maps.LatLngBounds();
if( nodes.length == 0 ){
alert('no results')
return;
}
for( var i = 0; i < nodes.length; i++ ) {
var node=nodes[i];
var id = node.getAttribute('id');
var name = node.getAttribute('name');
var name = node.getAttribute('name');
var address = node.getAttribute('address');
var distance = parseFloat(node.getAttribute('distance'));
var latlng = new google.maps.LatLng(
parseFloat( node.getAttribute('lat') ),
parseFloat( node.getAttribute('lng') )
);
createOption( name, distance, i, id );
createMarker( latlng, name, address, id );
bounds.extend( latlng );
}
map.fitBounds( bounds );
});
}
function createMarker( latlng, name, address, rid ) {
var html = "<b>" + name + "</b> <br/>" + address;
var marker = new google.maps.Marker({
map: map,
position: latlng,
rid:rid,
icon:'//maps.google.com/mapfiles/ms/icons/red-pushpin.png'
});
google.maps.event.addListener( marker, 'click', function(event){
infoWindow.setContent( html );
infoWindow.open( map, this );
oSelect.value=oSelect.querySelector('option[data-rid="'+this.rid+'"]').value;
});
markers.push( marker );
}
function createOption( name, distance, num, rid ) {
var option = document.createElement('option');
option.value = num;
option.innerHTML = name;
option.dataset.rid = rid;
oSelect.appendChild( option );
}
function clearLocations() {
infoWindow.close();
for( var i = 0; i < markers.length; i++ ) {
markers[i].setMap(null);
}
markers.length = 0;
oSelect.innerHTML = '';
var option = document.createElement('option');
option.value = 'none';
option.innerHTML = 'See all results:';
oSelect.appendChild(option);
oSelect.style.visibility = 'visible';
}
const parseXML=function( xml ){
try{
if( window.ActiveXObject ){
let oParser=new ActiveXObject('Microsoft.XMLDOM');
oParser.loadXML( xml );
return oParser;
} else {
let oParser = new DOMParser();
return oParser.parseFromString(xml,'application/xml');
}
}catch( err ){
console.log( err );
}
}
</script>
<script async defer src="https://maps.googleapis.com/maps/api/js?key=AIzaSyCbn9gIka8i-33HotlIor7GHMt2WEo_aAQ&callback=initMap"></script>
</head>
<body>
<form method='post'>
<label for='raddressInput'>Search location:</label>
<input type='text' id='addressInput' name='addressInput' size='15' value='kirrie' />
<label for='radiusSelect'>Radius:</label>
<select id='radiusSelect' name='radiusSelect' label='Radius'>
<option value=100>100 kms
<option value=90>90 kms
<option value=80>80 kms
<option value=70>70 kms
<option value=60>60 kms
<option value='50'>50 kms
<option value=40>40 kms
<option value='30'>30 kms
<option value='20'>20 kms
<option value='10' selected>10 kms
<option value='5'>5 kms
<option value='1'>1 kms
</select>
<input type='button' id='bttn' value='Search' />
<select id='locationSelect' name='locationSelect' style='width: 10%; visibility: hidden'></select>
</form>
<div id='map'></div>
</body>
</html>
