A variety MySQL selection in php

[rajeesh_rsn]

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

[MeSampath]

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.

[mwasif]

You need to use FIND_IN_SET() in this way

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

[smantscheff]

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.

[smantscheff]

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$)"