Automatically send data feed to Froogle

[mattmcb]

Hey everyone... this is a great module!

 

If anyone is interested, I modified the file to also work with Yahoo! Ad Central Product Submit.

 

Basically, the only real changes are that Yahoo! requires a CODE field as a unique identifier for the products you are submitting and of course the ftp server is different. For the CODE field I modifed the script to use the products_id from the products table. The filename is also different, it's data.txt for everyone and for all files.

 

The one other change that is causing some problems for me is that Yahoo! requires you to put the file in a specific directory on their server. I can get my file to their ftp site, but I can't get it into the directory so I need some help with that.

 

So far everything works, I can create the file and send it to the server just fine but need help getting it into the right directory.

 

If anyone would like to give this a try or if the author of this mod would like to include this in their contribution please let me know; and I still need help with the auto upload.

 

Thanks,

Matt

Edited January 1, 2006 by Vger

[dyland]

To change directory using PHP and FTP:

 

if (ftp_chdir($conn_id, $ftpdirectory )) {

echo "Current directory is now: " . ftp_pwd($conn_id) . "<BR>\n";

} else {

echo "Couldn't change directory on $ftpservername<BR>\n";

return false;

}

 

 

 

In fact I put the ftp stuff into a function to make it easier to extend:

 

function ftp_file( $ftpservername, $ftpusername, $ftppassword, $ftpsourcefile, $ftpdirectory, $ftpdestinationfile )

{

// set up basic connection

$conn_id = ftp_connect($ftpservername);

if ( $conn_id == false )

{

echo "FTP open connection failed to $ftpservername <BR>\n" ;

return false;

}

 

// login with username and password

$login_result = ftp_login($conn_id, $ftpusername, $ftppassword);

 

// check connection

if ((!$conn_id) || (!$login_result)) {

echo "FTP connection has failed!<BR>\n";

echo "Attempted to connect to " . $ftpservername . " for user " . $ftpusername . "<BR>\n";

return false;

} else {

echo "Connected to " . $ftpservername . ", for user " . $ftpusername . "<BR>\n";

}

 

if ( strlen( $ftpdirectory ) > 0 )

{

if (ftp_chdir($conn_id, $ftpdirectory )) {

echo "Current directory is now: " . ftp_pwd($conn_id) . "<BR>\n";

} else {

echo "Couldn't change directory on $ftpservername<BR>\n";

return false;

}

}

 

ftp_pasv ( $conn_id, true ) ;

// upload the file

$upload = ftp_put( $conn_id, $ftpdestinationfile, $ftpsourcefile, FTP_ASCII );

 

// check upload status

if (!$upload) {

echo "$ftpservername: FTP upload has failed!<BR>\n";

return false;

} else {

echo "Uploaded " . $ftpsourcefile . " to " . $ftpservername . " as " . $ftpdestinationfile . "<BR>\n";

}

 

// close the FTP stream

ftp_close($conn_id);

 

return true ;

}

[FlyingMonkey]

Hey everyone... this is a great module!

 

If anyone is interested, I modified the file to also work with Yahoo! Ad Central Product Submit.

 

Basically, the only real changes are that Yahoo! requires a CODE field as a unique identifier for the products you are submitting and of course the ftp server is different. For the CODE field I modifed the script to use the products_id from the products table. The filename is also different, it's data.txt for everyone and for all files.

 

The one other change that is causing some problems for me is that Yahoo! requires you to put the file in a specific directory on their server. I can get my file to their ftp site, but I can't get it into the directory so I need some help with that.

 

So far everything works, I can create the file and send it to the server just fine but need help getting it into the right directory.

 

If anyone would like to give this a try or if the author of this mod would like to include this in their contribution please let me know; and I still need help with the auto upload.

 

Thanks,

Matt

http://www.seekshopping.com

i could modify the code and release it as a contribution, if you want.

[mattmcb]

Here is the code that is slighlty modified. It just needs the ability to include an upload directory for Yahoo!'s Product Submit:

 

<?php
$OutFile = "../yahoo/data.txt"; //"CHANGEME-full-path-to-file-with-777-dir-and-file-permissions.fr-outfile.txt";
$destination_file = "data.txt"; ?//"CHANGEME-filename-to-upload-to-yahoo.txt";
$source_file = $OutFile;
$imageURL = 'http://seekshopping.com/catalog/images';
$productURL = 'http://www.seekshopping.com/catalog/product_info.php?products_id=';
$already_sent = array();

$home = "localhost";
$user="db user";
$pass="db pass";
$base="db name";

$ftp_server = "ftp.productsubmit.adcentral.yahoo.com";
$ftp_user_name = "yahoo user name";
$ftp_user_pass = "yahoo password";

$convertCur = false; //default = false
$curType = "USD"; // Converts Currency to any defined currency (eg. USD, EUR, GBP)
if($convertCur)
{
$productURL = 'http://www.seekshopping.com/catalog/product_info.php?currency=CURTYPE&products_id='; ?//where CURTYPE is your currency type (eg. USD, EUR, GBP)
}

if (!($link=mysql_connect($home,$user,$pass)))
{
echo "Error when connecting itself to the data base";
exit();
}
if (!mysql_select_db( $base , $link ))
{
echo "Error the data base does not exist";
exit();
}

