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);
}
?>

Published by Sunny

A Mechanical engineer by education and web developer by passion and profession. He lives in Lucknow, India. Currently earning his livings by hitting keyboad. ;)

Join the Conversation

6 Comments

  1. 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

  2. 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

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

  4. 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.

Leave a comment

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