[SOLVED] Populating databases from text files

[gerkintrigg]

Hi!!

I have a few text files with lists of items on them that I want to add to my shop database.

 

Each item is on a new line and I want each item to be added automatically into the database and check whether it already exists, if it does, don't enter it in, if it doesn't exist, add it.

 

Is there an easy way to do this? I think I can work most of it out, but it's the looping through the lines that I think might be the problem. would I use explode() ? If so, what would I use as the explode character?

 

Thanks.

[thedarkwinter]

yeah its pretty simple:

 

something like:

 

<?php

$infile = fopen("blah.txt","r");
while ($line = trim(fgets($infile,1024))) // make sure you trim it, otherwise you end up with whitespace on the last value
{
  $values = explode(",", $line); // where "," is the separator
  mysql_query("insert into table (x,y,z) values ($values[0].$values[1],$values[2]);
  // you can also add a mysql_query to see if the line already exists etc..
}

[gerkintrigg]

thanks for the trim advice, but the items don't have comma separators (or any separators) - just line breaks. That was my main question really.

[thedarkwinter]

oh okay, so then theres only one item per line? then just trim the line and use that. no need to explode

 

<?php

$infile = fopen("blah.txt","r");
while ($line = trim(fgets($infile,1024))) // make sure you trim it, otherwise you end up with whitespace on the last value
{
   $result = mysql_query("select * from table where x='$line'); // check if its already there
   if ( (!$result) or (mysql_num_rows($result)==) )
   { mysql_query("insert into table (x) values ($line); } // if not, then insert
}

[HuggieBear]

The first question should have been "in what format is your text file".  Please give an example of layout.

 

Regards

Huggie

[gerkintrigg]

Yup, thanks, thedarkwinter, it worked perfectly!

I also changed the populate script slightly to populate a members newsletter script...

here's what I had:

<?php
include '../includes/db.php';
$infile = fopen("file.txt","r");
$i=0;
$c=0;
while ($line = trim(fgets($infile,1024))) // make sure you trim it, otherwise you end up with whitespace on the last value
{
  $values = explode(",", $line); // where "," is the separator
  foreach($values as $varName => $value) { 
  //echo $value.'<br/>';
  $q="SELECT COUNT(*) FROM member WHERE Username='".$value."' ;";
$result = mysql_query( $q ) or die( 'MySQL Error Report: ' . mysql_error() );
$result = mysql_result( $result, 0 );
if ($result==0){
$c++;
	mysql_query("INSERT INTO `member` ( `First_Name` , `Surname` , `Username` , `Password` , `Email` , `PhoneNo` , `al1` , `al2` , `al3` , `al4` , `al5` , `DOB` , `Sex` , `RegDate` , `Notes` , `Points` , `Type` , `Subscriber` , `online` , `userid` )
VALUES (
'', '', '".$value."', '".$value."', '".$value."', '', '', '', '', '', '', '0000-00-00', 'male', NOW( ) , '', '0', 'General', 'Subscriber', 'n', NULL
);");
}
else {
echo $value.'<br/>';
$i++;
}
}
  //mysql_query("insert into table (x,y,z) values ($values[0].$values[1],$values[2]");
  // you can also add a mysql_query to see if the line already exists etc..
}
echo '<br/><br/>'.$c.' records added';
echo '<br/><br/>'.$i.' duplicate records found';

mysql_query("UPDATE `member` SET `Password` = userid WHERE `First_Name` ='';");
?>