$sql = "
select concat( '" . $productURL . "' ,products.products_id) AS product_url,
products_model , products_weight ,
manufacturers.manufacturers_name ,
products.products_id AS id,
products_description.products_name AS name,
products_description.products_description AS description,
FORMAT(products.products_price,2) AS price,
CONCAT( '" . $imageURL . "' ,products.products_image) AS image_url,
concat_ws( ' > ' , catdescparent.categories_name , catdesccurrent.categories_name ) AS category
FROM categories ,
products,
products_description,
categories_description as catdesccurrent,
products_to_categories


left join categories_description as catdescparent on ( catdescparent.categories_id = categories.parent_id )
left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id )

WHERE products.products_id=products_description.products_id
AND products.products_id=products_to_categories.products_id
AND products_to_categories.categories_id=categories.categories_id
AND catdesccurrent.categories_id = categories.categories_id
AND products.products_status != 0
ORDER BY
products.products_id ASC
";


//Check for any applicable specials for the corresponding products_id
$sql2 = "
SELECT
specials.products_id AS idS,
FORMAT(specials.specials_new_products_price,2) AS priceS
FROM
specials,
products
WHERE
specials.products_id=products.products_id
AND specials.status != 0
AND products.products_status != 0
ORDER BY
specials.products_id ASC
";

$_strip_search = array(
"![\t ]+$|^[\t ]+!m", // remove leading/trailing space chars
'%[\r\n]+%m'); // remove CRs and newlines
$_strip_replace = array(
'',
'');

$output = "code \t product_url \t name \t description \t price \t image_url \t category\n";

$result=mysql_query( $sql )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql = " . htmlentities($sql) );

//Specials Checker
$result2=mysql_query( $sql2 )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql2 = " . htmlentities($sql2) );
$row2 = mysql_fetch_object( $result2 );

//Currency Information
if($convertCur)
{
$sql3 = "
SELECT
currencies.value AS curUSD
FROM
currencies
WHERE currencies.code = '$curType'
";

$result3=mysql_query( $sql3 )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql3 = " . htmlentities($sql3) );
$row3 = mysql_fetch_object( $result3 );
}

while( $row = mysql_fetch_object( $result ) )
{
if ($already_sent[$row->product_url] == 1) continue; // if we've sent this one, skip the rest of the while loop

// reset the products price to our special price if there is one for this product
if( $row2->idS == $row->id ){
?$row->price = $row2->priceS;
?$previdS = $row2->idS;
?while ( $row2->idS == $previdS )
?{
?$row2 = mysql_fetch_object( $result2 ); ?//advance row in special's table
?}
}

if($convertCur)
{
?$row->price = $row->price * ?$row3->curUSD;
?$row->price = number_format($row->price, 2, '.', '');
}

$output .= 
$row->id . "\t" .
$row->product_url . "\t" .
preg_replace($_strip_search, $strip_replace, strip_tags( str_replace(">", "> ", $row->name) ) ) . "\t" .
preg_replace($_strip_search, $strip_replace, strip_tags( str_replace(">", "> ", $row->description) ) ) . "\t" .
$row->price . "\t" .
$row->image_url . "\t" .
$row->category . "\n";

$already_sent[$row->product_url] = 1;
}

if ( file_exists( $OutFile ) )
unlink( $OutFile );

$fp = fopen( $OutFile , "w" );
$fout = fwrite( $fp , $output );
fclose( $fp );

//Start FTP to Yahoo

// set up basic connection
$conn_id = ftp_connect($ftp_server);

// login with username and password
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

// check connection
if ((!$conn_id) || (!$login_result)) {
echo "FTP connection has failed!<BR>";
echo "Attempted to connect to $ftp_server for user $ftp_user_name<BR>";
exit;
} else {
echo "Connected to $ftp_server, for user $ftp_user_name<BR>";
}

// upload the file
$upload = ftp_put($conn_id, $destination_file, $source_file, FTP_BINARY);

// check upload status
if (!$upload) {
echo "FTP upload has <b>FAILED!</b> source: $source_file ?dest: $destination_file<BR>";
} else {
echo "Uploaded $source_file to $ftp_server as $destination_file<BR>";
}

// close the FTP stream
ftp_close($conn_id);

//End FTP to Yahoo
?>

 

 

Thanks,

Matt

Edited July 12, 2004 by The_Bear

[balloons88]

I am new to osCommerce. I am posting my froogle data feed file for someone to hopefully check. If you find any errors, could you PM me with the changes. Thanks in advance

 

PS: I also need help on where to put it once the file is correct.

 

 

<?php

$OutFile = "C:\Documents and Settings\Jeff\My Documents\NationwideParty.com\Froogle"; //"CHANGEME-full-path-to-file-with-777-dir-and-file-permissions.fr-outfile.txt";

$destination_file = "your-fr-outfile.txt"; //"CHANGEME-filename-to-upload-to-froogle.txt" ;

$source_file = $OutFile;

$imageURL = 'http://www.nationwideparty.com/store/images/';

$productURL = 'http://www.nationwideparty.com/product_info.php?products_id=';

$already_sent = array();

 

