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

User · Answer

If you just store the data in a database as you would if you were manually putting it into an array

"INSERT INTO database_name.database_table (`array`)
    VALUES
    ('One,Two,Three,Four')";

Then when you are pulling from the database, use the explode() function

$sql = mysql_query("SELECT * FROM database_name.database_table");
$numrows = mysql_num_rows($sql);
if($numrows != 0){
    while($rows = mysql_fetch_assoc($sql)){
        $array_from_db = $rows['array'];
    }
}else{
    echo "No rows found!".mysql_error();
}
$array = explode(",",$array_from_db);
foreach($array as $varchar){
    echo $varchar."<br/>";
}

Like so!

User · Answer

Storing with json or serialized array is the best solution for now  With some situations  trimming     characters  json might be getting trouble but serialize should be great choice   Note  If you change serialized data manually  you need to be careful about character count

User · Answer

I d prefer to normalize your table structure more  something like   COMMENTS ------- id  pk  title comment userId   USERS ----- id  pk  name email   COMMENT VOTE ------------ commentId  pk  userId  pk  rating  float    Now it s easier to maintain  And MySQL only accept one vote per user and comment

User · Answer

You can use the php serialize function to store array in MySQL    lt  php   array   array  Name   gt  Shubham   Age   gt  17   website   gt  http   mycodingtricks com      string array   serialize  array    echo  string array     gt    It   s output will be     a 3 s 4  Name  s 7  Shubham  s 3  Age  s 2  17  s 7  website  s 25  http   mycodingtricks com      And then you can use the php unserialize function to decode the data   I think you should visit this page on storing array in mysql

User · Answer

You can always serialize the array and store that in the database  PHP Serialize  You can then unserialize the array when needed

User · Answer

Consider normalizing the table structure into a comments  and a separate votes table   Table  comments    id comment user       Table  votes    user id   comment id vote  downvote upvote    this would allow an unlimited number of votes without having to deal with the limits of a database field    Also  you may have future needs for operations like  show all votes a user has cast   removing specific votes or limiting the maximum number of votes per day  These operations are dead easy and fast to implement with a normalized structure  and horribly slow and complex in a serialized array

User · Answer

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

User · Answer

you should have three tables  users  comments and comment users   comment users has just two fields  fk user id and fk comment id  That way you can keep your performance up to a maximum

User · Answer

create table like this   CommentId    UserId ---------------------    1            usr1    1            usr2   In this way you can check whether the user posted the comments are not   Apart from this there should be tables for Comments and Users with respective id s

User · Answer

You can save your array as a json  there is documentation for json data type  https   dev mysql com doc refman 5 7 en json html

[php] How to store an array into mysql?

Examples related to php

Examples related to mysql

Examples related to database