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
DB TABLE Structure is given below
Your quick response to this will be highly appreciated.
Thanks.
-
RBX
Junior Member
-
Posts: 19
Threads: 8
Joined: Oct 2016
Reputation:
0
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);
(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.
(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.
-
Martin7483
Crossfire CMS
-
Posts: 373
Threads: 14
Joined: Sep 2015
Reputation:
20
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
)
)
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.
-
Martin7483
Crossfire CMS
-
Posts: 373
Threads: 14
Joined: Sep 2015
Reputation:
20
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.
|