?
Solved

Insert Array into MYsql using multiple insert

Posted on 2005-04-10
6
Medium Priority
?
772 Views
Last Modified: 2013-12-12
Hi Everyone.

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.

questions:
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)
or
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
0
Comment
Question by:mrwilde
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

by:matt_mcswain
ID: 13750636
You can insert everything at once. If the fieldnames are identical in both tables then it won't be much work.
You can insert into mysql like this:

INSERT INTO tablename (field1, field2, field3) VALUES ('val1', 'val2','val3') , ('val1', 'val2','val3') ,('val1', 'val2','val3') ,etc.

So if you have an array($arr) of associative arrays of all the rows as you have shown above you could do something like:

$sql = 'INSERT INTO tablename (';
$fieldNames = '';
$fieldValues = ' VALUES ';
for($i=0;$i<count($arr);$i++){
   $fieldValues .= '(';
   foreach($arr[$i] as $key=>$value)
   {
      if ($i == 0) $fieldNames .= $key.',';
      $fieldValues .= "'$value',";
   }
   $fieldValues = rtrim($fieldValues,',').'),';
}
$fieldNames = rtrim($fieldNames,',');
$fieldValues = rtrim($fieldValues,',');
$sql .= $fieldNames .') '.$fieldValues;
mysql_query($sql) or die(msyql_error().$sql);

This is untested, but you can try it or and it there's a sql error it will print it out along with the query.
So try to get the query in the format I post above, or post the query.

0
 
LVL 8

Expert Comment

by:_Marcel_
ID: 13750766
When you're looking at a speed, I guess it would be better to use LOAD DATA INFILE ( http://dev.mysql.com/doc/mysql/en/load-data.html ). With this function you can read a comma (or any symbol you want) seperated file into the database at once. This has the advantage that no checking is happening on your data (since it is coming from a database it should be okay and the integrity should also be okay). You have somewhat more of a hasle with creating a file with the values etc., but I've seen applications become more than ten times faster by this.
But again, when it is a small table, you'd probably don't want to bother.
0
 
LVL 1

Author Comment

by:mrwilde
ID: 13750976
the issue I have is that the table it is coming from contains between 3500 and 10500 lines and around 40 colums.

I have tried a few querys and get time out errors and errors Like "MYsql has gone"
I am very interested in the file opption Marcel has described. Could you possibly offer some more info as to how i create a file from the array then upload the file into MYsql
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 8

Accepted Solution

by:
_Marcel_ earned 2000 total points
ID: 13751096
To create a text file with tab-separated values that is enquoted:

$handle = @fopen('\var\tmp\insertfile.txt', 'w') or die('Cannot open file');
foreach ($arr as $arrline) {
   $txt = "\"" . implode("\"\t\"", $arrline) . "\"\n";
   fwrite($handle, $txt);
}
fclose($handle);

You then need the following SQL statement to insert the file:

LOAD DATA LOCAL INFILE '\var\tmp\insertfile.txt' REPLACE
    INTO TABLE tbl_name
    FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\'
    LINES TERMINATED BY '\n'

where tbl_name is the name of the table (duh). You can use IGNORE instead of REPLACE to ignore the lines that already exist (where REPLACE replaces them).
Also make sure you have the right to read the file (or more important: that mysql has the right to do it). Also make sure that the user using mysql has the right (database)rights to do this.
0
 
LVL 1

Author Comment

by:mrwilde
ID: 13758515
The code supplied was extremley usefull Thankyou. The only problem is i cannot make this query work

$query = "LOAD DATA LOCAL INFILE '../var/tmp/insertfile.txt' REPLACE
  INTO TABLE Items
  FIELDS TERMINATED BY '\t'
  ENCLOSED BY '\"'
  ESCAPED BY '\\'
  LINES TERMINATED BY '\n' ";

this the error
check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 6

something to do with single and double quotes i think. Any suggestions

0
 
LVL 8

Expert Comment

by:_Marcel_
ID: 13759653
Maybe some more escape characters:

$query = "LOAD DATA LOCAL INFILE '../var/tmp/insertfile.txt' REPLACE
  INTO TABLE Items
  FIELDS TERMINATED BY '\\t'
  ENCLOSED BY '\"'
  ESCAPED BY '\\\\'
  LINES TERMINATED BY '\\n' ";

I think MySQL expects the characters to be escaped, so you do not want PHP to expand them already.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question