Waves - left, right, both

[ripcurlksm]

I have a database of famous ocean waves/beaches. Some waves break "right", some waves break "left" and some waves break "both" (left & right).

I have my database setup like this where

left = 1
right = 2
both = 3

name			location	wave_direction
---------------------------------------------------------
Pipeline		HI		3
Snapper Rocks		AU		2
Teahupoo		PF		1

My difficulty here is in the search results for the "both" wave.
1) If a user searches for a left(1) wave, it should return results for all 1 & 3 results.

2) If a user searches for a right(2) wave, it should return results for all 2 & 3 results.

What is the best way to hand this "both" wildcard so technically it is included on any search?

[Tybe-O]

If you split up the wave part in two columns of type CHAR(1) with value 0/1 (my MySQL way of a boolean) you can query:

left: WHERE wave_left="1"
right: WHERE wave_right="1"
both: WHERE wave_left="1" AND wave_right="1"

(If the wave options stay within these three this approach is the easiest, else you should start thinking about extra tables, JOINS, etc.)

[ripcurlksm]

I just tried this and i think its workin

if ($_POST['direction'] == 1) {
  $query .= " AND (w_direction LIKE '1' OR w_direction LIKE '3')";
}
if ($_POST['direction'] == 2) {
  $query .= " AND (w_direction LIKE '2' OR w_direction LIKE '3')";
}
if ($_POST['direction'] == 3) {
  $query .= " AND w_direction LIKE '3'";
} 

[Tybe-O]

Hmm, yes, that will get you going. Some advice:

1) Use '=' instead of LIKE. It's faster which will become noticable as your db grows.
2) Consider the separate columns I mentioned because it's much more straightforward. If you're going to add more and more functionality (and thereby more queries) it just makes more sense. Keeping it the way it is now means you have to consider the exact meaning of the value 1,2 and 3 and apply that to every query...

[mudelta]

Since the 1,2,3 part is a bitmask, you can use the bitwise and operator:

SELECT * FROM TableName WHERE wave_direction & 1=1;

returns all the waves that break left

SELECT * FROM TableName WHERE wave_direction & 2=2;

returns all the waves that break right

SELECT * FROM TableName WHERE wave_direction & 3=3;

returns all the waves that break both

So in php:

$waveDirection=//put here the direction the user searches
$query=sprintf("SELECT * FROM TableName WHERE wave_direction & %s = %s",$waveDirection,$waveDirection);

//and then execute query 

[longneck]

keep in mind that bitmasks can't be accelerated by indexes.