Page 1 of 1

Select query serialized field

Posted: Wed Dec 15, 2010 2:15 pm
by Kane10
Hello,

I have a field in a table with states abbr. that have been serialized example,
s:155:"AK,AL,AR,AZ,CA,CO,CT,DC,DE,FL,GA,HI,IA,ID,IL,IN,KS,KY,LA,MA,MD,ME,MI,MN,MO,MS,MT,NC,ND,NE,NH,NJ,NM,NV,NY,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VA,VI,VT,WA,WI,WV,WY";

The above example is one with all states, but some just have one state or a few it depends on how many the client selected.
How would I write a select statement to pull all the records that have "CA" in the list for example.

I tried this: select * from locations where 1 and states REGEXP '["]CA["]' but this only returns records with just CA
s:2:"CA";

Im looking for a way to grab those and also the records with multiple states if CA is in it.

Any help would be greatly appreciated.
Thanks in advance

Re: Select query serialized field

Posted: Wed Dec 15, 2010 2:29 pm
by Jonah Bron
You can select them like this:

Code: Select all

SELECT * FROM locations WHERE states LIKE "%CA%"
But if you need select based on that, it would be better not to serialize that field, and instead create a many-to-many relationship between locations and states (this would require two extra tables).

Re: Select query serialized field

Posted: Wed Dec 15, 2010 2:56 pm
by VladSun
Don't serialize, use SQL tables. Normalize!

Re: Select query serialized field

Posted: Wed Dec 15, 2010 5:04 pm
by califdon
I agree with Vladsun. Serializing (if that's the correct term for this) breaks First Normal Form.

[Edit:] I have just looked up serializing and my interpretation is that it is inappropriate in this use. This article discusses where it is appropriate to use and what the consequences are, primarily the very problem you are having: searching. It violates the rules for relational databases.
http://www.mysqlperformanceblog.com/201 ... -database/

Re: Select query serialized field

Posted: Wed Dec 15, 2010 5:35 pm
by VladSun
Yes, denormalizing is used in some cases - but one still has to have a normalized DB. Then one can easily build denormalized tables/view from it. That's what data warehouse is.