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
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]
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]
