Results 1 to 3 of 3
Nov 20, 2009, 11:08 #1
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:
$profile = "SELECT * FROM profiles WHERE area LIKE '%".$areaid."%' AND serv LIKE '%".$servid."%' AND active='2'";
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.
Nov 20, 2009, 11:12 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 49 Post(s)
- 1 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
Nov 20, 2009, 11:13 #3
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.