help with php code to insert data into mysql db from a csv.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
62microbus
Forum Newbie
Posts: 1
Joined: Thu Oct 16, 2003 9:54 am
Location: Cambs, UK
Contact:

help with php code to insert data into mysql db from a csv.

Post by 62microbus »

Hi

I have a client who has a website that uses a mysql database which is updated from a MS Access db via a csv file.
I want to add some fileds to the database's but when i do the code does not insert the new rows.

Am i right in thinking if i add OR $c==27 in the if statment it will solve my problem.
Thanks guys.

Code: Select all

// Initialise database
include "../initialise.php";
if ($Parlours=="on") { 
  echo "<P>Imprting Parlours:</P>\n"; 
  $row = 0;
  //this line may need to be altered to reflect server's file system
  $fp = fopen ("../csv/Parlours.csv","r");
  $deleteres=mysql_query("DELETE from Parlours");
  //read in each line from csv file 
  while ($data = fgetcsv ($fp, 1000, "¬")) &#123;
     $num = count ($data);
     $qry="INSERT INTO Parlours VALUES(""";
     //for each record to be input, contruct query
     for ($c=0; $c<$num; $c++) &#123;
       $imported&#1111;$row]&#1111;$c]=$data&#1111;$c];
       if ($c==5 OR $c==6 OR $c==7 OR $c==8 OR $c==11 OR $c==15 OR $c==19 OR $c==20 OR $c==21 OR $c==22 OR $c==23 OR $c==24 

OR $c==25 OR $c==26 OR $c==27) &#123;
         if ($imported&#1111;$row]&#1111;$c]==1) &#123;
           $imported&#1111;$row]&#1111;$c]="yes";
         &#125;
         else &#123;
           $imported&#1111;$row]&#1111;$c]="no";
         &#125;
       &#125;
       if ($c==11 OR $c==12 OR $c==13) &#123;
         $imported&#1111;$row]&#1111;$c]=chop($imported&#1111;$row]&#1111;$c]);
       &#125;
       $qry=$qry.","".$imported&#1111;$row]&#1111;$c].""";
     &#125;
     $qry=$qry.")";
     //print $qry."\n";  
     //print "\n";   
	 $insertres=mysql_query($qry);
     $row++;
   &#125;
  fclose ($fp);  
&#125;
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post by xisle »

just a suggestion, for data input I use
http://www.mysql.com/doc/en/LOAD_DATA.html
for example..

Code: Select all

$query="LOAD DATA INFILE '&#123;$path&#125;' INTO TABLE &#123;$tablename&#125;(col1,col2,...)  
IGNORE 1 LINES 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'";
$result=mysql_query($query) or die(mysql_error());
tstimple
Forum Commoner
Posts: 53
Joined: Wed Jan 21, 2004 10:12 pm

Post by tstimple »

Hey Xisle,

I saw this bit of code while searching for "LOAD DATA INFILE"

I see the "IGNORE LINES 1"
I assume this will ignore the first row in the text (csv) file.

Can you also "IGNORE COLUMNS #, #" or somthing like that if you want to bypass (NOT LOAD) certain values from the csv file???
Post Reply