Page 1 of 1

Insert data into table from a textfile

Posted: Mon Aug 09, 2010 10:03 am
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());

}


Re: Insert data into table from a textfile

Posted: Mon Aug 09, 2010 3:06 pm
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?

Re: Insert data into table from a textfile

Posted: Mon Aug 09, 2010 7:13 pm
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??

Re: Insert data into table from a textfile

Posted: Tue Aug 10, 2010 3:17 am
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

Re: Insert data into table from a textfile

Posted: Tue Aug 10, 2010 9:33 am
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

Re: Insert data into table from a textfile

Posted: Tue Aug 10, 2010 11:56 am
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.