Returing The exact Row

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
namitjung
Forum Commoner
Posts: 42
Joined: Mon Jun 20, 2005 3:17 am

Returing The exact Row

Post 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
namitjung
Forum Commoner
Posts: 42
Joined: Mon Jun 20, 2005 3:17 am

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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,%'
User avatar
Verminox
Forum Contributor
Posts: 101
Joined: Sun May 07, 2006 5:19 am

Post by Verminox »

Edit: Nevermind, I made a bad attempt at MySQL REGEXPs :(
Last edited by Verminox on Mon Jun 26, 2006 8:00 am, edited 2 times in total.
namitjung
Forum Commoner
Posts: 42
Joined: Mon Jun 20, 2005 3:17 am

Post by namitjung »

Thank you very much

My problem is solved

Thank you again. :lol:
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Alternate (and 'proper') way is to use additional table:

Code: Select all

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