SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
Thread: Waves - left, right, both
-
Mar 16, 2008, 23:20 #1
- Join Date
- Aug 2004
- Location
- San Clemente, CA
- Posts
- 859
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Waves - left, right, both
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
Code: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?
-
Mar 17, 2008, 04:55 #2
- Join Date
- Mar 2008
- Location
- NL, Rotterdam
- Posts
- 24
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.)
-
Mar 17, 2008, 05:10 #3
- Join Date
- Aug 2004
- Location
- San Clemente, CA
- Posts
- 859
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I just tried this and i think its workin
PHP Code: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'";
}
-
Mar 17, 2008, 07:02 #4
- Join Date
- Mar 2008
- Location
- NL, Rotterdam
- Posts
- 24
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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...
-
Mar 17, 2008, 07:29 #5
- Join Date
- Jan 2007
- Posts
- 48
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Since the 1,2,3 part is a bitmask, you can use the bitwise and operator:
Code SQL:SELECT * FROM TableName WHERE wave_direction & 1=1;
Code SQL:SELECT * FROM TableName WHERE wave_direction & 2=2;
Code SQL:SELECT * FROM TableName WHERE wave_direction & 3=3;
So in php:
PHP Code:$waveDirection=//put here the direction the user searches
$query=sprintf("SELECT * FROM TableName WHERE wave_direction & %s = %s",$waveDirection,$waveDirection);
//and then execute query
-
Mar 17, 2008, 07:49 #6
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
keep in mind that bitmasks can't be accelerated by indexes.
Bookmarks