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