Auto update feeds...

[Lee_K_00]

Tweet

Ok so I have my database set up with all the different tables that I want to retrieve data from and I know how to upload a CSV file into the table manually. But how can I go about doing this automatically?

I know I need to set up a Cron Job of some sort, but set it up with what exactly? A script?
Basically what I want is for it to download the CSV files and import them into my database. But I havn't a clue where to start! Anyone got any links or any help on this? Google doesn't seem to throw up a great deal!

Thanks in advance!

[Lee_K_00]

Am I right in thinking that I need to use wget to download the required CSV files, gzip to unzip them, and store them within the server. Then a php script to import the CSV files into the database. And have them run using a cron?

[scriptmonkey]

Basically what I want is for it to download the CSV files and import them into my database.

Does your server allow fopen or will you be using cURL?

Do you have phpmyadmin access to the database?

[scriptmonkey]

This works for uncompressed CSV files using fopen:

$url = 'yourcsvfileurl';
$filename = 'temp.csv';
$newfname = $filename;

$file = fopen ($url, "rb");
if ($file) {
  $newf = fopen ($newfname, "wb");

  if ($newf)
  while(!feof($file)) {
    fwrite($newf, fread($file, 4096 ), 4096 );
  }
}

if ($file) {
  fclose($file);
}

if ($newf) {
  fclose($newf);
} 

$hostname_dbase = "dbasehost";
$database_dbase = "dbasename";
$username_dbase = "dbaseusername";
$password_dbase = "dbasepassword";
$dbase = mysql_connect($hostname_dbase, $username_dbase, $password_dbase) or trigger_error(mysql_error(),E_USER_ERROR);

mysql_select_db($database_dbase, $dbase);


$sql = "LOAD DATA LOCAL INFILE 'temp.csv' REPLACE INTO TABLE tablename FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\' " ;
mysql_query($sql, $dbase) or die(mysql_error()); 

[scriptmonkey]

This works the same as above using cURL:

$url = "yourcsvfileurl";

 $path = 'temp.csv';
    $fp = fopen($path, 'wb');
    $ch = curl_init($url);
    curl_setopt($ch, CURLOPT_FILE, $fp);
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
    curl_setopt($ch, CURLOPT_HTTPAUTH, CURLAUTH_ANY);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_FAILONERROR, true);
    $data = curl_exec($ch);
    curl_close($ch);
     file_put_contents($path, $data);
    fclose($fp);


$hostname_dbase = "dbasehost";
$database_dbase = "dbasename";
$username_dbase = "dbaseusername";
$password_dbase = "dbasepassword";
$dbase = mysql_connect($hostname_dbase, $username_dbase, $password_dbase) or trigger_error(mysql_error(),E_USER_ERROR);

mysql_select_db($database_dbase, $dbase);


$sql = "LOAD DATA LOCAL INFILE 'temp.csv' REPLACE INTO TABLE tablename FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\' " ;
mysql_query($sql, $dbase) or die(mysql_error()); 

[scriptmonkey]

If you need to unzip, then here's the best method:

Unzip files with PHP

[Lee_K_00]

Thanks for the replies! It's much appreciated. I have access to phpMyAdmin, but not sure about cURL or fopen. Is there any way of finding out which 1 is on my server?

[Lee_K_00]

Well I managed to find out that cURL is installed on the server so I presume the above script should do the job.

1 more thing, sorry to be a pain but would you mind explaining what each part of the script does? I want to learn this stuff rather than just copy and paste it.

Thanks!

[scriptmonkey]

Sure.

cURL stuff

$url and $path should be self explanatory

$fp = fopen($path, 'wb'); - sets $fp to open the file $path for write (w) binary (b)

$ch = curl_init($url); - set $ch to open a cURL session with the file found at $url

Rather than go through all the cURL options, here's a link to the page: PHP: curl_setopt - Manual

$data = curl_exec($ch); - execute the opened cURL session and store the stream as $data

curl_close($ch); - as it says.

file_put_contents($path, $data); - write the stream in $data to the file in $path

Database stuff

$hostname_dbase = "dbasehost";
$database_dbase = "dbasename";
$username_dbase = "dbaseusername";
$password_dbase = "dbasepassword";
$dbase = mysql_connect($hostname_dbase, $username_dbase, $password_dbase) or trigger_error(mysql_error(),E_USER_ERROR);

mysql_select_db($database_dbase, $dbase);

Standard definition of a mysql database connection setting host, username, password and database names.

$sql = "LOAD DATA LOCAL INFILE 'temp.csv' REPLACE INTO TABLE tablename FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\' " ;
mysql_query($sql, $dbase) or die(mysql_error());

Providing you have a unique index in your table (for awin, aw_product_id is the one) then this will write the csv into your table, writing new rows where the product doesn't exist and replacing the ones that do exist (for price changes etc.)

Tony

[Lee_K_00]

Thanks man this should be a great help! I'll let you know how I get on with it...

How did you learn about all this stuff? A book? College? I'm just curious really, I really want to get up to scratch with all this but not sure where to start!

[scriptmonkey]

Self taught, I got some of these when I was a toddler.

Cuisenaire rods - Wikipedia, the free encyclopedia

[Lee_K_00]

Ah... I have managed to pick up a fair bit of HTML, CSS, and Photoshop, all self taught. Took an interest when I was in school around 10 - 12 year ago but sort of stopped as I got a bit older, and recently started trying to get back into it over the last 6 months. It's going well but PHP is a hard 1 to pick up imo. However I am determined!

[scriptmonkey]

There aren't so many things to learn in php.

In terms of displaying products then simple MySQL queries to get the data into an array coupled with do while loops are generally what most sites are populated with. Nail those two and the rest will come easily.

[Lee_K_00]

Quick question, i'm just going through this now. Had a few problems to begin with but managed to sort them out I think.

But my question is, at the minute I have all me tables within the database all with unique names to match their actual program name. Just to help me identify them really but the table name doesn't seem to be all that important. So I was thinking of just naming them table1, table 2, table 3 etc.

The idea is that in each of my pages (and there's going to be a few) where all my tables are linked together I can write the code linking table 1, 2, 3, 4, 5, 6, right up to table 20. Even though I only have 10 tables at the moment, but the idea is that as I add more retailers, rather than going back through each page adding a unique table name, all I have to do is add the table to the database giving it a name of table 11, table 12 and so on... So basically it saves me going back and editing each page individually as the code is already there.

My code is set up so that if nothing is found from a specific table then nothing is displayed, no error messages or anything. But what i'm worried about is will it effect the loading time by much if I include all these empty tables that don't currently exist? Sorry if i've not explained that very well...

[scriptmonkey]

That sounds like a route to madness (or at least future expansion issues). I'd have one table with your products in, set the product ID as primary unique key, set the merchant ID as another indexed field, then, have another table with the merchant details setting the merchant ID as the primary unique key (could cause problems if you're using more than one network), then you can manipulate the data by the queries you write, e.g. select * from product_table, merchant_table where merchant ID = 1234 Then you can reference items from both tables in your php without causing an unnecessary overhead.