Insert data into table from a textfile

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
laanes
Forum Newbie
Posts: 7
Joined: Thu Jan 28, 2010 4:38 am

Insert data into table from a textfile

Post by laanes »

I want to update products every day from a text file.

This script should help me to do that but it does not.

Would anybody help me to get this right?

Code: Select all


$array=file("/mypath/file.txt");

$rCount=0;

	foreach($array as $value)
	        {

		mysql_query("INSERT INTO tempProducts VALUES (".trim($value).")" or die(mysql_error());
		$rCount++;

		}

$numRecords=mysql_result(mysql_query("SELECT COUNT(PCode) as Num FROM tempProducts"),0);

if($numRecords==$rCount)
	{

$sql = "TRUNCATE TABLE products";

mysql_query($sql) or die(mysql_error());

$putProducts = "INSERT INTO products SELECT * FROM tempProducts";

mysql_query($putProducts) or die(mysql_error());

}

mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Insert data into table from a textfile

Post by mikosiko »

which part is not working?...
are you connecting to the DB?.... I don't see any related code to that....
did you test that $numrecords effectively has some value? or that $rCount is not zero?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Insert data into table from a textfile

Post by califdon »

I'm confused by your code. You're truncating the temp table, which deletes all records, THEN using it as the source for an insert to the permanent table. Doesn't make any sense. Did you mean to empty the temp table BEFORE acquiring the data from the text file??
laanes
Forum Newbie
Posts: 7
Joined: Thu Jan 28, 2010 4:38 am

Re: Insert data into table from a textfile

Post by laanes »

The idea is to create a temporary table that loads data from a text file. Then empty the products table and insert new data into it from the temporary table. Then at the end delete the temporary table because it is only needed for the data transfering process.

The script fails after the line:
$array=file("/the/absolute/path/of/the/file/located/in/the/domain/root/folder/products.txt");

So I guess it can create the temporary table and put the data from the text file into the array variable but fails when starts to insert data into the products table.

The whole script looks like this:

Code: Select all

<?php
	@set_time_limit(10);
	mysql_connect("host", "username", "password");
	mysql_select_db("databasename");
	mysql_query("DROP TABLE IF EXISTS tempProducts") or die(mysql_error());
	mysql_query("CREATE TABLE tempProducts (`productId` int(11) NOT NULL,
  `disabled` tinyint(1) NOT NULL default '0',
  `productCode` varchar(60) collate utf8_unicode_ci default NULL,
  `quantity` int(16) NOT NULL default '1',
  `description` text collate utf8_unicode_ci,
  `image` varbinary(250) default NULL,
  `noImages` int(11) NOT NULL default '0',
  `price` decimal(30,2) NOT NULL default '0.00',
  `name` varchar(250) collate utf8_unicode_ci default NULL,
  `cat_id` int(16) NOT NULL default '0',
  `popularity` bigint(64) NOT NULL default '0',
  `sale_price` decimal(30,2) NOT NULL default '0.00',
  `stock_level` int(11) NOT NULL default '0',
  `stockWarn` tinyint(1) NOT NULL default '0',
  `useStockLevel` int(11) NOT NULL default '1',
  `digital` int(11) NOT NULL default '0',
  `digitalDir` varchar(255) collate utf8_unicode_ci default NULL,
  `prodWeight` decimal(10,3) default NULL,
  `taxType` int(11) default '1',
  `tax_inclusive` tinyint(1) NOT NULL default '0',
  `showFeatured` tinyint(1) unsigned NOT NULL default '1',
  `prod_metatitle` text collate utf8_unicode_ci,
  `prod_metadesc` text collate utf8_unicode_ci,
  `prod_metakeywords` text collate utf8_unicode_ci,
  `eanupcCode` bigint(17) unsigned default NULL,
  `date_added` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `seo_custom_url` text collate utf8_unicode_ci,
  `testfield` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`productId`),
  KEY `popularity` (`popularity`),
  KEY `cat_id` (`cat_id`),
  FULLTEXT KEY `fulltext` (`productCode`,`description`,`name`)) 
	
	ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;") 
	
	or die(mysql_error());
	
	$array=file("/the/absolute/path/of/the/file/located/in/the/domain/root/folder/products.txt");
	$rCount=0;
	foreach($array as $value)
	{
		mysql_query("INSERT INTO tempProducts VALUES (".trim($value).")" or die(mysql_error());
		$rCount++;
		
	}
	

	$numRecords=mysql_result(mysql_query("SELECT COUNT(productId) as Num FROM tempProducts"),0);
	if($numRecords==$rCount)
	{
		// truncate the products table
		$sql = "TRUNCATE TABLE products";
		// run the first query to clear table
		mysql_query($sql) or die(mysql_error());

		// Setup query to import the new data into the products table
		$putProducts = "INSERT INTO products SELECT * FROM tempProducts";

		// Run the query
		mysql_query($putProducts) or die(mysql_error());

		//Delete the tempProducts table
		mysql_query("DROP TABLE IF EXISTS tempProducts") or die(mysql_error());
			
		echo "products table now updated";
	}
	
?>
Best regards,
laanes
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Insert data into table from a textfile

Post by mikosiko »

Some observations:

- I don't see the need to delete/create the temp table each time (2 DROPS + 1 CREATE). just create the table one time and TRUNCATE it at the beginning of your procedure... or use a memory temporary table.

- You said the your code fail after
$array=file("/the/absolute/path/of/the/file/located/in/the/domain/root/folder/products.txt");

how do you know that?... did you try to per example inspect what your $array contain after that? (using var_dump per example?)...

and a few questions to you in my previous post as no been answered....

Miko
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Insert data into table from a textfile

Post by califdon »

laanes wrote:The idea is to create a temporary table that loads data from a text file. Then empty the products table and insert new data into it from the temporary table. Then at the end delete the temporary table because it is only needed for the data transfering process.
Sorry, I didn't read it carefully enough.

I agree with mikosiko on just truncating the temp table, not dropping and creating each time.
Post Reply