Select query serialized field

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Kane10
Forum Newbie
Posts: 1
Joined: Wed Dec 15, 2010 2:11 pm

Select query serialized field

Post 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
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Select query serialized field

Post 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).
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Select query serialized field

Post by VladSun »

Don't serialize, use SQL tables. Normalize!
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Select query serialized field

Post 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/
Last edited by califdon on Wed Dec 15, 2010 5:11 pm, edited 2 times in total.
Reason: Added comments.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Select query serialized field

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply