CODEWITHSUNDEEP
phpmysqlregexmysqli
WomenInTech
WomenInTech

Reputation: 1141

Strict search using mysql/php

I am pretty much stuck for hours on this and Would appreciate if someone could help .

I am doing a mysql search on data :

Row Id   List Data
1   3,2,7,11
2   1,27
3   2,17,44
4   19,77

My problem is I want to do a strict search .

Lets say i want to search for rows which have '7' in above data .

If i do :

select id from table where list_data like '%7%'

will obviously return all 4 rows

I want to get only 1st row as my result .

How can i achieve that ?

Thanks in advance :)

Upvotes: 0

Views: 232

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157893

Your table format is wrong. It have to be

id Data
1  3
1  2
1  7
1  11
2  1
2  27
3  2
3  17
3  44
4  19
4  77

and query

select id from table where data = 7

Upvotes: 1

Alma Do
Alma Do

Reputation: 37365

You can use FIND_IN_SET function (there is a sample in another answer) or regex instead:

SELECT id FROM `table` WHERE list_data REGEXP '(^|,)7($|,)'

however, it seems that you're trying to put several data links to one field. If so, then avoid that - because it violates relation DB principles. Use linking table instead and then your query will be rewritten using SQL JOIN operator.

Upvotes: 2

juergen d
juergen d

Reputation: 204766

Use FIND_IN_SET()

select id from table 
where find_in_set(7, list_data) > 0

BTW you should normalize your data to avoid such problems in the future. Please store always only 1 value in a column, not multiple.

Upvotes: 4

Related Questions