Professional Web Applications Themes

mysql Pattern Matching - PHP Development

i'm doin this offlist -----Original Message----- From: Ralph Guzman [mailto:ralph213sbcglobal.net] Sent: Thursday, September 04, 2003 10:23 PM To: PHP General Mailing List Subject: [PHP] mysql Pattern Matching I know this question is best for the mySQL mailing list, but I am unable to subscribe to their list at this moment so perhaps somebody here can help me out. I have a table with a field where amenities are listed together using a comma delimiter like: pool,spa,fitness-center To search this table I use a query that looks something like this: SELECT * FROM properties WHERE amenities LIKE '%pool%' AND amenities LIKE ...

  1. #1

    Default RE: [PHP] mysql Pattern Matching

    i'm doin this offlist

    -----Original Message-----
    From: Ralph Guzman [mailto:ralph213sbcglobal.net]
    Sent: Thursday, September 04, 2003 10:23 PM
    To: PHP General Mailing List
    Subject: [PHP] mysql Pattern Matching


    I know this question is best for the mySQL mailing list, but I am unable
    to subscribe to their list at this moment so perhaps somebody here can
    help me out.

    I have a table with a field where amenities are listed together using a
    comma delimiter like: pool,spa,fitness-center

    To search this table I use a query that looks something like this:

    SELECT * FROM properties WHERE amenities LIKE '%pool%' AND amenities
    LIKE '%spa%' AND amenities LIKE '%fitness-center%'

    This works, however let's say the user chooses to search for more
    amenities. This means the query would need multiple 'AND amenities LIKE
    '%____%' statements.

    Is there a better way to write this?

    I tried the following, but it did not work:

    AND amenities LIKE ('%pool%','%spa%','%fitness-center%')

    Any suggestions?

    --
    PHP General Mailing List (http://www.php.net/)
    To unsubscribe, visit: http://www.php.net/unsub.php
    Electroteque Guest

  2. #2

    Default mysql Pattern Matching

    I know this question is best for the mySQL mailing list, but I am unable
    to subscribe to their list at this moment so perhaps somebody here can
    help me out.

    I have a table with a field where amenities are listed together using a
    comma delimiter like: pool,spa,fitness-center

    To search this table I use a query that looks something like this:

    SELECT * FROM properties WHERE amenities LIKE '%pool%' AND amenities
    LIKE '%spa%' AND amenities LIKE '%fitness-center%'

    This works, however let's say the user chooses to search for more
    amenities. This means the query would need multiple 'AND amenities LIKE
    '%____%' statements.

    Is there a better way to write this?

    I tried the following, but it did not work:

    AND amenities LIKE ('%pool%','%spa%','%fitness-center%')

    Any suggestions?
    Ralph Guzman Guest

  3. #3

    Default Re: [PHP] mysql Pattern Matching

    From: "Ralph Guzman" <ralph213sbcglobal.net>

    > I know this question is best for the mySQL mailing list, but I am unable
    > to subscribe to their list at this moment so perhaps somebody here can
    > help me out.
    >
    > I have a table with a field where amenities are listed together using a
    > comma delimiter like: pool,spa,fitness-center
    >
    > To search this table I use a query that looks something like this:
    >
    > SELECT * FROM properties WHERE amenities LIKE '%pool%' AND amenities
    > LIKE '%spa%' AND amenities LIKE '%fitness-center%'
    >
    > This works, however let's say the user chooses to search for more
    > amenities. This means the query would need multiple 'AND amenities LIKE
    > '%____%' statements.
    >
    > Is there a better way to write this?
    You could use FIND_IN_SET().

    SELECT * FROM properties WHERE FIND_IN_SET('pool',amenities) AND
    FIND_IN_SET('spa',amenities) AND ...

    It'll still grow as the number of amentities grow, but there's no getting
    around that.

    ---John Holmes...
    Cpt John W. Holmes Guest

  4. #4

    Default Re: [PHP] mysql Pattern Matching

    > From: "Ralph Guzman" <ralph213sbcglobal.net>
    >
    >
    >> I know this question is best for the mySQL mailing list, but I am unable
    >> to subscribe to their list at this moment so perhaps somebody here can
    >> help me out.
    >>
    >> I have a table with a field where amenities are listed together using a
    >> comma delimiter like: pool,spa,fitness-center
    Mmm.. I'm jumping in late, and without knowledge of your app, but why not
    normalise that column to its own table:

    recid masterrec amenity
    1 22 pool
    2 22 spa
    3 12 fitness-center

    And run a unique key across all three columns (or drop recid and use two
    columns).

    select masterrec from amenities where amenity = ....

    The query will still grow in length, but it may be easier to work with..
    *shrug*
    Duncan Hill Guest

Similar Threads

  1. pattern matching
    By Tad McClellan in forum PERL Beginners
    Replies: 30
    Last Post: June 23rd, 07:42 PM
  2. Pattern matching for xx-xx-xx string
    By jeff@nospam.com in forum PHP Development
    Replies: 7
    Last Post: January 19th, 06:38 PM
  3. Pattern matching username
    By perl@swanmail.com in forum PERL Beginners
    Replies: 1
    Last Post: September 30th, 05:37 PM
  4. matching the pattern (strings)
    By MJS in forum PERL Beginners
    Replies: 0
    Last Post: September 28th, 07:04 PM
  5. [PHP] mysql Pattern Matching
    By Cpt John W. Holmes in forum PHP Development
    Replies: 1
    Last Post: September 5th, 06:42 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139