Welcome Guest, Not a member yet? Register   Sign In
insert_batch() not working completely
#1

I have two array, one with same keys and second array with different different keys. insert_batch function working fine when keys of array is the same, but not working fine when keys are different. Please check below

1. Array with same keys (insert_batch() working fine)


Code:
Array
(

[0] => Array
   (
       [trainer_id] => 2
       [plan_id] => 2
       [week] => week1
       [mon] => {"time":"1:00 AM","item":"a","description":"a"}
       [created_at] => 2017-04-11 13:27:30
       [updated_at] => 2017-04-11 13:27:30
   )

[1] => Array
   (
       [trainer_id] => 2
       [plan_id] => 2
       [week] => week2
       [mon] => {"time":"1:00 AM","item":"b","description":"b"}
       [created_at] => 2017-04-11 13:27:30
       [updated_at] => 2017-04-11 13:27:30
   )

[2] => Array
   (
       [trainer_id] => 2
       [plan_id] => 2
       [week] => week3
       [mon] => {"time":"1:00 AM","item":"c","description":"c"}
       [created_at] => 2017-04-11 13:27:30
       [updated_at] => 2017-04-11 13:27:30
   )

[3] => Array
   (
       [trainer_id] => 2
       [plan_id] => 2
       [week] => week4
       [mon] => {"time":"1:00 AM","item":"d","description":"d"}
       [created_at] => 2017-04-11 13:27:30
       [updated_at] => 2017-04-11 13:27:30
   )
)
   

All keys of inner array are same in above array of array and insert_batch() is working fine for above array.

2. Array with different-different keys(insert_batch() is not working fine)

Code:
Array
(

[0] => Array
   (
       [trainer_id] => 2
       [plan_id] => 2
       [week] => week1
       [mon] => {"time":"1:00 AM","item":"a","description":"a"}
       [created_at] => 2017-04-11 13:17:27
       [updated_at] => 2017-04-11 13:17:27
   )

[1] => Array
   (
       [trainer_id] => 2
       [plan_id] => 2
       [week] => week2
       [tue] => {"time":"1:00 AM","item":"b","description":"b"}
       [created_at] => 2017-04-11 13:17:27
       [updated_at] => 2017-04-11 13:17:27
   )

[2] => Array
   (
       [trainer_id] => 2
       [plan_id] => 2
       [week] => week3
       [wed] => {"time":"1:00 AM","item":"d","description":"d"}
       [created_at] => 2017-04-11 13:17:27
       [updated_at] => 2017-04-11 13:17:27
   )

[3] => Array
   (
       [trainer_id] => 2
       [plan_id] => 2
       [week] => week4
       [thur] => {"time":"1:00 AM","item":"d","description":"d"}
       [created_at] => 2017-04-11 13:17:27
       [updated_at] => 2017-04-11 13:17:27
   )
)


Please check the just above array carefully because some keys are different in this array and insert_batch() is not working for this array.

Error shows like below for second array 

[Image: 4499ee4a-1f6d-11e7-8650-1ac0a6f14a0f.png]

DB TABLE Structure is given below

[Image: fa9448ce-1ed9-11e7-8c4c-fd3f0d74202c.png]


Your quick response to this will be highly appreciated.

Thanks.
Reply
#2

Any one have answer?
Reply
#3

That's expected.

Check the MySQL documentation, you can define columns only once.


Code:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
Reply
#4

(04-19-2017, 08:50 AM)RBX Wrote: That's expected.

Check the MySQL documentation, you can define columns only once.


Code:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Thank you very much for commenting on my query. Hope, You have read my question. So this is not an answer of question.


insert_batch() should work with different keys. It works fine when keys will be same in the array.
Reply
#5

The error is very clear.

One of the values your are inserting is an Array and not a string. It literally says that: "Array to string conversion"

Meaning the structure of the array you are passing to the insert_batch method is incorrect
Reply
#6

(04-20-2017, 01:37 AM)Martin7483 Wrote: The error is very clear.

One of the values your are inserting is an Array and not a string. It literally says that: "Array to string conversion"

Meaning the structure of the array you are passing to the insert_batch method is incorrect

Hope, You have seen my both array. For information, insert_batch() working fine when array keys are same as showing in below array.

Code:
Array
(

[0] => Array
  (
      [trainer_id] => 2
      [plan_id] => 2
      [week] => week1
      [mon] => {"time":"1:00 AM","item":"a","description":"a"}
      [created_at] => 2017-04-11 13:27:30
      [updated_at] => 2017-04-11 13:27:30
  )

[1] => Array
  (
      [trainer_id] => 2
      [plan_id] => 2
      [week] => week2
      [mon] => {"time":"1:00 AM","item":"b","description":"b"}
      [created_at] => 2017-04-11 13:27:30
      [updated_at] => 2017-04-11 13:27:30
  )

[2] => Array
  (
      [trainer_id] => 2
      [plan_id] => 2
      [week] => week3
      [mon] => {"time":"1:00 AM","item":"c","description":"c"}
      [created_at] => 2017-04-11 13:27:30
      [updated_at] => 2017-04-11 13:27:30
  )

[3] => Array
  (
      [trainer_id] => 2
      [plan_id] => 2
      [week] => week4
      [mon] => {"time":"1:00 AM","item":"d","description":"d"}
      [created_at] => 2017-04-11 13:27:30
      [updated_at] => 2017-04-11 13:27:30
  )
)

