List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:September 14 2007 1:13pm
Subject:Re: adress stored as an array
View as plain text  
Ross Hulford wrote:
> Hi,
> 
> I have an address stored as a serialized array
> 
> address[0] = "my street";
> address[1] = "Edinburgh";
> address[2] = "Scotland";
> 
> This is what I have. I want if it is possible to extract all the UNIQUE 
> towns from my database.
> 
> This is what the on of the entires looks like in my database.
> 
> a:7:{s:8:"building";s:0:"";s:7:"company";s:0:"";s:5:"line1";s:12:"Leyland 
>
> Road";s:5:"line2";s:0:"";s:5:"line3";s:0:"";s:4:"town";s:8:"Bathgate";s:6:"county";s:12:"West
> 
> Lothian";}

You are missing the point of a database by denormalizing your data in 
this way, and making it very hard to work with.  You should read up on 
first normal form and re-normalize your data.  Not because I'm a MySQL 
fan, but one of the most understandable explanations I've seen of this 
is actually on the MySQL site:

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

Yes it's possible with some SUBSTRING() and LOCATE() or 
SUBSTRING_INDEX() calls, but it's not something I can stare at and tell 
you how to do in 3 seconds, so I'll just refer you to the manual:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

Baron
Thread
Seeing Table Structuretonylabarbara23 Jul
  • Re: Seeing Table StructureOlaf Stein23 Jul
    • Re: Seeing Table StructureMogens Melander23 Jul
      • Re: Seeing Table Structuretonylabarbara23 Jul
      • Re: Seeing Table StructureSteve Edberg24 Jul
    • Re: Seeing Table Structuretonylabarbara23 Jul
    • adress stored as an arrayRoss Hulford14 Sep
      • Re: adress stored as an arrayBaron Schwartz14 Sep