Need to insert multiple values in the same row,For example i need to insert the different referrer came for a site.the table look like
|Date |Ref |Uri
--------------------------
28/9 |ref1 ref2 ref3 |url1
in the above table for the same date and link got the 3 different referrer. How can i store the referrer in the same row for the particular date and retrieve individual referrer. Hope have understand my requirement
You can do this, but you shouldn't. It contradicts the Database normalization rules, which you can see here: https://en.wikipedia.org/wiki/Database_normalization.
Use a further table which contains the primary key from your table above and connect it with each ref key. Example:
Existing Table:
T-Id |Date |Uri
--------------------------
1 | 28/9 |url1
2 | 28/9 |url2
New Table:
Id | Ref-Id | T-Id
--------------------------
1 | 1 | 1
2 | 2 | 1
3 | 3 | 1
4 | 1 | 2
5 | 3 | 2
First of all you should not do that . You should not save data in MySQL like that. Any row must not have a column in which more than one value is saved like separated with commas ,space or anything else. Rather than that, you must separate such data into multiple rows. By this, you can easily retrieve,update and delete any row.
But if you want to save data like that then you should go for JSON datatype . As of MySQL 5.7.8, MySQL supports a native JSON data type that enables efficient access to data in JSON (JavaScript Object Notation) documents.
It can be saved using JSON array . A JSON array contains a list of values separated by commas and enclosed within [ and ] characters:
["abc", 10, null, true, false]
Create table ex:
CREATE TABLE `book` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) NOT NULL,
`tags` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Insert data ex:
INSERT INTO `book` (`title`, `tags`)
VALUES (
'ECMAScript 2015: A SitePoint Anthology',
'["JavaScript", "ES2015", "JSON"]'
);
There are many native functions in MySql to handle JSON data type.
How to Use JSON Data Fields in MySQL Databases
In the case when referer is an entity, having many attribute then you can do as suggested by @rbr94 . In case when referer has not more than one attribute then splitting data in multiple rows or using JSON DataType will do the Job. At last it depends on your choice of solution.
,ref1,ref2,ref3,. Later you can explode the values with comma (in your program) and then take it up. If suppose you want to look for all the records ofref2you can uselike '%,ref%'