But insert_batch() is not working for below array because array keys are different, Please see below

Code:
Array
(

[0] => Array
  (
      [trainer_id] => 2
      [plan_id] => 2
      [week] => week1
      [mon] => {"time":"1:00 AM","item":"a","description":"a"}
      [created_at] => 2017-04-11 13:17:27
      [updated_at] => 2017-04-11 13:17:27
  )

[1] => Array
  (
      [trainer_id] => 2
      [plan_id] => 2
      [week] => week2
      [tue] => {"time":"1:00 AM","item":"b","description":"b"}
      [created_at] => 2017-04-11 13:17:27
      [updated_at] => 2017-04-11 13:17:27
  )

[2] => Array
  (
      [trainer_id] => 2
      [plan_id] => 2
      [week] => week3
      [wed] => {"time":"1:00 AM","item":"d","description":"d"}
      [created_at] => 2017-04-11 13:17:27
      [updated_at] => 2017-04-11 13:17:27
  )

[3] => Array
  (
      [trainer_id] => 2
      [plan_id] => 2
      [week] => week4
      [thur] => {"time":"1:00 AM","item":"d","description":"d"}
      [created_at] => 2017-04-11 13:17:27
      [updated_at] => 2017-04-11 13:17:27
  )
)

I think, inset_batch() need to be upgrade for insert the data in different columns. 


insert_batch() works fine when array has same keys not via different array keys. That is reason, so insert_batch() working fine for my 1st array not 2nd array.
Reply
#7

If that is the problem the the solution should be easy.

Looking at your arrays, each weekday is a column within the table

So just add every weekday to the arrays and fill in the values where they should go

Code:
Array
(
[0] => Array
  (
      [trainer_id] => 2
      [plan_id] => 2
      [week] => week1
      [mon] => {"time":"1:00 AM","item":"a","description":"a"}
      [tue] => ""
      [wed] => ""
      [thu] => ""
      [fri] => ""
      [sat] => ""
      [sun] => ""
      [created_at] => 2017-04-11 13:17:27
      [updated_at] => 2017-04-11 13:17:27
  )
[1] => Array
  (
      [trainer_id] => 2
      [plan_id] => 2
      [week] => week2
      [mon] => ""
      [tue] => {"time":"1:00 AM","item":"b","description":"b"}
      [wed] => ""
      [thu] => ""
      [fri] => ""
      [sat] => ""
      [sun] => ""
      [created_at] => 2017-04-11 13:17:27
      [updated_at] => 2017-04-11 13:17:27
  )

[2] => Array
  (
      [trainer_id] => 2
      [plan_id] => 2
      [week] => week3
      [mon] => ""
      [tue] => ""
      [wed] => {"time":"1:00 AM","item":"d","description":"d"}
      [thu] => ""
      [fri] => ""
      [sat] => ""
      [sun] => ""
      [created_at] => 2017-04-11 13:17:27
      [updated_at] => 2017-04-11 13:17:27
  )

[3] => Array
  (
      [trainer_id] => 2
      [plan_id] => 2
      [week] => week4
      [mon] => ""
      [tue] => ""
      [wed] => ""
      [thur] => {"time":"1:00 AM","item":"d","description":"d"}
      [fri] => ""
      [sat] => ""
      [sun] => ""
      [created_at] => 2017-04-11 13:17:27
      [updated_at] => 2017-04-11 13:17:27
  )
)
Reply
#8

Thank you so much for your reply. insert_batch() works fine for the array which you have shared, also i have tried earlier as well. But i would like to know the way for how can I use the insert_batch() for different different array keys as i have given the array.
If there is not any solution for now then fine. But, If point seems to be valid then please consider my point as bug/enhancement.
No further comment needed.
Thank you very much again.
Reply
#9

If you really want to use a batch function for arrays with different key => value pairs you will need to create a custom batch function that will create an insert for each array element.

But as RBX wrote
(04-19-2017, 08:50 AM)RBX Wrote: That's expected.

Check the MySQL documentation, you can define columns only once.

Code:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

This is the expected behavior for a bulk insert. It's not a bug or flaw in CodeIgniter

I would advise you use the solution I provide. Just set all the columns in your array and only pair the values you need for each record.
To make it easy, create a method that holds a default array with all columns set as keys with empty values.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB