inserting data into a table from a txt file

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?

$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());

}

Kind regards,
laanes

ixalmida

Do you need to validate or normalize data? If not, why not just run a "LOAD DATA" statement?

For example...

$query = "LOAD DATA INFILE '/folder/file.txt' INTO TABLE table_name
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
  LINES TERMINATED BY '\n' (field1, filed2, field3)";

Just change the comma to a \t if the text file is tab-delimited. This is far superior than running multiple INSERT queries. But if you must use an insert query, build a single, proper insert query.

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

// Build your insert string
$insert_string = "";
foreach($array as $value)
  $insert_string .= "(".$value."),";

// Remove extra comma
$insert_string = substr($insert_string, 0, -1);

// Run the query just once!
$query = "INSERT INTO tmpproducts (PCode) VALUES ".$insert_string;
mysql_query($query);

...

I'm assuming, based on your code, that these are numeric values. If not, then you need to add single-quotes around the values to make the insert query work.

laanes

ixalmida;10960462 wrote:
Do you need to validate or normalize data? If not, why not just run a "LOAD DATA" statement?

For example...
$query = "LOAD DATA INFILE '/folder/file.txt' INTO TABLE table_name
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
  LINES TERMINATED BY '\n' (field1, filed2, field3)";
Just change the comma to a \t if the text file is tab-delimited. This is far superior than running multiple INSERT queries. But if you must use an insert query, build a single, proper insert query.
$array=file("/mypath/file.txt");

// Build your insert string
$insert_string = "";
foreach($array as $value)
  $insert_string .= "(".$value."),";

// Remove extra comma
$insert_string = substr($insert_string, 0, -1);

// Run the query just once!
$query = "INSERT INTO tmpproducts (PCode) VALUES ".$insert_string;
mysql_query($query);

...
I'm assuming, based on your code, that these are numeric values. If not, then you need to add single-quotes around the values to make the insert query work.

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=php]<?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=InnoDB 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";
}

?>[/code]

Best regards,
laanes

laanes

ixalmida;10960462 wrote:
Do you need to validate or normalize data? If not, why not just run a "LOAD DATA" statement?

For example...
$query = "LOAD DATA INFILE '/folder/file.txt' INTO TABLE table_name
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
  LINES TERMINATED BY '\n' (field1, filed2, field3)";
Just change the comma to a \t if the text file is tab-delimited. This is far superior than running multiple INSERT queries. But if you must use an insert query, build a single, proper insert query.
$array=file("/mypath/file.txt");

// Build your insert string
$insert_string = "";
foreach($array as $value)
  $insert_string .= "(".$value."),";

// Remove extra comma
$insert_string = substr($insert_string, 0, -1);

// Run the query just once!
$query = "INSERT INTO tmpproducts (PCode) VALUES ".$insert_string;
mysql_query($query);

...
I'm assuming, based on your code, that these are numeric values. If not, then you need to add single-quotes around the values to make the insert query work.

Where exactly should the single quotes go in my script?

Regards,
laanes

berekin

For debbuging purposes after the firt line put print_r($array); to see if the data are stored in $array correctly. What is the exact error you get wheb script hangs? If it can instert in databse you should get mysql_error() error or warning...

berekin

I forgot .. maybe you txt file is to big to store data to array

laanes

berekin;10960508 wrote:
I forgot .. maybe you txt file is to big to store data to array

In that case, wouldn't it tell me that its out of memory or something?

laanes

berekin;10960507 wrote:
For debbuging purposes after the firt line put print_r($array); to see if the data are stored in $array correctly. What is the exact error you get wheb script hangs? If it can instert in databse you should get mysql_error() error or warning...

The worst thing is that is comes up with a white blank page. No errors 🙁

laanes

ixalmida;10960462 wrote:
Do you need to validate or normalize data? If not, why not just run a "LOAD DATA" statement?

For example...
$query = "LOAD DATA INFILE '/folder/file.txt' INTO TABLE table_name
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
  LINES TERMINATED BY '\n' (field1, filed2, field3)";
Just change the comma to a \t if the text file is tab-delimited. This is far superior than running multiple INSERT queries. But if you must use an insert query, build a single, proper insert query.
$array=file("/mypath/file.txt");

