php - 使用 Jquery 复选框数组通过多对 MySql 数据库进行搜索？

Question

我一直在慢慢学习和构建我的小搜索系统 - 然而我似乎陷入了这个棘手的问题。

我有一个事件的多对关系数据库。每个事件可以有多种音乐风格存储在 events_music_styles 表中。 events_music_styles 表有自己的 ID 列:EVENT_ID 和 MUSIC_STYLE_ID。

当音乐风格和事件在另一个表中一起引用时，如何在事件表中搜索(通过使用复选框数组)音乐风格？

这是我到目前为止所拥有的:

HTML

<input type="checkbox" class="group1" id="checkbox1" value="latino">Latino<BR />
<input type="checkbox" class="group1" id="checkbox2" value="rock">Rock<BR />
<input type="checkbox" class="group1" id="checkbox3" value="oldies">Oldies<BR />
<input type="checkbox" class="group1" id="checkbox4" value="reggae">Reggae<BR />

<div id="AnswerField"></div>

Jquery

var VarDancingTo = new Array();
$('.group1:checked').each(function () {
    VarDancingTo[VarDancingTo.length] = $(this).val();
});


$("#AnswerField").text( VarDancingTo.join(', '));

以及一个简单的 PHP 表和我的完整数据库(如果有任何帮助的话)

$query = 'SELECT e.ID, e.EVENT_NAME, e.EVENT_DATE, e.ENTRANCE_PRICE, v.BEER_PRICE, v.WINE_PRICE, v.SPIRITS_PRICE, v.VENUE_NAME, l.LOCATION, GROUP_CONCAT(ms.MUSIC_STYLE_NAME) as `Styles`'.
     ' FROM events AS e'.
     ' INNER JOIN venues as v ON e.VENUE_LOCATION = v.ID'.
     ' INNER JOIN locations AS l ON e.VENUE_LOCATION = l.ID'.
     ' INNER JOIN events_music_styles AS ems ON e.ID = ems.EVENT_ID'.
     ' INNER JOIN music_styles AS ms ON ms.ID = ems.MUSIC_STYLE_ID'.
     ' GROUP BY e.ID';
$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}

Here's the relations between the tables:

以下是创建表语句:

SET FOREIGN_KEY_CHECKS=0;

-- Drop table locations
DROP TABLE IF EXISTS `locations`;

CREATE TABLE `locations` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `LOCATION` varchar(50),
  `LOCATION_SK` varchar(50) CHARACTER SET utf8,
  PRIMARY KEY(`ID`)
)
ENGINE=INNODB;

-- Drop table music_styles
DROP TABLE IF EXISTS `music_styles`;

CREATE TABLE `music_styles` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `MUSIC_STYLE_NAME` varchar(50),
  `MUSIC_STYLE_NAME_SK` varchar(50) CHARACTER SET utf8,
  PRIMARY KEY(`ID`)
)
ENGINE=INNODB;

-- Drop table venue_types
DROP TABLE IF EXISTS `venue_types`;

CREATE TABLE `venue_types` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `TYPE_NAME` varchar(50),
  `TYPE_NAME_SK` varchar(50) CHARACTER SET utf8,
  PRIMARY KEY(`ID`)
)
ENGINE=INNODB;

-- Drop table venues
DROP TABLE IF EXISTS `venues`;

