How to store multiple dynamic values within one field in MySQL?

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 separate lines for example:

1
2
foo
tree
monkey

Any ideas?

MySQL 5.7.8 has a new data type that is JSON. You can store a JSON string with all the user information in that column.

Example:

CREATE TABLE table1 (jsonString JSON);

INSERT INTO table1 VALUES('{"car": "bmw", "year": "2006", "key": "value"  }');

MySQL Reference

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);

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