CODEWITHSUNDEEP
mysql
imran
imran

Reputation: 21

How to search for a comma separated value

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...

Upvotes: 2

Views: 2468

Answers (3)

egrunin
egrunin

Reputation: 25053

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?

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332571

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.

Upvotes: 7

Ruel
Ruel

Reputation: 15780

By using REGEXP

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

Upvotes: -2

Related Questions