php - 如果 Locations 相等，则将多个结果合并为一个

Question

我正在尝试创建谷歌地图标记来显示事件，但我遇到的问题是有时事件发生在同一位置 - 因此标记重叠。

我想如果更多行具有相同的venues.VENUE_LOCATION，是否可以将结果折叠成一个。

因此，与其生成这样的 xml:

<marker lat="48.153938" lng="17.108459" venue="2" html="Event 1 - Jan 1" />
<marker lat="48.153938" lng="17.108459" venue="2" html="Event 2 - Jan 10" />
<marker lat="48.153938" lng="17.108459" venue="2" html="Event 3 - Jan 18" />
<marker lat="49.459843" lng="17.564821" venue="5" html="Event 55 - Jan 4" />
<marker lat="49.459843" lng="17.564821" venue="5" html="Event 70 - Jan 10" />

我会有一个标记，其中包含该位置所有标记的信息。

<marker lat="48.153938" lng="17.108459" venue="2" html="Event 1 - Jan 1<BR>Event 2 - Jan 10<BR>Event 3 - Jan 18" />
<marker lat="49.459843" lng="17.564821" venue="5" html="Event 55 - Jan 4<BR>Event 70 - Jan 10" />

知道我该怎么做吗？这是我当前的 php 代码

    $query = "SELECT
    events.ID,
    events.EVENT_NAME,
    events.start_datetime,
    events.end_datetime,
    events.VENUE_LOCATION,
    venues.VENUE_NAME,
    venues.VENUE_LOCATION,
    venues.LAT,
    venues.LNG,
    GROUP_CONCAT(music_styles.MUSIC_STYLE_NAME) AS MUSIC_STYLE_NAME
    FROM events
    INNER JOIN venues 
    ON events.VENUE_LOCATION = venues.ID
    INNER JOIN events_music_styles
    ON events.ID = events_music_styles.event_id
    INNER JOIN music_styles
    ON events_music_styles.music_style_id = music_styles.id
    WHERE start_datetime >= '$phpFromDate'
    AND start_datetime <= '$phpToDate' 
";


$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}



header("Content-type: text/xml");
echo '<markers>';

if(mysql_num_rows($result) > 0)
{
while ($row = @mysql_fetch_assoc($result))
{
  echo '<marker ';
  echo 'event_id="' . parseToXML($row['ID']) . '" ';
  echo 'lat="' . parseToXML($row['LAT']) . '" ';
  echo 'lng="' . parseToXML($row['LNG']) . '" ';
  echo 'venue="' . parseToXML($row['VENUE_LOCATION']) . '" ';
  echo '/>';
}
}
echo '</markers>';

谢谢!

Answer 1

尝试根据 GROUP_CONCAT 修改您的 SQL 查询:

GROUP_CONCAT(music_styles.MUSIC_STYLE_NAME SEPARATOR '<br>') AS MUSIC_STYLE_NAME

在查询末尾添加 GROUP BY 语句:

GROUP BY venues.LAT, venues.LNG

然后当你打印出marker标签时，添加html属性:

echo 'html="' . parseToXML($row['MUSIC_STYLE_NAME']) . '" ';

所需代码:

$query = "SELECT
    events.ID,
    events.EVENT_NAME,
    events.start_datetime,
    events.end_datetime,
    events.VENUE_LOCATION,
    venues.VENUE_NAME,
    venues.VENUE_LOCATION,
    venues.LAT,
    venues.LNG,
    GROUP_CONCAT(music_styles.MUSIC_STYLE_NAME SEPARATOR '<br/>') AS MUSIC_STYLE_NAME
    FROM events
    INNER JOIN venues 
    ON events.VENUE_LOCATION = venues.ID
    INNER JOIN events_music_styles
    ON events.ID = events_music_styles.event_id
    INNER JOIN music_styles
    ON events_music_styles.music_style_id = music_styles.id
    WHERE start_datetime >= '$phpFromDate'
    AND start_datetime <= '$phpToDate'
    GROUP BY venues.LAT, venues.LNG
";


$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}



header("Content-type: text/xml");
echo '<markers>';

if(mysql_num_rows($result) > 0)
{
    while ($row = @mysql_fetch_assoc($result))
    {
      echo '<marker ';
      echo 'event_id="' . parseToXML($row['ID']) . '" ';
      echo 'lat="' . parseToXML($row['LAT']) . '" ';
      echo 'lng="' . parseToXML($row['LNG']) . '" ';
      echo 'venue="' . parseToXML($row['VENUE_LOCATION']) . '" ';
      echo 'html="' . parseToXML($row['MUSIC_STYLE_NAME']) . '" ';
      echo '/>';
    }
}
echo '</markers>';