[SOLVED] batch insert .txt into MYSQL field ?

[amwd07]

Hello

not sure if this is possible

I have 100 text documents with decriptions for products.

rather than copying & pasted 1by1 I herd about the LOAD DATA INFILE command

idealy I need a php script to do this: all text files are named the same as the model field in my product db

 

for example: model001 / model002 / model003      etc.

uses model001.txt / model002.txt / model003.txt  etc.

 

is this possible to do within php  ???

[.josh]

not really a sql guru so I don't know if using LOAD DATA INFILE will be any help, but here's one thing you can do:

 

Make a table with product name and description columns.  For example lets call the table products and the columns product and description.  I assume you will want to make the product column type varchar and the description blob or something.  Also add an id column called product_id that's auto incremented, because you will probably make use of that later.  Put all the files in the same directory as this script, and then run the script.

 

runme.php

<?php

// connect to and select db. change it to your info
$conn = mysql_connect('localhost','dbusername','dbpassword') or die(mysql_error());
$db = mysql_select_db('dbname', $conn) or die(mysql_error());

// for each file...
foreach(glob("*.txt") as $filename) {
   // get contents of file into a string
   $content = file_get_contents($filename);
   // insert current file into table
   $sql = "INSERT INTO products (product_id, product, description) VALUES ('', '$filename', '$content')";
   $result = mysql_query($sql, $conn) or die(mysql_error());
} // end foreach filename

?>

[paul2463]

I went a slightly different way as I assumed he already had the product table set up with the model names already filled in

 

<?php
$query = "SELECT * FROM product";
$result = mysql_query($query) or die ("Error in query" . mysql_error());
while($row = mysql_fetch_assoc($result))
//for each entry in the product table
{
 	//get the name of the model
 	$file = $row['model'];
 	//use that name and create a filename
	$filename = "$file.txt";
	// read into a string the file contents
	$contents= file_get_contents($filename);
	//write it back into the table in the description column - whatever it is called
	$updateQuery = "UPDATE product SET description = '$contents' WHERE model = '$file'";
	mysql_query($updateQuery) or die ("Error in UpdateQuery" . mysql_error());
}	
?>

 

[amwd07]

These are both brilliant suggestions will try these & post back thanks for your help 

I prefer to use foreach but you are right I already have the model names filled in so will try to merge the 2 codes

 

many thanks

[amwd07]

I have tried using foreach but can't get this working

so have gone with the second method with a few small alterations unfortuntly this still doesn't work  ???

I hope someone can have a quick look at this failed to open stream

http://dev-shops.7serve.net/phoenix-healthcare/globnew.php

 

<?php

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname);


$query = "SELECT * FROM product_test";
$result = mysql_query($query) or die ("Error in query" . mysql_error());
while($row = mysql_fetch_assoc($result))
//for each entry in the product table
{
 	//Fetch Model
 	$file = $row['model'];
	//Path to txt files
	$dir 	= './shortdesc';
 	//Use Model and create a filename
	$filename = "$dir/$file.txt";
	// read into a string the file contents
	$contents = file_get_contents($filename);
	//write it back into the table in the description column - whatever it is called
	$updateQuery = "UPDATE product_test SET description_short = '$contents' WHERE model = '$file'";
	mysql_query($updateQuery) or die ("Error in UpdateQuery" . mysql_error());
}
?>	

[amwd07]

Sorry this does actually work.

it's only displaying the erros for the ones with no descriptions.

 

is there away to run only when model.txt exist ?

[.josh]

<?php

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname);


$query = "SELECT * FROM product_test";
$result = mysql_query($query) or die ("Error in query" . mysql_error());
while($row = mysql_fetch_assoc($result))
//for each entry in the product table
{
 	//Fetch Model
 	$file = $row['model'];
	//Path to txt files
	$dir 	= './shortdesc';
 	//Use Model and create a filename
	$filename = "$dir/$file.txt";
	// read into a string the file contents
	$contents = (file_exists($filename))? file_get_contents($filename) : '';
	//write it back into the table in the description column - whatever it is called
	$updateQuery = "UPDATE product_test SET description_short = '$contents' WHERE model = '$file'";
	mysql_query($updateQuery) or die ("Error in UpdateQuery" . mysql_error());
}
?>	

[amwd07]

thanks