// Build your insert string
$insert_string = "";
foreach($array as $value)
  $insert_string .= "(".$value."),";

// Remove extra comma
$insert_string = substr($insert_string, 0, -1);

// Run the query just once!
$query = "INSERT INTO tmpproducts (PCode) VALUES ".$insert_string;
mysql_query($query);

...
I'm assuming, based on your code, that these are numeric values. If not, then you need to add single-quotes around the values to make the insert query work.

When I try the solution you recommend I will end up with something like this:

    [code=php]error_reporting(E_ALL);

    @set_time_limit(10);
mysql_connect("localhost", "Jeff", "timber5760");
mysql_select_db("stp_store_db");

$query = "LOAD DATA INFILE '/var/www/vhosts/swanseatimber.co.uk/httpdocs/stpwebsite.txt' 
REPLACE INTO TABLE CubeCart_inventory 
FIELDS TERMINATED BY ',' ESCAPED BY '\\' 
OPTIONALLY ENCLOSED BY '\"' (productId, 'productCode', 'description', 'image', 
price, 'name', cat_id, sale_price, taxType, tax_inclusive, noImages)";

mysql_query($query) or die(mysql_error()); 
echo("works");[/code]

And this leads me to an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"' (productId, 'productCode', 'description', 'image', price, 'name', cat_id, s' at line 3

johanafm

Field names should not be quotes. i.e. productCode is correct, 'productCode' is wrong. Field string literals on the other hand must be quoted.

laanes

johanafm;10960515 wrote:
Field names should not be quotes. i.e. productCode is correct, 'productCode' is wrong. Field string literals on the other hand must be quoted.

Which ones are string literals in the current code?

laanes

laanes;10960517 wrote:
Which ones are string literals in the current code?

Got the syntax right now.

Now it says:

Access denied for user 'Jeff'@'%' (using password: YES)

I know that my database details are correct. Why access denied then?

kind regards,
laanes

johanafm

Because, as your dbms tells you, you do not have the proper access rights. It could be that the user jeff doesn't exist, the password is incorrect, or
the user and pass are correct, but you lack access to the database or table, or
have access rights but not insert / update privileges.

When do you get the error message? On connect, on selecting db, on creating a table, on inserting data?

laanes

johanafm;10960520 wrote:
Because, as your dbms tells you, you do not have the proper access rights. It could be that the user jeff doesn't exist, the password is incorrect, or
the user and pass are correct, but you lack access to the database or table, or
have access rights but not insert / update privileges.

When do you get the error message? On connect, on selecting db, on creating a table, on inserting data?

How can I check that?

laanes

laanes;10960522 wrote:
How can I check that?

On inserting data.

laanes

johanafm;10960520 wrote:
Because, as your dbms tells you, you do not have the proper access rights. It could be that the user jeff doesn't exist, the password is incorrect, or
the user and pass are correct, but you lack access to the database or table, or
have access rights but not insert / update privileges.

When do you get the error message? On connect, on selecting db, on creating a table, on inserting data?

But maybe the query itself is not right?

ixalmida

Or, you may just need to change where the user can connect from. If the user's "Host" is set to 'localhost', for example, that means you can only connect to mysql from the mysql server. That's fine if the web server is on the same machine, but if you want to connect to mysql from a remote web server, for example, change it to the IP address of the web server. Or to connect from multiple locations, you can change Host to '%' (anywhere).

ixalmida

But maybe the query itself is not right?

The query is fine, but the user's permissions may not be. You also might want to check whether the user has permission to do updates or inserts. I sometimes make SELECT-only users with permissions only to certain custom views for clients that want to connect to the server via ODBC and pull data into their own systems.

laanes

ixalmida;10960528 wrote:
The query is fine, but the user's permissions may not be. You also might want to check whether the user has permission to do updates or inserts. I sometimes make SELECT-only users with permissions only to certain custom views for clients that want to connect to the server via ODBC and pull data into their own systems.

Well I can insert, update rows and fields through PHPMyAdmin. Does that mean I have all the privileges?

johanafm

Well, you might want to consult the MySQL manual first. E.g. it could be the "access denied" really only is for connecting to the db.
But to check where things go wrong, you could for example change your "or die(mysql_error())" to "or die ('1' . mysql_error())", "or die ('2' . mysql_error())" etc