Page 1 of 1

Returing The exact Row

Posted: Mon Jun 26, 2006 7:23 am
by namitjung
Hi All,

I am trying to return the exact matching row from mysql in php.The column type is varchar so i use wild char characters to return the row but it didn't work.I also tried to use IN statement in my sql but it also didn't work.

I would like to explain the problem scenario as follows:

Code: Select all

TableName: users

userid     username          matchtypes
1             Bob                   1,4,5,13
2             Jessica              3,21,45,67
3             Ken                   13,42,12
4             Stephe              103,24,203
5             Andrew             3,12,45
I am creating dating site,so whenever a match is found it should send email to the respective user. In matchtypes column, matchid are inserted as text.i got problem to extract the exact match records.let's say we have to extract those records which should match with 3. For that I used following sql statement

Code: Select all

select * from users where matchtypes like '%3%'
but sql returns all records since there is 3 in every row. But i want it to extract only two records (Jessica and andrew) since they have exact match .

Is thery any sql statement which i can use to extract the exact records.

Thanx in advance

Posted: Mon Jun 26, 2006 7:48 am
by namitjung
can i use split function after returning records and match it with the provided with separtely? but there will be a very long loop.

Posted: Mon Jun 26, 2006 7:49 am
by Weirdan
simple way would be to force the field to have leading and trailing commas:

Code: Select all

TableName: users

userid     username          matchtypes
1             Bob            ,1,4,5,13,
2             Jessica        ,3,21,45,67,
3             Ken            ,13,42,12,
4             Stephe         ,103,24,203,
5             Andrew         ,3,12,45,
Then you would query it like this:

Code: Select all

select * from users where matchtypes like '%,3,%'

Posted: Mon Jun 26, 2006 7:55 am
by Verminox
Edit: Nevermind, I made a bad attempt at MySQL REGEXPs :(

Posted: Mon Jun 26, 2006 7:58 am
by namitjung
Thank you very much

My problem is solved

Thank you again. :lol:

Posted: Mon Jun 26, 2006 8:00 am
by Weirdan
Alternate (and 'proper') way is to use additional table:

Code: Select all

create table `people_matchtypes` (`userid`, `matchtype`);