$home = "localhost" ;

$user="nationwi";

$pass="partyworld";

$base="nationwi_osc";

 

$ftp_server = "hedwig.google.com" ;

$ftp_user_name = "I Dont Think So";

$ftp_user_pass = "I Dont Think So ";

 

$convertCur = false; //default = false

$curType = "USD"; // Converts Currency to any defined currency (eg. USD, EUR, GBP)

if($convertCur)

{

$productURL = 'http://www.nationwideparty.com/product_info.php?currency=CURTYPE&products_id='; //where CURTYPE is your currency type (eg. USD, EUR, GBP)

}

 

if (!($link=mysql_connect($home,$user,$pass)))

{

echo "Error when connecting itself to the data base";

exit();

}

if (!mysql_select_db( $base , $link ))

{

echo "Error the data base does not exist";

exit();

}

 

$sql = "

select concat( '" . $productURL . "' ,products.products_id) AS product_url,

products_model , products_weight ,

manufacturers.manufacturers_name ,

products.products_id AS id,

products_description.products_name AS name,

products_description.products_description AS description,

FORMAT(products.products_price,2) AS price,

CONCAT( '" . $imageURL . "' ,products.products_image) AS image_url,

concat_ws( ' > ' , catdescparent.categories_name , catdesccurrent.categories_name ) AS category

FROM categories ,

products,

products_description,

categories_description as catdesccurrent,

products_to_categories

 

 

left join categories_description as catdescparent on ( catdescparent.categories_id = categories.parent_id )

left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id )

 

WHERE products.products_id=products_description.products_id

AND products.products_id=products_to_categories.products_id

AND products_to_categories.categories_id=categories.categories_id

AND catdesccurrent.categories_id = categories.categories_id

AND products.products_status != 0

ORDER BY

products.products_id ASC

";

 

 

//Check for any applicable specials for the corresponding products_id

$sql2 = "

SELECT

specials.products_id AS idS,

FORMAT(specials.specials_new_products_price,2) AS priceS

FROM

specials,

products

WHERE

specials.products_id=products.products_id

AND specials.status != 0

AND products.products_status != 0

ORDER BY

specials.products_id ASC

";

 

$_strip_search = array(

"![\t ]+$|^[\t ]+!m", // remove leading/trailing space chars

'%[\r\n]+%m'); // remove CRs and newlines

$_strip_replace = array(

'',

'');

 

$output = "product_url \t name \t description \t price \t image_url \t category\n";

 

$result=mysql_query( $sql )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql = " . htmlentities($sql) );

 

//Specials Checker

$result2=mysql_query( $sql2 )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql2 = " . htmlentities($sql2) );

$row2 = mysql_fetch_object( $result2 );

 

//Currency Information

if($convertCur)

{

$sql3 = "

SELECT

currencies.value AS curUSD

FROM

currencies

WHERE currencies.code = '$curType'

";

 

$result3=mysql_query( $sql3 )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql3 = " . htmlentities($sql3) );

$row3 = mysql_fetch_object( $result3 );

}

 

while( $row = mysql_fetch_object( $result ) )

{

if ($already_sent[$row->product_url] == 1) continue; // if we've sent this one, skip the rest of the while loop

 

// reset the products price to our special price if there is one for this product

if( $row2->idS == $row->id ){

$row->price = $row2->priceS;

$previdS = $row2->idS;

while ( $row2->idS == $previdS )

{

$row2 = mysql_fetch_object( $result2 ); //advance row in special's table

}

}

 

if($convertCur)

{

$row->price = $row->price * $row3->curUSD;

$row->price = number_format($row->price, 2, '.', '');

}

 

$output .= $row->product_url . "\t" .

preg_replace($_strip_search, $strip_replace, strip_tags( str_replace(">", "> ", $row->name) ) ) . "\t" .

preg_replace($_strip_search, $strip_replace, strip_tags( str_replace(">", "> ", $row->description) ) ) . "\t" .

$row->price . "\t" .

$row->image_url . "\t" .

$row->category . "\n";

 

$already_sent[$row->product_url] = 1;

}

 

if ( file_exists( $OutFile ) )

unlink( $OutFile );

 

$fp = fopen( $OutFile , "w" );

$fout = fwrite( $fp , $output );

fclose( $fp );

 

[code]

Edited July 13, 2004 by balloons88

[balloons88]

Could somebody look at my post??????????? ^^^^^^^^^^^^^^^^^^^^^

[Guest]

$OutFile = "C:\Documents and Settings\Jeff\My Documents\NationwideParty.com\Froogle\your-fr-outfile.txt";

[FlyingMonkey]

probably this too:

 

$destination_file = "your-fr-outfile.txt"; //"CHANGEME-filename-to-upload-to-froogle.txt";

 

Please DO NOT POST ALL OF YOUR CODE, unless you are working on updating or modifying a feature of the feeder. The only part that needs to be changed are the settings at the top of the file.

[mattmcb]

I haven't heard anything back from anyone yet on getting this to work with Yahoo! Product Submit.

 

The only issue is that yahoo requires that you put the file into a specific directory... anyone able to help modify that last portion of code to get it to work.

 

Then we can release a Yahoo! Product Submit contrib!

