How to find comma separated value

Question

Hello, I have a string that is stored in my database, separated by a comma for example: (New South Wales, Queensland, etc. etc.). I know my problem, when I try to find Queensland, I can not get the result but when I try to find new south wales i get a record.

But I want to get the result when I try to find a queen, etc. I am new to php, so please help ...

Answer 1

Short term solution

Use the FIND_IN_SET function :

WHERE FIND_IN_SET('Queensland', csv_column) 

... because using LIKE with wildcards at both ends is risky, depending on how many / small matches (and also provides table scans). LIKE performance with wildcards on both sides is on par with REGEXP - which means it's bad.

Long term solution

Do not store values ​​separated by commas - use the correct many-to-many relationship with three tables:

Things

• thing_id (primary key)

Australian states

• State_id (primary key)
• State_name

Things_to_Auz_States

• thing_id (primary key, foreign key for table THINGS )
• State_id (primary key, foreign key for table AUSTRALIAN_STATES )

You will need JOINs to retrieve data from three tables, but if you want to find out how many of them are related to a particular state or two specific states, this is the correct model.

Answer 2

Not exactly what you asked for, but just to be complete: you will have many problems if you do not change your approach.

The right way:

 TableOne -------- ThingID TableTwo -------- ThingID Province 

Then your database query will look like this:

 SELECT fields FROM TableOne WHERE ThingID IN (SELECT ThingID from TableTwo WHERE Province = 'Queensland') 

And what do you want when they search for "Australia"? Returning to both Western Australia and South Australia?

Answer 3

Using REGEXP

 $result = mysql_query("SELECT * FROM table WHERE column REGEXP $your_search_string");