php - How to store an array into mysql?

Question

Is there a way to store an array into mysql field? I'm creating a comment rating system so I want to store the arrays of user ids to prevent multiple votings. I'm going to create new table that holds the comment id and the array of user ids who have voted on this comment. Than I'll join comments table and this table and check whether the current user id exists in the voters array or note. If it does than the voting icons would be disabled. I think I'll prevent to use mysql query in loop in this way.

Do you happen to know any better ways?

Answer 1

You may want to tackle this as follows:

CREATE TABLE comments (
    comment_id int, 
    body varchar(100), 
    PRIMARY KEY (comment_id)
);

CREATE TABLE users (
    user_id int, 
    username varchar(20), 
    PRIMARY KEY (user_id)
);

CREATE TABLE comments_votes (
    comment_id int, 
    user_id int, 
    vote_type int, 
    PRIMARY KEY (comment_id, user_id)
);

The composite primary key (comment_id, user_id) on the intersection table comments_votes will prevent users from voting multiple times on the same comments.

Let's insert some data in the above schema:

INSERT INTO comments VALUES (1, 'first comment');
INSERT INTO comments VALUES (2, 'second comment');
INSERT INTO comments VALUES (3, 'third comment');

INSERT INTO users VALUES (1, 'user_a');
INSERT INTO users VALUES (2, 'user_b');
INSERT INTO users VALUES (3, 'user_c');

Now let's add some votes for user 1:

INSERT INTO comments_votes VALUES (1, 1, 1);
INSERT INTO comments_votes VALUES (2, 1, 1);

The above means that user 1 gave a vote of type 1 on comments 1 and 2.

If the same user tries to vote again on one of those comments, the database will reject it:

INSERT INTO comments_votes VALUES (1, 1, 1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY'

If you will be using the InnoDB storage engine, it will also be wise to use foreign key constraints on the comment_id and user_id fields of the intersection table. However note that MyISAM, the default storage engine in MySQL, does not enforce foreign key constraints:

CREATE TABLE comments (
    comment_id int, 
    body varchar(100), 
    PRIMARY KEY (comment_id)
) ENGINE=INNODB;

CREATE TABLE users (
    user_id int, 
    username varchar(20), 
    PRIMARY KEY (user_id)
) ENGINE=INNODB;

CREATE TABLE comments_votes (
    comment_id int, 
    user_id int, 
    vote_type int, 
    PRIMARY KEY (comment_id, user_id),
    FOREIGN KEY (comment_id) REFERENCES comments (comment_id),
    FOREIGN KEY (user_id) REFERENCES users (user_id)
) ENGINE=INNODB;

These foreign keys guarantee that a row in comments_votes will never have a comment_id or user_id value that doesn't exist in the comments and users tables, respectively. Foreign keys aren't required to have a working relational database, but they are definitely essential to avoid broken relationships and orphan rows (ie. referential integrity).

In fact, referential integrity is something that would have been very difficult to enforce if you were to store serialized arrays into a single database field.