[Guest]

I haven't heard anything back from anyone yet on getting this to work with Yahoo! Product Submit.

 

The only issue is that yahoo requires that you put the file into a specific directory... anyone able to help modify that last portion of code to get it to work.

 

Then we can release a Yahoo! Product Submit contrib!

I think the code above someone posted works fine.... I built my own and looks very similar to the one above and it seems to work for. Most all my products got accepted by Yahoo.... I stopped using the service since but they were up there....

 

B)

[FlyingMonkey]

I haven't heard anything back from anyone yet on getting this to work with Yahoo! Product Submit.

 

The only issue is that yahoo requires that you put the file into a specific directory... anyone able to help modify that last portion of code to get it to work.

 

Then we can release a Yahoo! Product Submit contrib!

Sorry about that, I wanted to test the code before I released it. It was just taking longer than I thought it would take. Please let me know if you find any errors, the only difference in terms of setting it up is adding the $ftp_directory up top.

 

Here it is:

 

<?php
$OutFile = "../yahoo/data.txt"; //"CHANGEME-full-path-to-file-with-777-dir-and-file-permissions.fr-outfile.txt";
$destination_file = "data.txt";  //"CHANGEME-filename-to-upload-to-yahoo.txt";
$source_file = $OutFile;
$imageURL = 'http://www.yourdomain.com/catalog/images';
$productURL = 'http://www.yourdomain.com/catalog/product_info.php?products_id=';
$already_sent = array();

$home = "localhost";
$user="db user";
$pass="db pass";
$base="db name";

$ftp_server = "ftp.productsubmit.adcentral.yahoo.com";
$ftp_user_name = "yahoo user name";
$ftp_user_pass = "yahoo password";
$ftp_directory ="your_ftp_directory";

$convertCur = false; //default = false
$curType = "USD"; // Converts Currency to any defined currency (eg. USD, EUR, GBP)
if($convertCur)
{
$productURL = 'http://www.yourdomain.com/catalog/product_info.php?currency=CURTYPE&products_id=';  //where CURTYPE is your currency type (eg. USD, EUR, GBP)
}

if (!($link=mysql_connect($home,$user,$pass)))
{
echo "Error when connecting itself to the data base";
exit();
}
if (!mysql_select_db( $base , $link ))
{
echo "Error the data base does not exist";
exit();
}

$sql = "
select concat( '" . $productURL . "' ,products.products_id) AS product_url,
products_model , products_weight ,
manufacturers.manufacturers_name ,
products.products_id AS id,
products_description.products_name AS name,
products_description.products_description AS description,
FORMAT(products.products_price,2) AS price,
CONCAT( '" . $imageURL . "' ,products.products_image) AS image_url,
concat_ws( ' > ' , catdescparent.categories_name , catdesccurrent.categories_name ) AS category
FROM categories ,
products,
products_description,
categories_description as catdesccurrent,
products_to_categories


left join categories_description as catdescparent on ( catdescparent.categories_id = categories.parent_id )
left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id )

WHERE products.products_id=products_description.products_id
AND products.products_id=products_to_categories.products_id
AND products_to_categories.categories_id=categories.categories_id
AND catdesccurrent.categories_id = categories.categories_id
AND products.products_status != 0
ORDER BY
products.products_id ASC
";


//Check for any applicable specials for the corresponding products_id
$sql2 = "
SELECT
specials.products_id AS idS,
FORMAT(specials.specials_new_products_price,2) AS priceS
FROM
specials,
products
WHERE
specials.products_id=products.products_id
AND specials.status != 0
AND products.products_status != 0
ORDER BY
specials.products_id ASC
";

$_strip_search = array(
"![\t ]+$|^[\t ]+!m", // remove leading/trailing space chars
'%[\r\n]+%m'); // remove CRs and newlines
$_strip_replace = array(
'',
'');

$output = "code \t product_url \t name \t description \t price \t image_url \t category\n";

$result=mysql_query( $sql )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql = " . htmlentities($sql) );

//Specials Checker
$result2=mysql_query( $sql2 )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql2 = " . htmlentities($sql2) );
$row2 = mysql_fetch_object( $result2 );

//Currency Information
if($convertCur)
{
$sql3 = "
SELECT
currencies.value AS curUSD
FROM
currencies
WHERE currencies.code = '$curType'
";

$result3=mysql_query( $sql3 )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql3 = " . htmlentities($sql3) );
$row3 = mysql_fetch_object( $result3 );
}

while( $row = mysql_fetch_object( $result ) )
{
if ($already_sent[$row->product_url] == 1) continue; // if we've sent this one, skip the rest of the while loop

// reset the products price to our special price if there is one for this product
if( $row2->idS == $row->id ){
$row->price = $row2->priceS;
$previdS = $row2->idS;
while ( $row2->idS == $previdS )
{
$row2 = mysql_fetch_object( $result2 );  //advance row in special's table
}
}

if($convertCur)
{
$row->price = $row->price *  $row3->curUSD;
$row->price = number_format($row->price, 2, '.', '');
}

$output .= 
$row->id . "\t" .
$row->product_url . "\t" .
preg_replace($_strip_search, $strip_replace, strip_tags( str_replace(">", "> ", $row->name) ) ) . "\t" .
preg_replace($_strip_search, $strip_replace, strip_tags( str_replace(">", "> ", $row->description) ) ) . "\t" .
$row->price . "\t" .
$row->image_url . "\t" .
$row->category . "\n";

$already_sent[$row->product_url] = 1;
}

