SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict TegSkywalker's Avatar
    Join Date
    Nov 2001
    Location
    USA
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    LIKE search to differentiate between 1, 10, 100

    Here is my dilemma. I have a MySQL LIKE search for profiles in an area but the profiles themselves can have multiple areas stored. My problem is that areas of 1, 10, and 100 are showing up as the same but I want it to limit to only the 1, 10, or 100 depending on my query. Same thing goes for area of 2 not showing area 12, 20, or 200 because it has 2 in it. Pretty much want to make the numbers be unique and I dunno if its a code deal or I have to change my table collation.

    For example, this is what I have in my PHP. I also have a serv value but I am assuming the code will be the same since they are numeric and each profile has multiple values:

    PHP Code:
    $profile "SELECT * FROM profiles WHERE area LIKE '%".$areaid."%' AND serv LIKE '%".$servid."%' AND active='2'"
    Where a profile may have these saved in their areas: "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,100, 101, 102"

    I went on the MySQL page and tried '".$areaid."%' and '".$areaid."_' and '".$areaid."\_' but I either get no results or all of them.

    Any tips would be great.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    use FIND_IN_SET() but bear in mind that ~no~ query which searches within a multi-valued column will scale, i.e. the more rows you have, the slower the query

    consider adding a profile_area table, with one row for each area that a profile has

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Comma separated values in a column? Sooner or later it'll get you into trouble
    What you should do is normalise your database. In this case, create a new table (profileareas) with two columns: profileid and area.
    Then, to get the profiles you want, all you have to do is join the two tables and select the areas you want.


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
  •