CODEWITHSUNDEEP
phpmysqlarraysserialization
Albert
Albert

Reputation: 1526

make a query searching for a value in a field with a serialized array

I've read that this is not a good option, but I don't have any other way to do this.

I use cakephp and its database sessions. All the data of a session is stored as a serialized array, something like:

Config|a:3:{s:9:"userAgent";s:32:"c25774da215a84965aadcfd075489db8";s:4:"time";i:1331594676;s:7:"timeout";i:10;}Message|a:0:{}Auth|a:1:{s:7:"Usuario";a:20:{s:10:"id_usuario";s:4:"6658";s:6:"nombre";s:6:"Master";s:8:"apellido";s:4:"User";s:5:"login";s:11:"master-user";s:16:"fecha_nacimiento";s:10:"1998-08-03";s:14:"fecha_registro";s:10:"0000-00-00";s:13:"hora_registro";s:8:"00:00:00";s:4:"pais";s:1:"7";s:9:"provincia";s:2:"CA";s:6:"cuidad";s:5:"dfdff";s:13:"codigo_postal";s:6:"123456";s:6:"ctpais";s:0:"";s:8:"ctcuidad";s:0:"";s:8:"telefono";s:8:"23324234";s:5:"grupo";s:1:"3";s:6:"activo";s:1:"1";s:11:"id_campania";s:3:"999";s:9:"sessiones";s:1:"1";s:10:"admin_camp";s:1:"0";s:6:"codigo";N;}}

When a user logs in, I need to check if he already has a session going on, so I need to do a query searching for the value of the field login inside that serialized array to retrieve the record that contains that value, if it exists.

How can I do that?

Upvotes: 0

Views: 261

Answers (1)

Basti
Basti

Reputation: 4042

Store the user login in a seperate column like

CREATE TABLE  `session` (
    `session_id` VARCHAR( 50 ) NOT NULL ,
    `user_login` VARCHAR ( 50 ) NOT NULL,
    `session_serialized` TEXT NOT NULL ,
    PRIMARY KEY (  `session_id` )
);

You can now search for sessions with WHERE user_login = ?.

If you cannot or don't want to touch the session table, you can also create a second table storing which user is using which session_id. Assuming you want a user not to have more than one session:

CREATE TABLE  `user_session` (
    `session_id` VARCHAR( 50 ) NOT NULL ,
    `user_login` VARCHAR ( 50 ) NOT NULL,
    PRIMARY KEY (  `user_login` )
);

I would not recommend search through the serialized Session-Array.

Upvotes: 1

Related Questions