CODEWITHSUNDEEP
phpmysql
Freddy
Freddy

Reputation: 867

Storing multiple data in one field (storing data in an array in database)

I have a table called user_thoughts. The table has many columns, one of them being favourited_by.

A thought may be favourited by many different users, but I don't want to create a new row stating that this thought id has been favourited by this user.

I would rather have it that it stores multiple username's in one field. So favourited_by for example can hold data like this: 

Alice, Fred, Freddy, Conor ....

All in one single row. I have tried messing around with the data types on phpMyAdmin but cannot figure out how the field can hold multiple data.

Upvotes: 1

Views: 849

Answers (2)

Siphon
Siphon

Reputation: 1061

What you're asking is the wrong way to do this. You should not serialize the favorites data into a text field for either the user table or the thought table. This destroys the whole purpose of using a relational database like MySQL.

The right way to do this: create a cross-reference table between the user table and the thought table. This utilizes a many-to-many table to store a list of favorites using the primary keys of a thought row and a user row.

Your new favorite table:

CREATE TABLE IF NOT EXISTS `favorite` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `thought_id` int NOT NULL,
  PRIMARY KEY (`id`)
);

What this does is take the id from the user table and store it in favorite.user_id, then stores the id from the thought table in favorite.thought_id.

To add a new favorite for the user with the id of 123, for the thought with id 456:

INSERT INTO favorite (user_id, thought_id) VALUES ('123', '456');

Get the users that have marked the thought with id 456 as their favorite (using a JOIN):

SELECT u.* FROM favorite AS f
  JOIN user AS u ON u.id = f.user_id
  WHERE f.thought_id = 456;

And similar to the last query, get the favorite thoughts for the user with id 123:

SELECT t.* FROM favorite AS f
  JOIN thought AS t ON t.id = f.thought_id
  WHERE f.user_id = 123;

Upvotes: 1

DanielM
DanielM

Reputation: 6666

The ideal way to handle this is to map it to another table, however you can just store it as json.

MySQL 5.7 even includes JSON as a data type allowing easier filtering and manipulation.

https://dev.mysql.com/doc/refman/5.7/en/json.html

You can put json into any text field however if you don't need to search it, etc.

Upvotes: 0

Related Questions