CODEWITHSUNDEEP
phpmysqldatabasearrays
H Bellamy
H Bellamy

Reputation: 22715

How to add to an array in a database and get back each array and display it

How can I add a value into an array in a database row, and then later on, when I want to get values of the array, simply display each different array value on a new line?

Also, how do arrays work in mysql and how to get the value of it?

Upvotes: 0

Views: 457

Answers (3)

maček
maček

Reputation: 77786

save array

$foo = array(1,2,3);
mysql_query(sprintf("INSERT INTO some_table ('foo') VALUES ('%s')", serialize($foo));

foo will appear as a string 1,2,3 in the database now

fetch array

$result = mysql_query("SELECT id, foo FROM some_table")
$item = mysql_fetch_object($result);
$foo = $item->foo;
$foo = unserialize($foo);

add data to array

$foo[] = 4;
$foo = array_uniq($foo); // you might want to ensure only unique values
mysql_query(sprintf("UPDATE some_table SET foo='%s' WHERE id=%d", serialize($foo), $item->id);

foo will appear as a string 1,2,3,4 in the database now

Upvotes: 1

O. Jones
O. Jones

Reputation: 108796

MySQL does not have a native array data type. So, your first step is to figure out how you will store the elements of the array in a MySQL table.

Will you store each array element in its own database row?

 elem  val
  1    value1
  2    value2
     ...
  n    valuen

Or will you store the arraw as a series of concatenated values in a single row, something like this?

  values  
  value1,value2,value3,...,valuen

In the first case, you can update a single array element easily:

  UPDATE array SET val=newValue
   WHERE elem=updatedElement

In the second case, you'll have to read the values column, break it out (deserialize it) into an array, change the element or elements you want to change, then gather it up (serialize it) back into a values column, and update your mySQL table. @Anthony pointed this out.

You need to answer the question about how you're storing the array before you can start to figure out how you will update it.

Upvotes: 1

Anthony
Anthony

Reputation: 3218

Filed testField has serialized data.

$arrayData = array('one','two','three');
$serializedData = serialize($arrayData);

Mysql insertion:

insert INTO table ('testField') Values ($serializedData);

To get data:

select testField from table where id=1

You are getting here some string value. Then you should unserialize this string to get array:

$arrayData = unserialize($selectResultValue);

Look here for more details about serialize function: http://php.net/manual/en/function.unserialize.php

Upvotes: 2

Related Questions