if ( file_exists( $OutFile ) )
unlink( $OutFile );

$fp = fopen( $OutFile , "w" );
$fout = fwrite( $fp , $output );
fclose( $fp );

//Start FTP to Yahoo

function ftp_file( $ftpservername, $ftpusername, $ftppassword, $ftpsourcefile, $ftpdirectory, $ftpdestinationfile )
{
// set up basic connection
$conn_id = ftp_connect($ftpservername);
if ( $conn_id == false )
{
echo "FTP open connection failed to $ftpservername <BR>\n";
return false; 
}

// login with username and password
$login_result = ftp_login($conn_id, $ftpusername, $ftppassword); 

// check connection
if ((!$conn_id) || (!$login_result)) { 
echo "FTP connection has failed!<BR>\n";
echo "Attempted to connect to " . $ftpservername . " for user " . $ftpusername . "<BR>\n"; 
return false; 
} else {
echo "Connected to " . $ftpservername . ", for user " . $ftpusername . "<BR>\n";
}

if ( strlen( $ftpdirectory ) > 0 )
{
if (ftp_chdir($conn_id, $ftpdirectory )) {
echo "Current directory is now: " . ftp_pwd($conn_id) . "<BR>\n";
} else { 
echo "Couldn't change directory on $ftpservername<BR>\n";
return false; 
}
}

ftp_pasv ( $conn_id, true );
// upload the file
$upload = ftp_put( $conn_id, $ftpdestinationfile, $ftpsourcefile, FTP_ASCII ); 

// check upload status
if (!$upload) { 
echo "$ftpservername: FTP upload has failed!<BR>\n";
return false; 
} else {
echo "Uploaded " . $ftpsourcefile . " to " . $ftpservername . " as " . $ftpdestinationfile . "<BR>\n";
}

// close the FTP stream 
ftp_close($conn_id); 

return true;
}

ftp_file( $ftp_server, $ftp_user_name, $ftp_user_pass, $source_file, $ftp_directory, $destination_file);

//End FTP to Yahoo
?>

[FlyingMonkey]

I'll be starting on Froogle Data Feeder version 1.4 soon, and hopefully releasing version 1.3.7.12 publicly within next week. Yahoo Data Feeder should be released the following week once the code above is tested. Then shortly after, I will integrate the "unlimited categories" upgrade from Froogle Data Feeder version 1.3.7.12 in to the Yahoo Data Feeder

Thanks to everyone who helps makes this happen. :)

Edited July 18, 2004 by FlyingMonkey

[mattmcb]

Make sure to change this, it's a hard error to catch:

 

$imageURL = 'http://www.yourdomain.com/catalog/images';

change to:

$imageURL = 'http://www.yourdomain.com/catalog/images/';

 

If you don't add the /, the image links won't work.

 

Also, how should the diectory be input, with or wihtout the //s? I didn't put any /s and it worked so you might want to include that in the comments.

 

This code is also wrong:

 

$output = "code \t product_url \t name \t description \t price \t image_url \t category\n";

Change to:

$output = "code \t product-url \t name \t description \t price \t image-url \t merchant-site-category\n";

 

Once you make those changes we're in business... it creates and uplods the file successfully into the directory!

Thanks,

Matt

Edited January 1, 2006 by Vger

[FlyingMonkey]

Here's the update, thanks matt for pointing that out:

 

<?php
$OutFile = "../feeds/your-yahoo-outfile.txt"; //"CHANGEME-full-path-to-file-with-777-dir-and-file-permissions.yahoo-outfile.txt";
$destination_file = "data.txt";  //"your-yahoo-outfile.txt";
$source_file = $OutFile;
$imageURL = 'CHANGEME-http://www.yourwebsite.com/yourimagesdirectory/';
$productURL = 'CHANGEME-http://www.yourwebsite.com/product_info.php?products_id=';
$already_sent = array();

$home = "localhost";
$user="CHANGEME-sql-db-user";
$pass="CHANGEME-sql-db-user-password";
$base="CHANGEME-name-of-sql-database";

$ftp_server = "ftp.productsubmit.adcentral.yahoo.com";
$ftp_user_name = "CHANGEME-yahoo-username";
$ftp_user_pass = "CHANGEME-yahoo-password";
$ftp_directory = "CHANGEME-yahoo_ftp_directory";

$convertCur = false; //default = false
$curType = "USD"; // Converts Currency to any defined currency (eg. USD, EUR, GBP)
if($convertCur)
{
$productURL = 'CHANGEME-http://www.yourwebsite.com/product_info.php?currency=CURTYPE&products_id=';  //where CURTYPE is your currency type (eg. USD, EUR, GBP)
}

if (!($link=mysql_connect($home,$user,$pass)))
{
echo "Error when connecting itself to the data base";
exit();
}
if (!mysql_select_db( $base , $link ))
{
echo "Error the data base does not exist";
exit();
}

$sql = "
select concat( '" . $productURL . "' ,products.products_id) AS product_url,
products_model , products_weight ,
manufacturers.manufacturers_name ,
products.products_id AS id,
products_description.products_name AS name,
products_description.products_description AS description,
FORMAT(products.products_price,2) AS price,
CONCAT( '" . $imageURL . "' ,products.products_image) AS image_url,
concat_ws( ' > ' , catdescparent.categories_name , catdesccurrent.categories_name ) AS category
FROM categories ,
products,
products_description,
categories_description as catdesccurrent,
products_to_categories


left join categories_description as catdescparent on ( catdescparent.categories_id = categories.parent_id )
left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id )

WHERE products.products_id=products_description.products_id
AND products.products_id=products_to_categories.products_id
AND products_to_categories.categories_id=categories.categories_id
AND catdesccurrent.categories_id = categories.categories_id
AND products.products_status != 0
ORDER BY
products.products_id ASC
";


//Check for any applicable specials for the corresponding products_id
$sql2 = "
SELECT
specials.products_id AS idS,
FORMAT(specials.specials_new_products_price,2) AS priceS
FROM
specials,
products
WHERE
specials.products_id=products.products_id
AND specials.status != 0
AND products.products_status != 0
ORDER BY
specials.products_id ASC
";

$_strip_search = array(
"![\t ]+$|^[\t ]+!m", // remove leading/trailing space chars
'%[\r\n]+%m'); // remove CRs and newlines
$_strip_replace = array(
'',
'');

$output = "code \t product-url \t name \t description \t price \t image-url \t merchant-site-category\n";

$result=mysql_query( $sql )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql = " . htmlentities($sql) );

//Specials Checker
$result2=mysql_query( $sql2 )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql2 = " . htmlentities($sql2) );
$row2 = mysql_fetch_object( $result2 );

//Currency Information
if($convertCur)
{
$sql3 = "
SELECT
currencies.value AS curUSD
FROM
currencies
WHERE currencies.code = '$curType'
";

$result3=mysql_query( $sql3 )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql3 = " . htmlentities($sql3) );
$row3 = mysql_fetch_object( $result3 );
}

while( $row = mysql_fetch_object( $result ) )
{
if ($already_sent[$row->product_url] == 1) continue; // if we've sent this one, skip the rest of the while loop

// reset the products price to our special price if there is one for this product
if( $row2->idS == $row->id ){
$row->price = $row2->priceS;
$previdS = $row2->idS;
while ( $row2->idS == $previdS )
{
$row2 = mysql_fetch_object( $result2 );  //advance row in special's table
}
}

if($convertCur)
{
$row->price = $row->price *  $row3->curUSD;
$row->price = number_format($row->price, 2, '.', '');
}

$output .=
$row->id . "\t" .
$row->product_url . "\t" .
preg_replace($_strip_search, $strip_replace, strip_tags( str_replace(">", "> ", $row->name) ) ) . "\t" .
preg_replace($_strip_search, $strip_replace, strip_tags( str_replace(">", "> ", $row->description) ) ) . "\t" .
$row->price . "\t" .
$row->image_url . "\t" .
$row->category . "\n";

$already_sent[$row->product_url] = 1;
}

if ( file_exists( $OutFile ) )
unlink( $OutFile );

$fp = fopen( $OutFile , "w" );
$fout = fwrite( $fp , $output );
fclose( $fp );

//Start FTP to Yahoo

function ftp_file( $ftpservername, $ftpusername, $ftppassword, $ftpsourcefile, $ftpdirectory, $ftpdestinationfile )
{
// set up basic connection
$conn_id = ftp_connect($ftpservername);
if ( $conn_id == false )
{
echo "FTP open connection failed to $ftpservername <BR>\n";
return false;
}

// login with username and password
$login_result = ftp_login($conn_id, $ftpusername, $ftppassword);

// check connection
if ((!$conn_id) || (!$login_result)) {
echo "FTP connection has failed!<BR>\n";
echo "Attempted to connect to " . $ftpservername . " for user " . $ftpusername . "<BR>\n";
return false;
} else {
echo "Connected to " . $ftpservername . ", for user " . $ftpusername . "<BR>\n";
}

if ( strlen( $ftpdirectory ) > 0 )
{
if (ftp_chdir($conn_id, $ftpdirectory )) {
echo "Current directory is now: " . ftp_pwd($conn_id) . "<BR>\n";
} else {
echo "Couldn't change directory on $ftpservername<BR>\n";
return false;
}
}

ftp_pasv ( $conn_id, true );
// upload the file
$upload = ftp_put( $conn_id, $ftpdestinationfile, $ftpsourcefile, FTP_ASCII );

// check upload status
if (!$upload) {
echo "$ftpservername: FTP upload has failed!<BR>\n";
return false;
} else {
echo "Uploaded " . $ftpsourcefile . " to " . $ftpservername . " as " . $ftpdestinationfile . "<BR>\n";
}

// close the FTP stream
ftp_close($conn_id);

return true;
}

ftp_file( $ftp_server, $ftp_user_name, $ftp_user_pass, $source_file, $ftp_directory, $destination_file);

//End FTP to Yahoo
?>

[FlyingMonkey]

does the yahoo feed require that the data comes in a certain order?

[FlyingMonkey]

as in code product_url name description price image_url category

 

i'll add the note about the directory. ;)

Edited July 18, 2004 by FlyingMonkey

[FlyingMonkey]

The Yahoo specs also require:

 

medium

 

Required for Music and Video products only (please leave this field blank for all other product types)

 

My interpertation is that all stores need to still submit the medium column, but just submit "nothing" as the data.

[FlyingMonkey]

Added to include the blank medium. This will present a problem for stores that are music or video related, since there is no default information for medium in the osC Database. Users will need to enter their own database information into the code.

 

<?php
$OutFile = "../feeds/your-yahoo-outfile.txt"; //"CHANGEME-full-path-to-file-with-777-dir-and-file-permissions.yahoo-outfile.txt";
$destination_file = "data.txt"; ?//"your-yahoo-outfile.txt";
$source_file = $OutFile;
$imageURL = 'CHANGEME-http://www.yourwebsite.com/yourimagesdirectory/';
$productURL = 'CHANGEME-http://www.yourwebsite.com/product_info.php?products_id=';
$already_sent = array();

$home = "localhost";
$user="CHANGEME-sql-db-user";
$pass="CHANGEME-sql-db-user-password";
$base="CHANGEME-name-of-sql-database";

$ftp_server = "ftp.productsubmit.adcentral.yahoo.com";
$ftp_user_name = "CHANGEME-yahoo-username";
$ftp_user_pass = "CHANGEME-yahoo-password";
$ftp_directory = "CHANGEME-yahoo_ftp_directory"; // slashes may not be needed for yahoo

$convertCur = false; //default = false
$curType = "USD"; // Converts Currency to any defined currency (eg. USD, EUR, GBP)
if($convertCur)
{
$productURL = 'CHANGEME-http://www.yourwebsite.com/product_info.php?currency=CURTYPE&products_id='; ?//where CURTYPE is your currency type (eg. USD, EUR, GBP)
}

if (!($link=mysql_connect($home,$user,$pass)))
{
echo "Error when connecting itself to the data base";
exit();
}
if (!mysql_select_db( $base , $link ))
{
echo "Error the data base does not exist";
exit();
}

$sql = "
select concat( '" . $productURL . "' ,products.products_id) AS product_url,
products_model , products_weight ,
manufacturers.manufacturers_name ,
products.products_id AS id,
products_description.products_name AS name,
products_description.products_description AS description,
FORMAT(products.products_price,2) AS price,
CONCAT( '" . $imageURL . "' ,products.products_image) AS image_url,
concat_ws( ' > ' , catdescparent.categories_name , catdesccurrent.categories_name ) AS category
FROM categories ,
products,
products_description,
categories_description as catdesccurrent,
products_to_categories


left join categories_description as catdescparent on ( catdescparent.categories_id = categories.parent_id )
left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id )

WHERE products.products_id=products_description.products_id
AND products.products_id=products_to_categories.products_id
AND products_to_categories.categories_id=categories.categories_id
AND catdesccurrent.categories_id = categories.categories_id
AND products.products_status != 0
ORDER BY
products.products_id ASC
";


//Check for any applicable specials for the corresponding products_id
$sql2 = "
SELECT
specials.products_id AS idS,
FORMAT(specials.specials_new_products_price,2) AS priceS
FROM
specials,
products
WHERE
specials.products_id=products.products_id
AND specials.status != 0
AND products.products_status != 0
ORDER BY
specials.products_id ASC
";

$_strip_search = array(
"![\t ]+$|^[\t ]+!m", // remove leading/trailing space chars
'%[\r\n]+%m'); // remove CRs and newlines
$_strip_replace = array(
'',
'');

$medium = ""; ?//hard coded to blank
$output = "code \t product-url \t name \t description \t price \t image-url \t merchant-site-category \t medium\n";

$result=mysql_query( $sql )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql = " . htmlentities($sql) );

//Specials Checker
$result2=mysql_query( $sql2 )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql2 = " . htmlentities($sql2) );
$row2 = mysql_fetch_object( $result2 );

//Currency Information
if($convertCur)
{
$sql3 = "
SELECT
currencies.value AS curUSD
FROM
currencies
WHERE currencies.code = '$curType'
";

$result3=mysql_query( $sql3 )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql3 = " . htmlentities($sql3) );
$row3 = mysql_fetch_object( $result3 );
}

while( $row = mysql_fetch_object( $result ) )
{
if ($already_sent[$row->product_url] == 1) continue; // if we've sent this one, skip the rest of the while loop

// reset the products price to our special price if there is one for this product
if( $row2->idS == $row->id ){
$row->price = $row2->priceS;
$previdS = $row2->idS;
while ( $row2->idS == $previdS )
{
$row2 = mysql_fetch_object( $result2 ); ?//advance row in special's table
}
}

if($convertCur)
{
$row->price = $row->price * ?$row3->curUSD;
$row->price = number_format($row->price, 2, '.', '');
}

$output .=
$row->id . "\t" .
$row->product_url . "\t" .
preg_replace($_strip_search, $strip_replace, strip_tags( str_replace(">", "> ", $row->name) ) ) . "\t" .
preg_replace($_strip_search, $strip_replace, strip_tags( str_replace(">", "> ", $row->description) ) ) . "\t" .
$row->price . "\t" .
$row->image_url . "\t" .
$row->category . "\t" .
$medium . "\n";
$already_sent[$row->product_url] = 1;
}

