Issue in inserting array in mysql insert query?

PHP
1

  • I want to store multiple values in database .

  • “$add1” and “$add2” are my two arrays in which I am storing values entered through two different fields.

  • In codeigniter This is my function from model where I am getting confused

        public function add3($add1,$add2,$id){

  if($add1 != null && $add2 != null){

  foreach($add1 as $a1) {
         $data1[]= array('no_of_containers'=> $a1,'container_id' => $id);
   }
   
   foreach($add2 as $a2) {
         $data2[]= array('no_of_cylinders'=> $a2,'cylinder_id' => $id);
   }
  

   $data = array_merge($data1,$data2);
 

   $this->db->insert('tbl_box',$data);
 
 if ($this->db->affected_rows() > 0) {
 	return true;
 } else {
 	return false;
 }  
      }	
  }

  • I am getting values in $data perfectly like this -

          Array
  (
  [0] => Array
    (
      [no_of_containers] => 2
      [container_id] => 28
     )

 [1] => Array
  (
      [no_of_containers] => 3
      [container_id] => 28
  )

  [2] => Array
  (
      [no_of_cylinders] => 5
      [cylinder_id] => 28
  )

    [3] => Array
  (
      [no_of_cylinders] => 3
      [cylinder_id] => 28
  )
)

-But when I print my query Its showing up like this

   INSERT INTO `tbl_box` (0, 1, 2, 3) VALUES (Array, Array, Array, Array)

Here I am not getting clear idea , its not working for me , how can I fix it ?

2

based on this document, you should be calling insert_batch here, instead of insert.

1 Like
3

This is my updated query

            $this->db->insert_batch('tbl_box',$data);

its working but not perfectly , now I am getting query like this

          INSERT INTO `tbl_box` () VALUES ('31','2'), ('31','3'), Array
4

Those values do not in any way match what you originally showed as being in $data. Re-verify your variable.

5

Thats because every time I try to inser new data the id and no of cylinder and container changed , its working perfectly for my purpose , so , values are not the isse here ,

  • the issue is , I am not getting all the four arrays in insert query like these two
6

Let me rephrase: It is impossible for your code above to have generated the thing you showed as $data in your OP.

Your foreach codes reference static values $year, $id, and $leave, rather than values specific to the loop instance.

Reevaluate your construction code. Values are 100% your issue here.

7

@m_hutley Ohh sorry! . actually , That was my mistake, I forgot to replace those values I have update my OP now

  • I am getting $id $add1 and $add2 from controller perfectly

Still its not working , please guide me .

8

So what does the query look like now?

9

its still the same

     INSERT INTO `xin_leaves_per_seniority` () VALUES ('58','3'), ('58','2'), Array
  • here 58 is id of recent inserting row thats why it is same for all
  • but next two arrays are not displaying here , I tried some logics here alos but they are also not working
10

and print_r($data); immediately before your insert_batch line?

11

after print_r($data);
here is my current $data

                 Array
    (
      [0] => Array
     (
        [no_of_containers] => 2
        [container_id] => 60
    )

   [1] => Array
    (
        [no_of_containers] => 3
        [container_id] => 60
    )

   [2] => Array
    (
        [no_of_cylinders] => 5
        [cylinder_id] => 60
    )

    [3] => Array
    (
        [no_of_cylinders] => 7
        [cylinder_id] => 60
    )

)

And this is my current query

     INSERT INTO `tbl_box` () VALUES ('60','2'), ('60','3'), Array
12

Is it getting upset because of the varying column names? That is, don’t they need to all be the same to work with multiple values in the same query? I don’t know Codeigniter, but I don’t see how it’s supposed to aggregate that array of arrays into the query.

13

They would, yes. Good eye, droopsnoot, I didnt even notice the difference.

14

Then how should I fix that ? I am not getting it correctly .

15

My guess would be don’t do the array_merge() and call insert_batch() separately for the two arrays you had in the beginning, $data1 and $data2.

16

Assuming you’re trying to insert 4 rows, then yes, insert each set individually.

If you’re trying to insert 2, you’ll need a more intelligent merge than simply array_merge’ing them together.

17

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.