How to store array in mysql?

This is one of the most asked question by php programmer because mysql doesn’t has any array data type. So we can not store array directly into mysql database.

To do this we have to first convert array into string using php serialize() function then save it into mysql database.

php code to store array in database

<?
$array = array("foo", "bar", "hello", "world");
$conn=mysql_connect('localhost', 'mysql_user', 'mysql_password');
mysql_select_db("mysql_db",$conn);
$array_string=mysql_escape_string(serialize($array));
mysql_query("insert into table (column) values($array_string)",$conn);
?>

To retrieve array from database

<?
$conn=mysql_connect('localhost', 'mysql_user', 'mysql_password');
mysql_select_db("mysql_db",$conn);
$q=mysql_query("select column from table",$conn);
while($rs=mysql_fetch_assoc($q))
{
$array= unserialize($rs['column']);
print_r($array);
}
?>

Liked It? Get Free updates in your Email

Delivered by feedburner

6 thoughts on “How to store array in mysql?

  1. Abbas Haroon
    #

    Thanks for sharing. I totally forgot the serialize() function. I was working in codeignitor and now i remebered that it had a built in mysql function which auto serilize. thanks a lot. Keep posting

    Reply
  2. seandale
    #

    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?
    java barcode control

    Reply
  3. Shivrajh Singh
    #

    Thanks a lot for giving this code, I am searching for this for a long time
    thanks again.

    Reply
  4. AVERY HORTON
    #

    I am having a problem updating records that have stored arrays.

    I can add, delete, and add again but update fails.

    Please provide an example.

    Thanking you in advance.

    Reply
  5. VideoPortal
    #

    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.

    Reply
  6. Hans Henrik
    #

    MySQL’s arrays are called `tables`

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *