使用MySQL，JSON和jQuery插件Gmap3填充Google地图标记 [英] Populate Google Map Markers using MySQL, JSON and jQuery plugin Gmap3
问题描述
我有问题需要显示哪些数据是从MySQL数据库中提取的。 JSON格式不正确，尽管JSON有效。我猜我的问题至少有两种方法，1.修改MySQL查询或2.在使用Gmap3构建Google Map之前用jQuery处理JSON输出。我和他们中的任何一个都斗争过。
my_json.php中的MySQL查询和JSON编码：
try {
$ db = new mysqli（DB_HOST，DB_USER，DB_PASSWORD，DB_NAME）;
$ tatorte = $ db-> query（
SELECT p.post_title as title，
max（pm.meta_key ='_cmb_tatort_map_latitude'then pm.meta_value end ）作为纬度，
最大（当pm.meta_key ='_cmb_tatort_map_longitude'然后pm.meta_value结束时）作为lng，
最大（当pm.meta_key ='_cmb_subline'然后pm.meta_value结束时）为subline，
max（case pm when pm.meta_key ='_cmb_teaser_text'then pm.meta_value end）as teaser
FROM mob_posts p join
mob_postmeta pm
on pm.post_id = p。 ID
WHERE pm.meta_key in（'_cmb_tatort_map_latitude'，'_cmb_tatort_map_longitude'，'_cmb_subline'，'_cmb_teaser_text'）和
p.post_type ='tatort'
group by p.id
ORDER BY p.id DESC
LIMIT 100;
）;
$ rows = array（）;
while（$ locations = $ tatorte-> fetch_assoc（））{
$ rows [] = array（'values'=> $ locations）;
$ json = array（
center=> array（51.95026490,11.69227350），//在给定位置的中心地图
tatorte => $ rows
）;
$ b $ header（'Content-Type：application / json; charset = UTF-8;'）;
echo json_encode（$ json）;
} catch（Exception $ e）{
echo $ e-> getMessage（）;
}
所以这似乎工作正常，我得到一个有效的JSON，如下所示：
{
center：[
51.9502649，
11.6922735
] ，
tatorte：[
{
values：{
title：my title，
lat：51.7920562，
lng：11.141447999999968，
subline：我的崇高，
teaser：我的传情文本
}
}
]
}
显然，格式应该是这样的：
{
center：[
46.578498，
2.457275
]，
tatorte：[
{
lat：49.00408，
lng：2.56228，
data：{
title：my标题，
subline：我的崇高，
teaser：我的挑逗b
$ b $ c
<所以我尝试在jQuery中加载和操作JSON，如下所示：
function loadData（）{
var标记= [];
$ .ajax（{
url：'my-json-load.php'，
success：function（data）{
var markers = [];
$ .each（data，function（key，val）{
var position = [val.lat，val.lng];
markers.push（{latLng：position}）;
} ）;
display（data.center，data.tatorte）;
}
}）;
}
然后，使用Gmap3插件显示它们：
$（function（）{
loadData（）;
}）;
函数display（center，tatorte）{
$（'＃map_canvas'）。gmap3（{
map：{
options：{
center ：center，
zoom：7，
mapTypeId：google.maps.MapTypeId.TERRAIN
}
}，
标记：{
values：tatorte
}
}）;
}
中心点显示正确，与地图相同。只有标记丢失。我错过了一些正确的事情......我想这很容易，但是我没有太多关于jQuery / javascript的经验，或者如何改变MySQL查询以获得标记值的正确格式。任何人？非常感谢。谢谢。
解决方案尝试调整您的php。
while（$ locations = $ tatorte-> fetch_assoc（））{
$ lat = $ locations ['lat'];
$ lng = $ locations ['lng'];
$ values = json_encode（array（'title'=> $ locations ['title']，'subline'=> $ locations ['subline']，'teaser'=> $ locations ['teaser ']））;
$ rows [] = array（'lat'=> $ lat，'lng'=> $ lng，'values'=> $ values）;
}
I have problems to display markers which data I pull from a MySQL database. The JSON format is not right, although the JSON is valid. I guess there are at least 2 approaches to my problem, 1. to alter the MySQL query or 2. to manipulate the JSON output with jQuery before constructing the Google Map with Gmap3. I struggle with either of them..
MySQL query and JSON encode in my-json.php:
try {
$db = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$tatorte = $db->query("
SELECT p.post_title as title,
max(case when pm.meta_key = '_cmb_tatort_map_latitude' then pm.meta_value end) as lat,
max(case when pm.meta_key = '_cmb_tatort_map_longitude' then pm.meta_value end) as lng,
max(case when pm.meta_key = '_cmb_subline' then pm.meta_value end) as subline,
max(case when pm.meta_key = '_cmb_teaser_text' then pm.meta_value end) as teaser
FROM mob_posts p join
mob_postmeta pm
on pm.post_id = p.ID
WHERE pm.meta_key in ('_cmb_tatort_map_latitude', '_cmb_tatort_map_longitude', '_cmb_subline', '_cmb_teaser_text') and
p.post_type = 'tatort'
group by p.id
ORDER BY p.id DESC
LIMIT 100;
");
$rows = array();
while ($locations = $tatorte->fetch_assoc()) {
$rows[] = array('values' => $locations);
}
$json = array(
"center" => array(51.95026490,11.69227350), // center map at a given location
"tatorte" => $rows
);
header('Content-Type: application/json; charset=UTF-8;');
echo json_encode( $json );
} catch (Exception $e) {
echo $e->getMessage();
}
So this seems to be working fine, I get a valid JSON like so:
{
"center":[
51.9502649,
11.6922735
],
"tatorte":[
{
"values":{
"title":"my title",
"lat":"51.7920562",
"lng":"11.141447999999968",
"subline":"my sublime",
"teaser":"my teaser text"
}
}
]
}
Apparently, the format should be rather like this:
{
"center": [
46.578498,
2.457275
],
"tatorte": [
{
"lat": 49.00408,
"lng": 2.56228,
"data": {
"title":"my title",
"subline":"my sublime",
"teaser":"my teaser text"
}
}
]
}
So I try to load and manipulate my JSON in jQuery as so:
function loadData(){
var markers = [];
$.ajax({
url:'my-json-load.php',
success:function(data){
var markers = [];
$.each(data, function(key, val){
var position = [val.lat, val.lng];
markers.push({ latLng: position });
});
display(data.center, data.tatorte);
}
});
}
Then, to use the Gmap3 plugin to display them:
$(function(){
loadData();
});
function display(center, tatorte) {
$('#map_canvas').gmap3({
map:{
options:{
center: center,
zoom:7,
mapTypeId: google.maps.MapTypeId.TERRAIN
}
},
marker: {
values: tatorte
}
});
}
The center point is displayed correctly, same as the map as such. Only the markers are missing. And I am missing something to do it right... I guess it is quite easy, but I don't have much experience with jQuery/javascript or how to alter the MySQL query in order to get to the right format for the marker values. Anyone? Help greatly appreciated. Thanks.
解决方案 Try tweaking your php.
while ($locations = $tatorte->fetch_assoc()) {
$lat = $locations['lat'];
$lng = $locations['lng'];
$values = json_encode( array('title' => $locations['title'], 'subline' => $locations['subline'], 'teaser' => $locations['teaser']) );
$rows[] = array('lat' => $lat, 'lng' => $lng, 'values' => $values);
}
这篇关于使用MySQL，JSON和jQuery插件Gmap3填充Google地图标记的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
