Splitting strings in mysql

Question

Ok so I have looked up several ways of getting this done, and none of them work for me, let me give you a few details on why the methods I have tried don't work.

I have tried SUBSTRING_INDEX and as most of us know it only "splits" based on the number of delimiters there are in the string, and well it isn't the same amount for all of the rows, so that is a no go, I also tried the method found here http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

Again I believe that it goes based on the number of delimiters in your query.

I have searched and searched for a way to get this done, and I just can't find a way to get it working. I am still a bit of a newbie to PHP so this could be a simple fix and I am just overlooking it somehow, but that I am not sure of.

Here is what I am working with;

This is the sql query

$sql = "SELECT * FROM offers WHERE type='$type' and country='$membercountry' LIMIT $start, $limit";
    $result = mysql_query($sql);

country is the column that has my comma delimited string. Some rows simply just have something like US while others have US,AU,CA,GB

The reason I am needing to split these is because the page that actually displays the offers checks to see if the users country matches the offers country, and it is obviously going to not match when it is reading it as a whole long string, Which means those offers won't show up for the user.

I talked to a friend of mine and he said to use explode(), but I am pretty sure you cannot use explode() in a sql query.

I know I probably haven't explained this as well as I could have, I have been working on this project all night so I am a bit tired.

Also as a note, I know that I could just make it to where there are multiple rows for each offer supplying the different country, but some of these offers allow all countries to do them, that would mean that for 1 offer I would have like 243 rows, and that would make me feel like I was just being sloppy!

If anyone could give me a push in the right direction I would be greatly appreciative!

Thanks, Casey

The "right direction" would be to normalise your data structure: rather than storing lists of this sort in a delimited string, create a separate table that associates `(offer,country)` pairs. More detail in [my answer to a similar question](http://stackoverflow.com/a/10881627/623041). — eggyal, Jun 28 '12 at 10:49

score 3 · Answer 1 · answered Jun 28 '12 at 10:55

Bit of an odd/bad data design: multiple countries in a column named country. That aside, you could maybe do something with FIND_IN_SET().

mysql> SELECT * FROM offers WHERE FIND_IN_SET('CA', country);

Performance wise .. it's not going to be great but I hope you can select on other fields using a good index.

score 1 · Accepted Answer · answered Jun 28 '12 at 10:49

1

Something like this may work out:

$sql = "SELECT * FROM offers WHERE type='$type' and country LIKE '%$membercountry%' LIMIT $start, $limit";

answered Jun 28 '12 at 10:49

Donatas Olsevičius

1,350
8
13

Or this to be sure of an exact match: `$sql = "SELECT * FROM offers WHERE type='$type' and CONCAT(',', country, ',') LIKE '%,$membercountry,%' LIMIT $start, $limit";` – Matthijs Jun 28 '12 at 10:54
@Matthijs .. and what if country would be at the beginning of the string, where there is no leading comma? :) – geertjanvdk Jun 28 '12 at 10:56
@geertjanvdk That's why I added a CONCAT, to pre- and append a comma. – Matthijs Jun 28 '12 at 10:58
@Matthijs, if all countries have just two symbols, my query is fine. Otherwise he should use REGEX. – Donatas Olsevičius Jun 28 '12 at 10:59
@Matthijs, than you forgot to remove commas from '%,$membercountry,%'. – Donatas Olsevičius Jun 28 '12 at 11:00
@Matthijs My bad! I missed the comma's in the LIKE. – geertjanvdk Jun 28 '12 at 11:00

Splitting strings in mysql

2 Answers2