Insert array in ONE field in mysql

Question

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|phone :br: fax|email|phone :br: fax|email|phone ..

I tried some thing like this :

Html :

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

@Justin Johnson

thanks for your answer, but it doesnt 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?

Sukumar · Accepted Answer · 2010-02-01 05:42:11Z

3

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

answered Feb 1, 2010 at 5:42

Sukumar

3,5873 gold badges28 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gerard Banasig Over a year ago

serialize and unserialize will do the trick and as Justin Johnson also mentioned it's also good to normalize them

me.again Over a year ago

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

Glorfindel · Accepted Answer · 2019-06-11 10:06:33Z

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.

edited Jun 11, 2019 at 10:06

Glorfindel

22.8k13 gold badges97 silver badges124 bronze badges

answered Feb 1, 2010 at 5:42

Justin Johnson

31.4k7 gold badges67 silver badges89 bronze badges

4 Comments

me.again Over a year ago

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; ) !!

Justin Johnson Over a year ago

Sorry about that, copy and pasted into the wrong place. I've updated my answer with the corrected code.

me.again Over a year ago

thanks .. I use your new code but also it says wrong ( A Database Error Occurred Error Number: 1054 Unknown column 'anotherField' in 'field list' INSERT INTO cats (anotherField, field) VALUES (0, 'b:0;') )

me.again Over a year ago

oh sorry ! .. I change the name of the field to (name) , the data is inserted but I get that ( b:0; ) in my field :(

user3427308 · Accepted Answer · 2014-05-16 04:34:58Z

2

You may need to use the functions serialize() and unserialize() as the following:

$string_from_array = serialize($array);

and when you need to restore the array:

$array= unserialize($string_from_array);

see this for more info : http://www.wpfasthelp.com/insert-php-array-into-mysql-database-table-row-field.htm

answered May 16, 2014 at 4:34

user3427308

312 bronze badges

Comments

me.again · Accepted Answer · 2010-02-03 07:35:15Z

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

PRVS · Accepted Answer · 2016-03-08 08:33:54Z

0

INSERTING ARRAY VALUES FROM FORM INTO DATABASE

function addRecord($Table){
        global $connection; // database connection
        extract($Table);

        $entry_date = date('Y-m-d');

        for($i=0; $i< count($project_id); $i++)
        {

            $sql="INSERT INTO expenses(type_id,project_id,amount,exp_date,entry_date,comments,entry_name)
              values('$type_id[$i]','$project_id[$i]','$amount[$i]','$exp_date[$i]','$entry_date','$comments[$i]','$_SESSION[emp_name]')";

            $result=mysqli_query($conn,$sql);
        }

edited Mar 8, 2016 at 8:33

PRVS

1,7006 gold badges40 silver badges75 bronze badges

answered Mar 8, 2016 at 7:55

Safdar Pathan

111 silver badge7 bronze badges

Collectives™ on Stack Overflow

Insert array in ONE field in mysql

5 Answers 5

2 Comments

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related