List:General Discussion« Previous MessageNext Message »
From:SGreen Date:January 20 2006 3:03pm
Subject:Re: Selecting based on serialized field...
View as plain text  
"Subscriptions" <subscriptions@stripped> wrote on 01/19/2006 
06:13:06 PM:

> I'm not sure if this falls under a PHP topic or a MySQL topic, but I 
have a 
> table that contains a field that stores serialized data.  Is there a way 
> order a recordset by a piece of the serialized data?  For example, if a 
> field called "data" contains serialized data that looks like this:
> a:11:{s:7:"company";s:12:"Some Company";s:8:"jobtitle";s:17:"Assistant 
> Manager";s:5:"phone";s:12:"123-456-7890";s:3:"fax";s:
> 12:"987-654-3210";s:7:"bf_time";i:1135221050;s:8:"bf_value";d:2.
> 416246843777560382449109965818934142589569091796875E-5;s:
> 17:"signup_email_sent";i:1;s:6:"status";a:2:{i:2;i:0;i:1;i:1;}s:
> 9:"is_active";i:1;s:9:"is_locked";s:0:"";s:11:"affiliation";s:1:"1";}
> is there a way to select a recordset ordered by "company"?  Kind of 
> but not quite:  SELECT * FROM mytable ORDER BY companyname
> (where the "companyname" is some function that pulls out the company 
> Jenifer

There is no native function compiled into the server that will pull out 
just certain fields from a dump like that. However, you can manually 
extract any part of any string through judicious use of the string 
functions that are built in.

Depending on your version, you could create either a User-defined function 
(UDF), a SQL Function, or a SQL Stored Procedure to help you do this.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Selecting based on serialized field...Subscriptions20 Jan
  • Re: Selecting based on serialized field...Peter Brawley20 Jan
  • Re: Selecting based on serialized field...SGreen20 Jan