Adding CSV data into a MySQL Database - arrays, implodes, explodes!

do you want to populate your database only one time ?

SQLyog provides an easy way to upload cvs files. Download the community edition :

http://www.webyog.com/en/downloads.php

The size of my file varies, however the text file I am using will be usually be around 100KB.  The test file size I am using is 11KB.

us111 - I do only want to populate my database once per Text file.  It will be a daily occurence though.

When the size of the file is less than the amount php is allowed to use, then you could possibly go with:

$parts = explode("~", file_get_contents("mycsvfile.csv");

$numberOfElementsPerRow = 8;

ok daily.
Do you erase all the content or just replace records which has been modified ? if you erase be careful you could loose some id.

Otherwise have a look at :
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Note to self: Remember to not press CTRL-S in QuickEE..

When the size of the file is less than the amount php is allowed to use, then you could possibly go with:

$parts = explode("~", file_get_contents("mycsvfile.csv");

$numberOfElementsPerRow = 8;

while(count($parts) > $numberOfElementsPerRow) {
  $record = array_slice($parts,0,$numberOfElementsPerRow);
 
  $record = array_map($record, "mysql_real_escape_string");
 
  $parts = array_slice($parts, $numberOfElementsPerRow);

  $query = 'INSERT INTO '.$table
          .' (field1,field1,field3,field4,field5,field6,field7,field8)'
          .' VALUES("'
          implode('","', $record)
          .'")'
  echo '<pre>Query: ' . $query . '</pre>';
}

Kind regards

-r-

Roonaan, this is very close to working i feel, I have the following on my page where $parts is the content of the file...  Do I need to be changing "mysql_real_escape_string"?

ConnDB contains my Connection string to the DB.  I am currently getting a blank screen when loading the info with no MySQL errors, so I am struggling to see what is wrong.

---

$parts = $contents;

$numberOfElementsPerRow = 23;

while(count($parts) > $numberOfElementsPerRow) {
  $record = array_slice($parts,0,$numberOfElementsPerRow);
 
  $record = array_map($record, "mysql_real_escape_string");
 
  $parts = array_slice($parts, $numberOfElementsPerRow);
 
      mysql_select_db($database_connDB, $connDB);
  $query = 'INSERT INTO bp_export (ref_id, order_number, date, name, company_name, address1, address2, towncity, county, na1, postcode, deliveryname, telephone, companybackup, na2, items, na3, email, na4,
bagsize, weight, deliveryinstructions, na5) VALUES ("' . implode('","', $record). '")';
$Result = mysql_query($query, $connDB) or die(mysql_error());  
echo '<pre>Query: ' . $Result . '</pre>';
 

}

I think that it is not working because you removed the explode statement. Or is $content an array?

$content is the CSV file - so not an array just the content itself.

In full (minus my connection details) my file is as follows...

$contents = file_get_contents("bpdata.txt", "r");
$parts = $contents;

$parts = explode("~", $parts);

$numberOfElementsPerRow = 23;

while(count($parts) > $numberOfElementsPerRow) {
  $record = array_slice($parts,0,$numberOfElementsPerRow);
 
  $record = array_map($record, "mysql_real_escape_string");
 
  $parts = array_slice($parts, $numberOfElementsPerRow);
 
      mysql_select_db($database_connDB, $connDB);
  $query = 'INSERT INTO bp_export (ref_id, order_number, date, name, company_name, address1, address2, towncity, county, na1, postcode, deliveryname, telephone, companybackup, na2, items, na3, email, na4,
bagsize, weight, deliveryinstructions, na5) VALUES ("' . implode('","', $record). '")';
$Result = mysql_query($query, $connDB) or die(mysql_error());  
echo '<pre>Query: ' . $Result . '</pre>';
 
}

With the above I get the following errors but I do not understand these :S

Warning: array_map() [function.array-map]: The first argument, 'Array', should be either NULL or a valid callback in /usr/local/www/vhosts/tv-wall-brackets.co.uk/httpdocs/BPData/read_file.php on line 82

Warning: implode() [function.implode]: Bad arguments. in /usr/local/www/vhosts/tv-wall-brackets.co.uk/httpdocs/BPData/read_file.php on line 88
Column count doesn't match value count at row 1

Any ideas?