if ( file_exists( $OutFile ) )
unlink( $OutFile );

$fp = fopen( $OutFile , "w" );
$fout = fwrite( $fp , $output );
fclose( $fp );

//Start FTP to Yahoo

function ftp_file( $ftpservername, $ftpusername, $ftppassword, $ftpsourcefile, $ftpdirectory, $ftpdestinationfile )
{
// set up basic connection
$conn_id = ftp_connect($ftpservername);
if ( $conn_id == false )
{
echo "FTP open connection failed to $ftpservername <BR>\n";
return false;
}

// login with username and password
$login_result = ftp_login($conn_id, $ftpusername, $ftppassword);

// check connection
if ((!$conn_id) || (!$login_result)) {
echo "FTP connection has failed!<BR>\n";
echo "Attempted to connect to " . $ftpservername . " for user " . $ftpusername . "<BR>\n";
return false;
} else {
echo "Connected to " . $ftpservername . ", for user " . $ftpusername . "<BR>\n";
}

if ( strlen( $ftpdirectory ) > 0 )
{
if (ftp_chdir($conn_id, $ftpdirectory )) {
echo "Current directory is now: " . ftp_pwd($conn_id) . "<BR>\n";
} else {
echo "Couldn't change directory on $ftpservername<BR>\n";
return false;
}
}

ftp_pasv ( $conn_id, true );
// upload the file
$upload = ftp_put( $conn_id, $ftpdestinationfile, $ftpsourcefile, FTP_ASCII );

// check upload status
if (!$upload) {
echo "$ftpservername: FTP upload has failed!<BR>\n";
return false;
} else {
echo "Uploaded " . $ftpsourcefile . " to " . $ftpservername . " as " . $ftpdestinationfile . "<BR>\n";
}

// close the FTP stream
ftp_close($conn_id);

return true;
}

ftp_file( $ftp_server, $ftp_user_name, $ftp_user_pass, $source_file, $ftp_directory, $destination_file);

//End FTP to Yahoo
?>

Edited July 18, 2004 by FlyingMonkey

[dyland]

FTP changing directory code is kind of hidden so I've copied it below:

 

if (ftp_chdir($conn_id, $ftpdirectory )) {

echo "Current directory is now: " . ftp_pwd($conn_id) . "<BR>\n";

} else {

echo "Couldn't change directory on $ftpservername<BR>\n";

return false;

}

 

 

Just set $ftpdirectory to the directory you want.

[FlyingMonkey]

Official Public Release of Froogle Data Feeder v1.3.7 build 13

Date: July 20, 2004

------------------------

 

Description: Creates and automatically sends a data feed to Froogle, to provide product information and pictures. This Froogle Data Feeder, handles product duplicates, specials(if available), currency conversion, html descriptions, full category trees, and automatic upload. It only adds products and specials that are in stock.

 

This release adds full category trees by high performance indexing. Development time for this took nearly two months and is meant generally for large stores.

 

Donations are greatly appreciated.

Paypal: ckscorpion@yahoo.com

Amazon Wishlist: http://amazon.com/gp/registry/TDRGVZI9R14X

Edited July 20, 2004 by FlyingMonkey

[Guest]

Hey Calvin...

 

Looks like you got that weird bug I was having with the beta version figured out as it's no longer happening.

 

I ran it against my 4,000 product store and took *maybe* 10 seconds to run.... Format looks ok on the surface and I will check a little later to make sure it got populated properly within Froogle.

 

Good Job and I will let you know if something you should know about crops up.

 

B)

[FlyingMonkey]

Hey Calvin...

 

Looks like you got that weird bug I was having with the beta version figured out as it's no longer happening.

 

I ran it against my 4,000 product store and took *maybe* 10 seconds to run.... Format looks ok on the surface and I will check a little later to make sure it got populated properly within Froogle.

 

Good Job and I will let you know if something you should know about crops up.

 

B)

yeah, the bug was worked out of 1.3.7 build 12 beta. It was a section of code that wasn't run on my system. I think you were testing build 11 somehow.... even when build 12 was out because I had it run on three other stores. build 13 just made it slightly easier to configure.

[FlyingMonkey]

bleh... i meant to say that the bug was in build 11 but not build 12.

[YSC]

I am getting the following error from froogle.php:

 

Connected to hedwig.google.com, for user medicalears

 

Warning: ftp_put(): medicalears.txt: Permission denied on server. (Overwrite) in /home/medicale/public_html/admin/froogle.php on line 226

FTP upload has FAILED! source: ../feeds/medicalears.txt dest: medicalears.txt

 

any ideas? I have the feeds folder and my .txt file permissions both set to 777. All of my information in froogle.php seems to be okay.

[YSC]

Sorry, here is my line 226 from froogle.php

 

$upload = ftp_put($conn_id, $destination_file, $source_file, FTP_BINARY);