build query for active record insert - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: build query for active record insert (/showthread.php?tid=1782) |
build query for active record insert - El Forum - 06-26-2007 [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( Code: function read_excel_file($fileName) { Code: function insert_data($excelData, $listingDate, $active) { 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) build query for active record insert - El Forum - 06-27-2007 [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... build query for active record insert - El Forum - 06-27-2007 [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. build query for active record insert - El Forum - 06-27-2007 [eluser]Grahack[/eluser] as you did previously: Code: for ($i = 0; $i <= count($excelData)-1; $i++) 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 build query for active record insert - El Forum - 06-27-2007 [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++) { build query for active record insert - El Forum - 06-28-2007 [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. build query for active record insert - El Forum - 06-28-2007 [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. build query for active record insert - El Forum - 06-28-2007 [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++) { Code: db->query("INSERT INTO mytable (title, name, date) VALUES build query for active record insert - El Forum - 06-29-2007 [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. build query for active record insert - El Forum - 06-29-2007 [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 "; |