The Four Seasons: Querying with Multiple Checkbox Values

wiswif · September 12th, 2011, 05:48 PM

Hello wrox,
Basically, I have a database of events, each of which bears a collection of details regarding that particular event and by which I've managed to develop a means for searching. I'd like to add a "season" designation for each event, and then enable users to specify for which season(s) they'd like to see events.

My instinct was to add a VARCHAR field for each particular event, 'event_season', and populate it with the applicable seasons, separated by commas (I'm opting for this route because a few events occur in multiple seasons), so a given event may have an 'event_season' value of 'spring,fall'.

On the search end, then, I was going to present users a series of checkboxes, one for each season, collect those values, search whether any of them are IN 'event_season', and display matches.

Problem is, as I'm sitting down to write the code, I'm having trouble coming up with a good approach to deal with the multiple checkboxes (namely, the possibility that more than one is checked)... again, my instinct is to name them for their respective season, check individually if each one is set, and then attach each one that is to an OR clause of the query, but for some reason I have an uneasy feeling (either that there is a simpler way or that it flat out won't work). Normally I'd just jump in and try a few things myself, but with the number of events I've got thus far and the fact that I'm changing table structure, I figured I might as well ask for an expert opinion.

My questions, then, are really two-fold:
1) Is my 'event_season' field structure logical, or is there a better way?
2) Is my query technique sound / efficient, or is there a better way?

Any input you might offer would be very much appreciated; Wrox has taught me so much through the books and continues to be extremely helpful on these forums, for which I am deeply grateful.

Thanks kindly,
wiswif

UseLess · September 15th, 2011, 09:28 AM

Greetings,

The HTML

Code:

<input type="checkbox" name="seasons[]" value="spring" />
<input type="checkbox" name="seasons[]" value="summer" />
<input type="checkbox" name="seasons[]" value="autumn" />
<input type="checkbox" name="seasons[]" value="winter" />

Our Autumn will be your Fall ;)

The PHP:

Code:

// Grab the seasons array from the form
$seasons = ( (isset($_POST['seasons']) && sizeof($_POST['seasons'])) ? $_POST['seasons'] : array());

// Setup the season SQL
$sql_season = '';

// Does the array contain anything, if no seasons where checked then the array will be empty
if( sizeof($seasons) )
{
	// Loop over the seasons
	foreach($seasons as $key => $value)
	{
		// add each season to the sql string
		$sql_season .= $value . ', ';
	}
	// remove the last , and space
	$sql_season = substr($sql_season, -2);
}

you now have a string with the selected seasons seperated by comas.

Note no checking was done, I'd suggest you do some checking of the values passed back via the form.

wiswif · September 15th, 2011, 12:03 PM

Thank you UseLess... definitely more efficient and I wouldn't have come up with it on my own. I may have a follow-up question once I get around to trying it, but thanks again for pointing me in the right direction.

Thanks,
wiswif

wiswif · September 15th, 2011, 06:13 PM

Just in case you're wondering, this is how I edited it (...and in actuality, I post this just because I'm curious if you had a better / more efficient way, even though this seems to be working like a charm...):

Code:

        // The "Narrow by season?" checkbox that brings up the seasons checkboxes
	if (isset($_POST['seasonal']) and ($_POST['seasonal'] == "on")) {
		// Grab the seasons array from the form
		$seasons = ((isset($_POST['seasons']) && sizeof($_POST['seasons'])) ? $_POST['seasons'] : array());

		// Setup the season SQL
		$sql_season = "AND ";

		// Does the array contain anything, if no seasons where checked then the array will be empty
		if (sizeof($seasons)) {
			if (sizeof($seasons) == 1) {
				// Loop over the seasons
				foreach($seasons as $key => $value) {
					$sql_season .= "(('" . $value . "' IN (`event_seasons`)) OR (`event_seasons` IS NULL))";
				}
			} else {
				// Loop over the seasons
				foreach($seasons as $key => $value) {
					// add each season to the sql string
					$sql_season .= "(('" . $value . "' IN (`event_seasons`)) OR";
				}
				// remove the last OR and space
				$sql_season = substr($sql_season, -3);
				$sql_season .= ") OR (`event_seasons` IS NULL))";
			}
		$query .= $sql_season;
		}
	}

...one thing I realize I probably DON'T need is the "if (sizeof($seasons) == 1)" bit; wrote that before I tacked on the final

Code:

$sql_season .= ") OR (`event_seasons` IS NULL))";'

And may go back to change it later. Still, with storing the `event_seasons` field as "spring,fall"--yes, I went back to fall

--this suits my purposes; I'm just always curious to hear what I did "wrong".

At any rate, thanks for your help on this; like I said, it would have taken me MANY of my son's naps to figure that out on my own.

Gratefully,
wiswif

wiswif · September 17th, 2011, 06:38 PM

...and after discovering that queries with multiple seasons weren't working, here's what I ended up with:

Code:

		// Does the array contain anything, if no seasons where checked then the array will be empty
		if (sizeof($seasons)) {
			if (sizeof($seasons) == 1) {
				// Loop over the seasons
				foreach($seasons as $key => $value) {
					$sql_season .= "(('" . $value . "' IN (`event_seasons`)) OR (`event_seasons` IS NULL))";
				}
			} else {
				$sql_season .= "(";
				// Loop over the seasons
				foreach($seasons as $key => $value) {
					// add each season to the sql string
					$sql_season .= "('" . $value . "' IN (`event_seasons`)) OR ";
				}
				// remove the last OR and spaces
				$sql_season = substr($sql_season, 0, -4);
				$sql_season .= ") OR (`event_seasons` IS NULL)";
			}
		$query .= $sql_season;
		}

...note that the final thing I changed before restoring function was adding the "0" starting point to the substr() function...

Thanks,
wiswif

UseLess · September 18th, 2011, 12:31 PM

Greetings,

Ignore this, didn't work created a quick and dirty db to see if it worked and it didn't so removed post.

How is your table laid out?

wiswif · September 19th, 2011, 02:41 PM

Hi UseLess,
You seem as invested in this as I at this point, so I thought I'd give you an update:

After my last code, which allowed me to search with multiple seasons selected, I discovered that I wasn't hitting events that had multiple seasons STORED in `event_seasons` (again, in the form 'spring,summer,fall', which maybe isn't the most efficient). However, borrowing from your advice, I decided to use a basic regular expression, and came up with this:

Code:

	if (isset($_POST['seasonal']) and ($_POST['seasonal'] == "on")) {
		// Grab the seasons array from the form
		$seasons = ((isset($_POST['seasons']) && sizeof($_POST['seasons'])) ? $_POST['seasons'] : array());

		// Setup the season SQL
		$sql_season = " AND ";

		// Does the array contain anything, if no seasons where checked then the array will be empty
		if (sizeof($seasons)) {
			$sql_season .= "(";
			// Loop over the seasons
			foreach($seasons as $key => $value) {
				// add each season to the sql string
				$sql_season .= "`event_seasons` REGEXP '" . $value . "') OR (";
			}
			// remove the last OR and spaces
			$sql_season = substr($sql_season, 0, -5);
			$sql_season .= " OR (`event_seasons` IS NULL)";
		$query .= $sql_season;
		}
	}

...again, probably not as efficient as it could be, but--from what I can tell--it is working the way I want it.

Once more, thanks for all your help, and if I discover any further problems you'll be the first to know.

Gratefully,
wiswif