Insert array in ONE field in mysql

Question

Hello .. I want to insert an array in ONE field in mysql database using PHP ..

this is work fine :

HTML :

anotherField :<input type="text"  name="anotherField" />

fax :<input type="text"  name="f[]" />
email :<input type="text"  name="f[]" />
phone :<input type="text"  name="f[]" />

PHP (I use CodeIgniter frame) :

<?php
    function addCustomRow($tableName)
    {

    $arr = $this->input->post('f');
    $field = implode("|", $arr);

        $data = array(
        'anotherField'          => $this->input->post('anotherField'),
        'field'             => $field
        );

        $this->db->insert($tableName, $data);
    }
?>

and I get data in mysql like this

fax|email|phone

BUT ..

My question is .. I want many arrays in the same field .. Like this :

fax|email|phont :br: fax|email|phone :br: fax|email|phone ..

I tried some thing like this :

Html :

Frist array :
fax :<input type="text" class="inp" name="f[0][0]" />
email :<input type="text" class="inp" name="f[0][1]" />
phone :<input type="text" class="inp" name="f[0][2]" />

Second array :
fax :<input type="text" class="inp" name="f[1][0]" />
email :<input type="text" class="inp" name="f[1][1]" />
phone :<input type="text" class="inp" name="f[1][2]" />

PHP :

<?php
    function addCustomRow($tableName)
    {

    $arr = $this->input->post('f[]');
    $field = implode(":br:", $arr);

        $data = array(
        'anotherField'          => $this->input->post('anotherField'),
        'field'             => $field
        );

        $this->db->insert($tableName, $data);
    }
?>

but it says Wrong [ Severity: Notice Message: Array to string conversion ] and I get data in mysql like this

array :br: array  

EDIT :

I want to do that like this way because I have a Categories Table .. and each cat has own details ( fields ) .. so when I add new cat I just do some thing like this

       name of cat : <input type="text"  name="name" />

   <!-- Fields -->
    Fields //

    Field 1 :
    title of the filde : <input type="text"  name="f[0][0]" />
    type : <input type="text"  name="f[0][1]" /> <!-- 1= text , 2= select , 3= textarea ..  -->
    default value : <textarea rows="8" cols="20" name="f[0][2]"> </textarea> <!-- if select type write value1::value2::value3  ... -->



Field 2 :
title of the filde : <input type="text"  name="f[1][0]" />
type : <input type="text"  name="f[1][1]" /> <!-- 1= text , 2= select , 3= textarea ..  -->
default value : <textarea rows="8" cols="20" name="f[1][2]"> </textarea> <!-- if select type write value1::value2::value3  ... -->

Field 3 :
title of the filde : <input type="text"  name="f[2][0]" />
type : <input type="text"  name="f[2][1]" /> <!-- 1= text , 2= select , 3= textarea ..  -->
default value : <textarea rows="8" cols="20" name="f[2][2]"> </textarea> <!-- if select type write value1::value2::value3  ... -->

and I can add any number of fields here ..

in database I want data inserted like this :

[nameOfBook|1|anyName :br: noOfPages|1|anyNo ]

and in the other cat like this for example :

[colorOfcar|2|red::black::green :br: price|1|anyPrice ]

any help ?

thanks in advance ..

Answer 1

Why are you storing multiple values in one field?!

Answer 2

To store an array in one field, just serialize it. When you need to access the array from the database unserialize it.

Answer 3

First and foremost, I would suggest that you try to normalize your table scheme a little more. Storing multiple, multiple values in one field is going to cause many headaches. Perhaps, something like this:

In this scheme, the contact_information table is associated with the person table by storing an ID (foreign reference) to a person row. This way, you can have as many contact entries for any given person, without having to cram an array of data into one field.

---

At any rate, to solve your problem as it is, try serializing the data before you insert it into the DB.

function addCustomRow($tableName) {    
    $data = array(
        'anotherField' => $this->input->post('anotherField'),
        'field'        => serialize($this->input->post('f[]'))
    );

    $this->db->insert($tableName, $data);
}

Edit: updated to address comment.

Answer 4

@Justin Johnson

thanks for your answer .. umm but it dosnt work .. I have to use $data var to insert all the data but I use you answer like this

function addCustomRow($tableName)
{

    $data = array(
        'name'          => $this->input->post('name'),
        'fields'            => serialize($this->input->post('f[]'))
        );

    $this->db->insert($tableName, $data);
}

and I get data mysqyl like this ( b:0; ) !!

//

@Suku thanks .. I have used it before asking but I did not know how .. How can I use it in my case here ? ..

@Alex

because I have table named (categories) and every cat has own fields like :

carsCat >
  type :
  color:
  details:

BooksCat >
nameOfbook:
writer:
numberOfpage:

and so on .. I find this way the Best way maybe in my case .. any suggestions :)

Answer 5

Hi again !

I did it with a small trick :) I just add one heddin input with value (:br:) , and keep all names of inputs equal (f[]) .. like this :

HTML :

field out of the array :
anotherField :<input type="text"  name="name" />

Field 1 :
title of the field : <input type="text"  name="f[]" />
type : <input type="text"  name="f[]" /> <!-- 1= text , 2= select , 3= textarea ..  -->
value : <textarea rows="8" cols="20" name="f[]"> </textarea> <!-- if select type write value1::value2::value3  ... -->
<input type="hidden" name="f[]" value=":br:" />

Field 1 :
title of the field : <input type="text"  name="f[]" />
type : <input type="text"  name="f[]" /> <!-- 1= text , 2= select , 3= textarea ..  -->
value : <textarea rows="8" cols="20" name="f[]"> </textarea> <!-- if select type write value1::value2::value3  ... -->
<input type="hidden" name="f[]" value=":br:" />

PHP :

function addCustomRow($tableName)
{
$arrF1 = $this->input->post('f');
$f = implode("|", $arrF1);

$data = array(
    'name'  => $this->input->post('name'),
        'fields'    => $f
    );

$this->db->insert($tableName, $data);

}

now I get data in mysql like this :

titleOffield|type|value|:brr:|titleOffield|type|value|:brr:

for example :

nameOfBook|1|writeTheName|:Br:|NoOfPages|1|value|:br:|

Then I can return the data from mysql database easily like this : (I use Smarty ! )

Output

  {assign var=fieldsBr value=":br:|"|explode:$r.values} <!-- exploade :br: -->

    {foreach from=$fieldsBr item=ff}
    {assign var=f value="|"|explode:$ff} <!-- exploade | -->
    {$f[0]} :  <b>{$f[2]}</b>  <br />  <!-- title of field :  the value  <br />  -->
    {/foreach}

** Notice I use 2 explode here One for (:br:|) and the other for (|) ..

Thanks any way for your help ..

Ahmad ..