Datafeed script problems. Please help

[steven420]

Hi, I have been having problems with this script:

<?php
 // Data feed variables
 $feedfile = '/2613_1600216_mp.txt.gz';
 $feed = gzopen($feedfile, 'r'); 
 $link = mysql_connect('localhost', 'username', 'password');
 if (!$link) {
 die('Could not connect: ' . mysql_error());
  }
 $DB = mysql_select_db ("testdatabase");
 if (!$DB) {
 die('Could not connect to testdatabase: ' . mysql_error());
  }
  mysql_query("drop table overstock"); 
 $table = mysql_query("CREATE TABLE overstock (   
  id INT NOT NULL,
 KEY prod_id (id),
  prod_name varchar(255) NOT NULL, 
  sku varchar(40) NOT NULL,
 prim_category varchar(50) NOT NULL,
 prod_url varchar(200) NOT NULL,
 prod_desc blob NOT NULL,
 price INT NOT NULL,
 col_id INT NOT NULL AUTO_INCREMENT ,
 PRIMARY KEY (col_id)
 )
  ");
 if (!$table) {
 die('Could not create table: ' . mysql_error()) ;
  } 
while($data = fgetcsv($feed, 3000)) { 
// Column variables
 $id = addslashes($data[1]);
 $prod_name = addslashes($data[2]);
 $sku = addslashes($data[3]);
 $prim_category = addslashes($data[4]);
 $prod_url = addslashes($data[6]);
 $prod_desc = addslashes($data[9]);
 $price = addslashes($data[14]);
 $reccount = 0;
$insert = mysql_query ("insert into overstock( id, 
prod_name,
 sku, 
prim_category, 
prod_url, 
prod_desc, 
price)
    values ('$id', 
     '$prod_name', 
    '$sku', 
    '$prim_category', 
    '$prod_url', 
    '$prod_desc', 
    '$price')")
  or die(mysql_error());
 $reccount++; 
     }
gzclose ($feedfile);
echo 'Connected successfully<br>';
echo 'Found database';
mysql_close($link);
?> 

The data isn't going into the table on my database. All the names are right and the script says that every thing was successful but when I check to see if there is any data in the table there is none. Please help me, I don't know what to do.

[wheelie34]

Hi
I have had problems with similar methods before, its usually the array thats mixed up, IF a value isnt present it add nothing at all.

Try printing to screen the elements you want to add first to see if they are all there, you will probably find one missing.

print "$id";#etc

HTH

[mcibor]

Hi Steven,

welcome to WebMastersWorld!

Instead of dropping the table, you can erase it:

mysql_query("TRUNCATE TABLE overstock");
you don't have to recreate it then.

This line I would change from
$id = addslashes($data[1]);

to
$id = (int)$data[1];

Moreover don't use addslashes, use mysql_real_escape_string [pl.php.net] instead.

And use Wheellie's suggestion

Have fun with PHP
Michal

[steven420]

Hi, thanks for the help. I'm going to go through the script and rewrite it from scratch and try and find any and all problems. I found this script and was trying to adjust it to do what I want. I will try all the suggestions.

Thanks again,
Steven.

[steven420]

Ok I am still having problems. I have re-written the script and the problem seems to be happening when I try to insert data into a table. Up until "$loop" there were no problems. When I run the script no messages are displayed. Please help and thank you for all the help so far. Here is the script:

<?php
 $conn = mysql_connect('localhost', 'username', 'password') or die ("could not connect to server: " . mysql_error());
 if ($conn) {
 echo "connected to server<br>";
 }
$database = mysql_select_db('testdatabase', $conn);
 if (!$database) {
 die('Could not connect to testdatabase: ' . mysql_error());
 }
 if ($database) { 
 echo "connected to database<br>";
 }
$file = gzopen("2613_1600216_mp.txt.gz","r");
 if (!$file) {
 die ('could not read file: ' . mysql_error());
 }
$rownum = 0;
 $loop =while ($data = fgetcsv($file, 3000, "¦")) {
 if ($rownum > 0) {
 $name = mysql_real_escape_string($data[0]);
 if (!$name) {
 die ('problem with data?: ' . mysql_error());
 } 
 $sql = mysql_query("insert into overstock (set name = '$name')");
 if (!$sql) {
 die ('could not insert: ' . mysql_error());
 }
}
  }
if (!$loop) {
die ('could not run loop: ' . mysql_error());
 }
$gz_close = gzclose($file);
 if (!$gz_close) {
 die ('could not close gz: ' . mysql_error());
 }
 $conn_close = mysql_close($conn);
 if (!$conn_close) { die( "could not close connection: " . mysql_error());}
?>

[mcibor]

OK. So you have a few mistakes:

die ('could not read file: ' . mysql_error()); - in this line the mysql error will not help you, as it is the problem with file, not db

there's no need to assign loop to variable
$loop =while ($data = fgetcsv($file, 3000, "¦")) {

mysql_query("insert into overstock (set name = '$name')");
query is wrong

Try this:

<?php
$conn = mysql_connect('localhost', 'username', 'password') or die ("Could not connect to server: " . mysql_error());

echo "connected to server<br>"; //you are connected, otherwise die will stop the script.

$database = mysql_select_db('testdatabase', $conn) or die('Could not connect to testdatabase: ' . mysql_error());

echo "connected to database<br>"; //the same here

$file = "2613_1600216_mp.txt.gz";
$fileHandle = gzopen($file, "r") or die ("Could not read file: $file_to_open");

$rownum = 0;
while ($data = fgetcsv($fileHandle, 3000, "¦")) {
if ($rownum > 0) { // do you want to omit first row?

if (!($name = mysql_real_escape_string($data[0]))) continue;//so that the loop will run further
$sql = "INSERT INTO `overstock` (name) VALUES ('$name')";
if(!($query = mysql_query($sql))) {
die ("Could not insert data with query $sql: " . mysql_error());
}
$rownum++;
}

gzclose($fileHandle);
mysql_close($conn); //it's not so important to close files and mysql connections, as the php server will take care of that on it's own. At least not important to check if they are closed.
?>

Hope this works for you!
Michal