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

Question

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:

enter image description here

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;

A many-many relationship in a database usually indicates there's something wrong with the design. many-many relationships should always be resolved by introducing a third element between the endpoints of the relationship so it becomes one-many-one (or many-one-many). In your design an event can have many styles, and a style can be used in many events, so that means there needs to be an extra entity that exists between events and styles to resolve the many-many relationship. — GordonM
– GordonM, Commented Sep 29, 2011 at 7:16
Hmmm, so is there no way around this with the current state of my database design? I was told that this is the way I should do it and since I'm a beginner I didn't question anybody's authority :) — pufAmuf
– pufAmuf, Commented Sep 29, 2011 at 7:20
You should show respect to people who are more experienced than you, but that doesn't mean you shouldn't ever question them! Even experienced people are wrong sometimes. closer examination of your query suggests there's already an event-styles relationship resolution table in there to resolve the many-many relationship between events and styles, am I right? Might I suggest you post your schema (the description of your DB tables) into your question as well? — GordonM
– GordonM, Commented Sep 29, 2011 at 7:32
Hello Gordon, I did a little visual chart to make it easy to read :)) i.imgur.com/e54pD.jpg - and I also uploaded my sql table script: jsfiddle.net/pufamuf/yt3rK Thanks so much :) — pufAmuf
– pufAmuf, Commented Sep 29, 2011 at 10:52

GordonM · Accepted Answer · 2011-09-30 06:12:05Z

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.

Thank you so much for your hard work Gordon!!!! I will now study this and hopefully be able to put something together! Thank you again!! :)))

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related