How to store & retrieve an array from a mySQL table?

[irock]

I would like to know if there's a way to store an entire array into a column of a mysql table. Someone told I can do so easily. Do you know how?

Thanks

[Nick_W]

You may have to explain what you need a little more. This isn't like 'how do i make the font big' - it's a little more complex than that.....

Nick

[irock]

Okay sorry about that.

I have a array named 'specifications' and I have a mysql table named 'hardware'

Since my spec array contains a whole bunch of elements (50 so far), I would like to put this array into a column called specifications under 'hardware' table.

Is this possible?

[brotherhood of LAN]

check out the implode function, since the values are in an array, you want to put the values of the array into a mysql query that inserts the values into a table.

$query = "INSERT INTO hardware (specifications) VALUES (".implode(",",$specifications).")";

If the values in the array are text values, you will need to add quotes

$query = "INSERT INTO hardware (specifications) VALUES ("'.implode("','",$specifications)."')";

mysql_query($query);

Also, if you don't want duplicate values, switch the "INTO" to "IGNORE" and only unique values will be inserted into the table.

Hope thats what youre asking for.

[irock]

Hi brotherhood,

Will this still be an array if I try to retrieve it using ordinary mySQL fetch method?

[brotherhood of LAN]

yup, if youre going to insert this array of values into the DB, you can retrive them back as an array

$query = mysql_query("SELECT specifications FROM hardware");
while($array = mysql_fetch_array($query,MYSQL_ASSOC)
{
echo $array['specifications'],'<br>';
}

This is querying the table for the results you inserted before, it should echo all the values in the DB under the 'specifications' field. Note "mysql fetch array", the function basically grabs the info from a db and puts it into an array.

[irock]

So, in other words, if I wish to display a particular element in the 'spec' array. From your example, I should add something like this, right?

$query = mysql_query("SELECT specifications FROM hardware");
while($array = mysql_fetch_array($query,MYSQL_ASSOC)
{
for loop here
echo $array['specifications'][$i],'<br>';
}

Given $i increments by one after each for loop.

[edited by: irock at 10:43 pm (utc) on Aug. 10, 2003]

[brotherhood of LAN]

no need to change the example ;) Try it....

Each result in mysql_fetch_array is an array in itself, i.e. all rows are their own array.

afaik, if you wanted to put them all back in the single array you could do this (maybe there's a better way?)

$array2 = array();
$query = mysql_query("SELECT specifications FROM hardware");
while($array = mysql_fetch_array($query,MYSQL_ASSOC)
{
array_push($array2,$array['specifications'];
}

print_r($array2);

That will end up with a single array with all the values (in order) retrieved from your table.

[irock]

BTW, I just found this piece of code on the net.

Storing array into mysql...

$value = addslashes(serialize($array));
$query = "INSERT INTO table (column) VALUES (\"$value\")"

and upon retrieval
$query = "SELECT column FROM table";
......
while($row = mysql_fetch_array($result)) {
$value = unserialize(stripslashes($row["column"]));
}

How does this compare to yours?

Thanks again... much appreciated your help!

[jatar_k]

the two methods are similar

whether you use implode or serialize you will have to use explode or unserialize respectivley to get at the actual array elements.