Re: Selecting based on serialized field...
"Subscriptions" <[EMAIL PROTECTED]> 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
Re: Selecting based on serialized field...
>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) Yes you can ORDER BY a function result. PB - Subscriptions wrote: 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 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.21/235 - Release Date: 1/19/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting based on serialized field...
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]