How to search for a comma separated value

Question

Hello i have a string that is stored in my database separated by comma eg: (new south wales,Queensland,etc,etc) Know my problem is when i try to search Queensland i am not able to get the result but when i try to search for new south wales i get the record.

But i want to get the result when i try to search for queen or etc. I am new to php so please help...

Answer 1

Not really what you were asking, but just to be complete: you're going to have a lot of trouble unless you change your approach.

The correct way:

TableOne
--------
ThingID


TableTwo
--------
ThingID
Province

Then your database query becomes:

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

And what do you want to have happen when they search for "Australia"? Get back both Western Australia and South Australia?

Answer 2

By using REGEXP

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

Answer 3

Short Term Solution

Use the FIND_IN_SET function:

WHERE FIND_IN_SET('Queensland', csv_column)

...because using LIKE with wildcards on either end is risky, depending on how much/little matches (and it also ensures a table scan). Performance of LIKE with wildcards on either side is on par with REGEXP--that means bad.

Long Term Solution

Don't store comma separated values -- use a proper many-to-many relationship, involving 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 to THINGS table)
• State_id (primary key, foreign key to AUSTRALIAN_STATES table)

You'll need JOINs to get data out of the three tables, but if you want to know things like how many are associated to a particular state, or two particular states, it's the proper model.