mysql - 试图将多个数据插入一行，但却得到多行

Question

我正在尝试进行多对多数据库选择，其中我有事件 和音乐风格(以及其他)。然而，由于一个事件可以有多种音乐风格，我决定采用多对多关系模型。

这是我创建的:

SELECT 
     a.* FROM music_types AS a, events_music_types AS b 
WHERE 
     a.id = b.music_type_id 
AND 
     b.event_id = events.ID

并将其插入到我的代码中，现在看起来像这样:

$query =    
"SELECT
    events.EVENT_NAME, events.start_datetime, events.end_datetime, events.VENUE_LOCATION, events.PARTY_TYPE, events.IMAGE_URL, events.ENTRANCE_PRICE, 
    venues.VENUE_NAME, venues.BEER_PRICE, venues.WINE_PRICE, SPIRITS_PRICE, 
    party_types.PARTYTYPE, 
    a.*
FROM events
INNER JOIN venues 
    ON events.VENUE_LOCATION = venues.ID
INNER JOIN party_types
    ON events.PARTY_TYPE = party_types.ID
INNER JOIN music_styles AS a, events_music_styles AS b
WHERE start_datetime >= '$DATE_START_SELECTED'
AND end_datetime < '$DATE_END_SELECTED' 
AND a.id = b.music_style_id 
AND b.event_id = events.ID
";
$result = mysql_query($query) or die(mysql_error());

echo "<table border='1'>
<tr>
<th> Poster </th>
<th> Event Name </th>
<th> Venue Name </th>
<th> Party Type </th>
<th> Entrance Price </th>
<th> Music </th>
<th> € of Beer </th>
<th> € of Wine </th>
<th> € of Spirits </th>
</tr>"; 

while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

echo "<tr>";
echo "<td><IMG src='" . $row['IMAGE_URL'] . "'></td>";
echo "<td>" . $row['EVENT_NAME'] . "</td>";
echo "<td>" . $row['VENUE_NAME'] . "</td>";
echo "<td>" . $row['PARTYTYPE'] . "</td>";
echo "<td>" . $row['ENTRANCE_PRICE'] . "</td>";
echo "<td>" . $row['MUSIC_STYLE_NAME'] . "</td>";
echo "<td>" . $row['BEER_PRICE'] . "</td>";
echo "<td>" . $row['WINE_PRICE'] . "</td>";
echo "<td>" . $row['SPIRITS_PRICE'] . "</td>";
echo "</tr>"; 
}

但是，虽然代码确实获取了一个事件的所有音乐风格，但它并没有将其放在一行中，而是根据每个事件的音乐风格的次数复制该行。

我如何将它组合成一行并用逗号分隔音乐风格？谢谢 :)!

Answer 1

将您的查询更改为 -

$query =    
"SELECT
    events.EVENT_NAME,
    events.start_datetime,
    events.end_datetime,
    events.VENUE_LOCATION,
    events.PARTY_TYPE,
    events.IMAGE_URL,
    events.ENTRANCE_PRICE, 
    venues.VENUE_NAME,
    venues.BEER_PRICE,
    venues.WINE_PRICE,
    SPIRITS_PRICE, 
    party_types.PARTYTYPE,
    GROUP_CONCAT(music_styles.MUSIC_STYLE_NAME) AS MUSIC_STYLE_NAME
FROM events
INNER JOIN venues 
    ON events.VENUE_LOCATION = venues.ID
INNER JOIN party_types
    ON events.PARTY_TYPE = party_types.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 >= '$DATE_START_SELECTED'
AND end_datetime < '$DATE_END_SELECTED' 
GROUP BY events.ID
";

你也有混合的连接样式，我现在已经更正了。