[eluser]ocergyNohtna[/eluser]
ok, i'm still new to CI and a bit new to php. but i've made one post so far on this forum, and while it hasn't gotten me a solution yet, those that helped had good advice. i'm trying to figure out how to format an array for an active record insert. according to the docs, this is the general idea:
Code: $data = array(
'title' => 'My title' ,
'name' => 'My Name' ,
'date' => 'My date'
);
$this->db->insert('mytable', $data);
// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')
so, i'm reading an Excel file and creating an array of data that i'll be using in my sql insert. here's the function that reads the excel file and creates the array, only reason for showing this is so you can see how the array is built that is being passed to the next function (the one that will create my array for the active record insert); the return of this function ($items) is passed to the next function as $excelData
Code: function read_excel_file($fileName) {
set_time_limit(120);
$pathToExcelFile = './listings/'.$fileName.'.xls';
if(!file_exists($pathToExcelFile)) {
return 'Excel file does not exist.<br />';
} else {
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read($pathToExcelFile);
error_reporting(E_ALL ^ E_NOTICE);
$k = 0;
//$i = 2 because i don't want the top row of data since it's "headers" for the columns
for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
$item = $data->sheets[0]['cells'][$i][$j];
$item = str_replace('$', '', $item);
$item = str_replace('#', '', $item);
$items[$k][$j] .= $item;
}
if ($i == $data->sheets[0]['numRows']) {
break;
} else {
$k++;
}
}
}
return $items;
}
then here's where i'm trying to build my array to pass through the active record insert:
Code: function insert_data($excelData, $listingDate, $active) {
for ($i = 0; $i <= count($excelData)-1; $i++) {
$sql[$i] = array(
'uniqueId' => 'NULL',
'lotNum' => $excelData[$i][1],
'title' => $excelData[$i][2],
'description' => $excelData[$i][3],
'serialNum' => $excelData[$i][4],
'askPrice' => $excelData[$i][5],
'increment' => $excelData[$i][6],
'reserve' => $excelData[$i][7],
'image1' => $excelData[$i][8],
'image2' => $excelData[$i][9],
'image3' => $excelData[$i][10],
'image4' => $excelData[$i][11],
'image5' => $excelData[$i][12],
'image6' => $excelData[$i][13],
'ebayCatId' => $excelData[$i][14],
'lowEstimate' => $excelData[$i][15],
'highEstimate' => $excelData[$i][16],
'listingDate' => $listingDate,
'active' => $active
);
}
$result = $this->db->insert('items', $sql);
if ($result != FALSE) {
return TRUE;
} else {
return FALSE;
}
}
does this logic make any sense to anyone?? i just can't seem to to figure out how to correctly build the array. any ideas? suggestions? slaps in the face? (feels like one of those that's right under my nose....yet ever so elusive)
[eluser]Grahack[/eluser]
Didn't inverstigate deeply, but you'd better loop again to store your $sql[$i] arrays.
The array that you're trying to db->insert is (0=>array(...), 1=>array(...), ...) so AR tries to find the '0' column, the '1' column...
[eluser]ocergyNohtna[/eluser]
right. that makes sense. but how do i "loop again", as you put it, to make the array as it should be. thanks for the help.
[eluser]Grahack[/eluser]
as you did previously:
Code: for ($i = 0; $i <= count($excelData)-1; $i++)
{
$this->db->insert('items', $sql[$i]);
}
you'll have to implement your own checks
EDIT: I didn't transform your $sql array, it's just that it contains the arrays you have to pass to the AR function
Note: I would personaly prefer $i < count($excelData) in the for condition
[eluser]ocergyNohtna[/eluser]
hah, yeah $i < count...don't know what i was thinking when i wrote that. prolly wasn't thinking at all. thanks.
but if i do it this way:
Code: for ($i = 0; $i < count($excelData); $i++) {
$result = $this->db->insert('items', $sql[$i]);
}
isn't that going to make x calls of INSERT to the db (where x = count($sql)) as opposed to having all the data sent in one INSERT command? i haven't tried this method at all actually. i figure it would work, but didn't want to hammer the db like that. the count() of $sql could potentially be as high as 5000. :\ i'm new to a lot of this, but wouldn't that be a bit much for the db to have to handle? or would it actually be smarter to use this approach where the data is getting sent in smaller chunks to the db? thanks for all your input.
[eluser]Grahack[/eluser]
you're right, multiple call to db->insert
1) AFAIK, Active Record inserts don't handle multiple rows
2) I'm not a db expert so I can't tell you which one is faster and sweeter(!!!) for your db engine
What you can do is to compare the two ways using the benchmarking tool. Tell us how much time it takes to make 5000 calls to db->insert, and how much time it takes to build a giant query with 5000 rows to insert, and then throw it in the mouth of your db.
[eluser]ocergyNohtna[/eluser]
good idea.  i've wanted to do something with the benchmarking class. as soon as i figure out how to rebuild this array i'll do just that.
[eluser]efishant[/eluser]
Why not just put the db->insert inside of the loop and just re-use the $sql variable as a one dimensional array?
Code: for ($i = 0; $i <= count($excelData)-1; $i++) {
$sql = array(
'uniqueId' => 'NULL',
'lotNum' => $excelData[$i][1],
'title' => $excelData[$i][2],
'description' => $excelData[$i][3],
'serialNum' => $excelData[$i][4],
'askPrice' => $excelData[$i][5],
'increment' => $excelData[$i][6],
'reserve' => $excelData[$i][7],
'image1' => $excelData[$i][8],
'image2' => $excelData[$i][9],
'image3' => $excelData[$i][10],
'image4' => $excelData[$i][11],
'image5' => $excelData[$i][12],
'image6' => $excelData[$i][13],
'ebayCatId' => $excelData[$i][14],
'lowEstimate' => $excelData[$i][15],
'highEstimate' => $excelData[$i][16],
'listingDate' => $listingDate,
'active' => $active
);
$result = $this->db->insert('items', $sql);
}
It would be faster to use one query for all inserts:
Code: db->query("INSERT INTO mytable (title, name, date) VALUES
('My title1', 'My name', 'My date'),
('My title2', 'My name', 'My date'),
('My title3', 'My name', 'My date'),
('My title4', 'My name', 'My date'),
('My title5', 'My name', 'My date'),
('My title6', 'My name', 'My date'),
('My titleETC', 'My name', 'My date')");
[eluser]ocergyNohtna[/eluser]
[quote author="efishant" date="1183109423"]Why not just put the db->insert inside of the loop and just re-use the $sql variable as a one dimensional array?[/quote]
well, because as i stated before, i figure it will work but it will hit the db numerous times with an insert command. so i'm trying to get my array rebuilt so that it can be sent once to the db.
[eluser]efishant[/eluser]
as grahack mentioned, active records doesn't handle the insertion of multiple rows in one statement (if I'm wrong, please let me know!). How will rebuilding your array help you either way? Why not just build a string with the "INSERT INTO" statement given in the example above.
Here's my proposed solution. With this, you will only need to loop 1 array and you only call the DB once:
Code: $sql = "INSERT INTO mytable (uniqueId, lotNum, title, description, etc) VALUES ";
for ($i = 0; $i < count($excelData); $i++) {
$comma = ($i!=(count($excelData)-1))?",":"";
$sql .= "('NULL', {$excelData[$i][1]}, {$excelData[$i][2]}, {$excelData[$i][3]})$comma";
}
$result = $this->db->query($sql);
|
