SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru ripcurlksm's Avatar
    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?

  2. #2
    SitePoint Member Tybe-O's Avatar
    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.)

  3. #3
    SitePoint Guru ripcurlksm's Avatar
    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'";


  4. #4
    SitePoint Member Tybe-O's Avatar
    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...

  5. #5
    SitePoint Enthusiast
    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;
    returns all the waves that break left

    Code SQL:
    SELECT * FROM TableName WHERE wave_direction & 2=2;
    returns all the waves that break right

    Code SQL:
    SELECT * FROM TableName WHERE wave_direction & 3=3;
    returns all the waves that break both

    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 

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •