Select from mysql database where columns contains PHP

Question

I have a database and some of the columns contain things like CA, GB etc, although some contain multiple country codes like

US+GB+CA+AU

I'm just wondering what kind of query I would do that would return that row when I'm searching for just CA or just GB, and no necessarily the whole package US+GB+CA+AU

Encase that's a little confusing, basically I just need to return that row based on a search for just CA or just GB etc.

Thanks

Why do some of your columns contain multiple data? I'd advise you to look at that and try and normalise it. It will cause you no end of problems... — liquorvicar
– liquorvicar, Commented Mar 21, 2012 at 12:27
it just the way the data was being stored in a csv before i imported it, i may very well just normalise it. — Belgin Fish
– Belgin Fish, Commented Mar 21, 2012 at 14:39
In that case I would normalise your schema now. It's easy enough to split the concatenated strings in the csv before you import the data. — liquorvicar
– liquorvicar, Commented Mar 21, 2012 at 14:42

Michael Berkowski · Accepted Answer · 2012-03-21 12:40:50Z

4

Use FIND_IN_SET(), but you'll first need to replace + with , since it expects a comma-separated string. Even without the REPLACE(), this is will not make use of any index on the countrycodes column.

SELECT * FROM tbl 
WHERE FIND_IN_SET('AU', REPLACE(countrycodes, '+', ',')) > 0

The proper long term solution, however, is to change your database structure to normalize these country codes into a table that contains only two columns - a country code, and the id of the associated row from the table you're attempting to query now. You can then index the column appropriately to improve performance (possibly drastically improve it).

edited Mar 21, 2012 at 12:40

answered Mar 21, 2012 at 12:32

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Travesty3 Over a year ago

I believe according to the MySQL doc, the correct syntax for REPLACE would be REPLACE(countrycodes, '+', ',').

Michael Berkowski Over a year ago

@Travesty3 Yes, you're correct thanks. I just did a string replacement in a different language with params in the other order...

Reinard · Accepted Answer · 2012-03-21 12:30:29Z

3

I would recommend to normalise it like liquorvicar said.

but using SELECT ... WHERE countrycode LIKE '%GB%' would work. http://w3schools.com/sql/sql_like.asp

answered Mar 21, 2012 at 12:30

Reinard

3,65410 gold badges45 silver badges62 bronze badges

Comments

Travesty3 · Accepted Answer · 2012-03-21 22:26:40Z

2

It's not a good solution, but you can use LIKE for your query:

SELECT * FROM `table` WHERE `field` LIKE '%+CA+%' OR `field` LIKE 'CA+%' OR `field` LIKE '%+CA' OR `field` = 'CA'

Last two checks for firs and last values.

edited Mar 21, 2012 at 22:26

Travesty3

14.5k7 gold badges64 silver badges103 bronze badges

answered Mar 21, 2012 at 12:32

Narek

3,8235 gold badges45 silver badges59 bronze badges

5 Comments

Carrie Kendall Over a year ago

that's not necessary. % is a substitute for zero or more characters

Narek Over a year ago

@CarrieKendall but we have LIKE '%+CA+%' not LIKE '%CA%'. First value is not +

Travesty3 Over a year ago

@CarrieKendall: I think the idea is to be extra safe in case you get a value like CCA, which you don't want to be returned.

Carrie Kendall Over a year ago

but those ONLY return the records that have multiple country codes. Read OP's first sentence.

Travesty3 Over a year ago

@CarrieKendall: A good point. You should add OR field = 'CA' to the WHERE clause. @MarcusAdams: Agreed, which is why I was referring to it as "extra safe" :-)

Collectives™ on Stack Overflow

Select from mysql database where columns contains PHP

3 Answers 3

2 Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related