Insert Array into MYsql using multiple insert
Posted on 2005-04-10
New and interesting problem. I am extracting data from very obscure db thru odbc and inserting the information into MYsql to be viewd on intanet.
what would be the fastest form of inserting the data into the mysql table. I have created my own " odbc_fetch_assoc() " function to return an array similar to MYsql.
Array ( [odbc_affected_rows] => 0 [ItemID] => 1 [IsInactive] => N [ItemName] => Polyclar QS [ItemNumber] => 17106 [QuantityOnHand] => 29.522000 [ValueOnHand] => 929.940000 [SellOnOrder] => 0.000000 [PurchaseOnOrder] => 0.000000 [ItemIsSold] => Y [ItemIsBought] => Y [ItemIsInventoried] => Y [IncomeAccountID] => 63 [ExpenseAccountID] => 30 [InventoryAccountID] => 138 [Picture] => [ItemDescription] => [UseDescription] => N [CustomList1ID] => 0 [CustomList2ID] => 0 [CustomList3ID] => 0 [CustomField1] => [CustomField2] => [CustomField3] => [BaseSellingPrice] => 40.000000 [PriceIsInclusive] => N [SellTaxCodeID] => 12 [SalesTaxCalcBasisID] => ASP [SellUnitMeasure] => Kg [SellUnitQuantity] => 1 [TaxInclusiveLastPurchasePrice] => 34.650200 [TaxExclusiveLastPurchasePrice] => 34.650200 [BuyTaxCodeID] => 12 [BuyUnitMeasure] => Kg [BuyUnitQuantity] => 1 [PrimarySupplierID] => 24 [SupplierItemNumber] => Polyclar QS - 11722245083 [MinLevelBeforeReorder] => 10.000000 [DefaultReoderQuantity] => 22.700000 [ChangeControl] => 0253900031 )
So now i need to extract the values and do an insert.
1) should i create and totalised array that contains a key for each entry then form a multiple MYsql insert(not really certain how to do that one)
2)Should do an insert for each array extracted
I am thinking that maybe the 1st option may be the better option as it is performing only one insert. Is one really big insert better than doing a lot of little inserts?
I hope this is explained clearly to understand