How to store an array into mysql?

by

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.

More Related Contents:

  1. Laravel one to many relation with a one to one relation
  2. which is better every user have a table or just a row [closed]
  3. save user images in MYSQL database by which method? [closed]
  4. PHP – Change value y,n in database [closed]
  5. PDOException SQLSTATE[HY000] [2002] No such file or directory
  6. Many-to-many relationships examples
  7. PDO get the last ID inserted
  8. Warning: mysql_connect(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in
  9. Warning: mysqli_connect(): (HY000/1045): Access denied for user ‘username’@’localhost’ (using password: YES)
  10. How to upload images into MySQL database using PHP code
  11. Importing CSV data using PHP/MySQL
  12. MySQL check if a table exists without throwing an exception
  13. Maximum execution time in phpMyadmin
  14. Mysql No connection could be made because the target machine actively refused it
  15. Replacing mysql_* functions with PDO and prepared statements
  16. PDO::PARAM for type decimal?
  17. Is there any way to check the performance of mysql Indexing
  18. Selecting a float in MySQL
  19. phpmyadmin automatic logout time
  20. MySQL skipping first row
  21. Using LIKE in bindParam for a MySQL PDO Query [duplicate]
  22. Fatal error: Call to undefined function mysqli_result()
  23. Laravel nested relationships
  24. Selecting all fields except only one field in mysql [duplicate]
  25. mysqli – fetch_Array error call to a member function fetch_array() on a non-object mysqli [duplicate]
  26. mysql injection damages?
  27. MySQL ORDER BY rand(), name ASC
  28. How do I save PHP session data to a database instead of in the file system?
  29. Auto Increment skipping numbers?
  30. Mysqli multiple row insert, simple multi insert query [duplicate]

Leave a Comment