Storing multiple dynamic values within one field in mysql.

Question

How can i go about storing multiple values ( numbers and words ) within one field of a mysql database and then extracting them again as and when i need them using mySQL and php?

for example: I want to store the dynamic values a user will enter using a form for example 1, 2, foo, tree, and monkey all in the same field in a database.

then i want to extract it and put them on seperate lines for example: 1 2 foo tree monkey

any ideas?

Answer 1

If you're referring to a datatype which can handle a whole slew of stuff, you can use text otherwise this is a bad idea and this is not how you should be storing data in a normalized relational database. Can you please provide information on what you're storing?

I'm a SQL noob myself so if any guru has a better schema strategy, let me know.. this is what I came up with:

Dump:

/*

Navicat MySQL Data Transfer


Date: 2009-10-20 03:01:18

*/



SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for `job_scores`

-- ----------------------------

DROP TABLE IF EXISTS `job_scores`;

CREATE TABLE `job_scores` (

  `job_id` int(2) NOT NULL,

  `user_id` int(2) NOT NULL,

  `rating` tinyint(2) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ----------------------------

-- Records of job_scores

-- ----------------------------

INSERT INTO `job_scores` VALUES ('1', '1', '10');



-- ----------------------------

-- Table structure for `jobs`

-- ----------------------------

DROP TABLE IF EXISTS `jobs`;

CREATE TABLE `jobs` (

  `id` int(2) NOT NULL auto_increment,

  `name` varchar(50) collate utf8_unicode_ci default NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ----------------------------

-- Records of jobs

-- ----------------------------

INSERT INTO `jobs` VALUES ('1', 'plumber');



-- ----------------------------

-- Table structure for `users`

-- ----------------------------

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (

  `id` int(2) NOT NULL auto_increment,

  `name` varchar(50) collate utf8_unicode_ci NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ----------------------------

-- Records of users

-- ----------------------------

INSERT INTO `users` VALUES ('1', 'John');

Example query:

SELECT

jobs.name as job_name, users.name as user_name, job_scores.rating

FROM

job_scores


INNER JOIN jobs ON jobs.id = job_scores.job_id
INNER JOIN users on users.id = job_scores.user_id

WHERE 

user_id = 1

Result:

plumber John 10

Answer 2

You can put all the values into an array and then serialize it:

$string = serialize(array(1, 2, 'foo', 'tree', 'monkey');

This will give you a string which you store in your database. Later, you can recover your array with de-serializing it:

$array = unserialize($string);