CREATE TABLE `venues` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `VENUE_TYPE` int(11),
  `VENUE_LOCATION` int(11),
  `VENUE_NAME` varchar(50),
  `ADDRESS` varchar(255),
  `ICON_URL` varchar(100),
  `PAGE_URL` varchar(100),
  `LAT` int(100),
  `LNG` int(100),
  `VENUE_CLOSE_T_MO` varchar(50),
  `VENUE_CLOSE_T_TU` varchar(50),
  `VENUE_CLOSE_T_WE` varchar(50),
  `VENUE_CLOSE_T_TH` varchar(50),
  `VENUE_CLOSE_T_FR` varchar(50),
  `VENUE_CLOSE_T_SA` varchar(50),
  `VENUE_CLOSE_T_SU` varchar(50),
  `BEER_PRICE` int(11),
  `WINE_PRICE` int(11),
  `SPIRITS_PRICE` int(11),
  `IF_COFFEE` int(1) DEFAULT '1',
  `IF_DRAFT_BEER` int(1) DEFAULT '0',
  `IF_TEA` int(1) DEFAULT '1',
  `IF_HOT_CHOCOLATE` int(1) DEFAULT '0',
  `IF_BILLIARD` int(1) DEFAULT '0',
  `IF_HOOKAH` int(1) DEFAULT '0',
  `IF_OUTDOOR_PATIO` int(1) DEFAULT '0',
  `IF_OUTDOORS` int(1) DEFAULT '0',
  `IF_NON_SMOKING_AREA` int(1) DEFAULT '0',
  PRIMARY KEY(`ID`),
  CONSTRAINT `Ref_01` FOREIGN KEY (`VENUE_TYPE`)
    REFERENCES `venue_types`(`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Ref_02` FOREIGN KEY (`VENUE_LOCATION`)
    REFERENCES `locations`(`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE=INNODB;

-- Drop table events
DROP TABLE IF EXISTS `events`;

CREATE TABLE `events` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `VENUE_LOCATION` int(11),
  `EVENT_NAME` varchar(50),
  `EVENT_DATE` date,
  `EVENT_NAME_SK` varchar(50) CHARACTER SET utf8,
  `EVENT_DESC` varchar(255),
  `EVENT_DESC_SK` varchar(255) CHARACTER SET utf8,
  `IMAGE_URL` varchar(255),
  `EVENT_URL` varchar(255),
  `START_TIME` varchar(50),
  `END_TIME` varchar(50),
  `IF_ENTRANCE` int(1) DEFAULT '0',
  `ENTRANCE_PRICE` int(11) DEFAULT '0',
  PRIMARY KEY(`ID`),
  CONSTRAINT `Ref_03` FOREIGN KEY (`VENUE_LOCATION`)
    REFERENCES `venues`(`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE=INNODB;

-- Drop table events_music_styles
DROP TABLE IF EXISTS `events_music_styles`;

CREATE TABLE `events_music_styles` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `EVENT_ID` int(11),
  `MUSIC_STYLE_ID` int(11),
  PRIMARY KEY(`ID`),
  CONSTRAINT `Ref_05` FOREIGN KEY (`MUSIC_STYLE_ID`)
    REFERENCES `music_styles`(`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Ref_06` FOREIGN KEY (`EVENT_ID`)
    REFERENCES `events`(`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE=INNODB;

SET FOREIGN_KEY_CHECKS=1;

Answer 1

您可能希望将 events_music_styles 中的一对外键(EVENT_ID、MUSIC_STYLE_ID)设为唯一索引，否则您将能够关联相同的风格多次发生同一事件。

CREATE UNIQUE INDEX index_name
ON events_music_styles (EVENT_ID, MUSIC_STYLE_ID);

至于获取事件以及相关音乐类型的列表，我怀疑您必须在前端(javascript、PHP，无论您使用的其他什么)中对数据进行一些处理。 GROUP BY 方法不起作用，因为每个事件只能获得一种样式。这将需要对查询进行一些重写，并且对查询获取逻辑进行更多重写以根据需要格式化数据。

$query = '
SELECT 
    e.ID, 
    e.EVENT_NAME, 
    e.EVENT_DATE, 
    e.ENTRANCE_PRICE, 
    v.BEER_PRICE, 
    v.WINE_PRICE, 
    v.SPIRITS_PRICE, 
    v.VENUE_NAME, 
    l.LOCATION, 
    ms.MUSIC_STYLE_NAME
FROM events AS e
INNER JOIN venues as v ON e.VENUE_LOCATION = v.ID
INNER JOIN locations AS l ON e.VENUE_LOCATION = l.ID
INNER JOIN events_music_styles AS ems ON e.ID = ems.EVENT_ID
INNER JOIN music_styles AS ms ON ms.ID = ems.MUSIC_STYLE_ID;';
if ($result = mysql_query ($query))
{
    $eventDetails = array ();
    // I think keys always end up lowercase when returned by MySQL so I'm using lower case key names here.  If it doesn't work then try with upper case.
    while ($row = mysql_fetch_assoc ($result))
    {
        // Add the event details to the results if they don't already exist in the array
        if (!array_key_exists ($row ['id'], $eventDetails))
        {
            $eventDetails [$row ['id']] = array (
                'ID' => $row ['id'],
                'EVENT_NAME' => $row ['event_name'],
                'EVENT_DATE' => $row ['event_date'],
                // Insert your other columns here
                'STYLES' => array ($row ['music_style_name'])
            );
        }
        // As we've already seen this event before we only need to add the musical style from this row to the result
        else
        {
          $eventDetails [$row ['id']]['STYLES'][] = $row ['music_style_name'];
        }
    }
}
else
{
    // Error handling logic here
}

这应该生成一个如下所示的数组:

array (
    1 => array (
        'ID' => 1,
        'EVENT_NAME' => 'Some event name',
        'EVENT_DATE' => '11/11/2011',
        'STYLES' => array (
            0 => 'rock',
            1 => 'jazz'
        )
    ),
    2 => array (
        'ID' => 2,
        'EVENT_NAME' => 'Some other event name',
        'EVENT_DATE' => '22/11/2011',
        'STYLES' => array (
            0 => 'rock',
            1 => 'techno',
            2 => 'dance'
        )
    ),
    // ...
    n => array (
        'ID' => n,
        'EVENT_NAME' => 'Yet another event',
        'EVENT_DATE' => '12/12/2012',
        'STYLES' => array (
            0 => 'classical',
            1 => 'prog rock',
            2 => 'folk'
        )
    )
)

或者，如果您只想要一个以逗号分隔的流派列表，您可以为 STYLES 创建一个字符串而不是数组，并在每次循环中执行字符串连接，而不是将样式添加到数组中。

注意:由于我无法访问您数据的完整数据库，也没有时间构建模型数据库，因此我没有测试上述代码。它应该有效，但我不能做出任何 promise 。希望即使它不起作用，它仍然可以作为如何做你想做的事情的指南。