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 
to 
> 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 
like, 
> but not quite:  SELECT * FROM mytable ORDER BY companyname
> 
> (where the "companyname" is some function that pulls out the company 
name)
> 
> 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.

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

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
Thread
Selecting based on serialized field...Subscriptions20 Jan
  • Re: Selecting based on serialized field...Peter Brawley20 Jan
  • Re: Selecting based on serialized field...SGreen20 Jan