Hi friends,

I had a database table as follows ...

Id | Film Name | Director | Cast |
----------------------------------------
12 | Dhoom | Name | 123,128,125,458,12
12 | Dhoom 2 | Name | 129,158,147,455
------------------------------------------------

I need search by cast column
For eg :
If I select :- select from table where cast like "%12%" Then it will select 2 rows even if "12" is in only one row ....

Please help me how we can avoid this


Thanks in advance
Thankyou for your valuable time
Rajeesh

Hi,

If you want to get only one row with value "12" then modify the query with

select from table where cast like "12"

or use "cast=12"

while using "%" on both sides in like operator it will list out all the matching entries which containing the string used in like condition.

Thank you.

You need to use FIND_IN_SET() in this way

select from table where FIND_IN_SET('12', cast);

It might be a better idea to link the cast to the films in a separate link table with foreign keys to speed up queries and to avoid data corruption.

Or, if you stay with a text field to store the cast, use a regex:

select * from table where cast = "12" or cast rlike "(^12,|,12,|,12$)"
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.