Search with Jquery checkbox array through a many-many MySql db?

I've been slowly learning and building my little search system - however I seem stuck at this tough question.

I have a many-many relation database of events. Each event can have multiple music styles stored in the events_music_styles table. The events_music_styles table has it's own ID column, EVENT_ID and MUSIC_STYLE_ID.

How can I search (through the use of a checkbox array) for music styles in the events table when the music styles and events are referenced in another table all together?

This is what I have so far:

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(', '));

and a simple PHP table with my complete database (if that's of any help)

$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:

Here are the create table statements:

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;

You might want to make the pair of foreign keys (EVENT_ID, MUSIC_STYLE_ID) in your events_music_styles a unique index, otherwise you'll be able to associate the same style with the same event multiple times.

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

As for getting the events plus the list of associated music types, I suspect you'll have to do some processing of the data in the front-end (javascript, PHP, whatever else you're using). The GROUP BY approach won't work, because you'll only get one style per event. This will require a bit of rewriting of your query, and somewhat more rewriting of your query fetching logic to format the data as needed.

$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
}

This should produce an array like the one below:

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'
        )
    )
)

Alternatively, if you just want a comma-separated list for the genres, you can create a string for STYLES instead of an array, and do a string concat each time through the loop instead of adding the style to an array.

NOTE: As I don't have access to a full database of your data and have no time to build a mockup database, I've not tested the above code. It ought to work but I can't make any promises. Hopefully, even if it doesn't work it should still serve as a guide regarding how to do what you want.