Google Base - New Attributes Requirement for Data Feeds

ezfindit · February 1, 2007

Hi everyone,

I have been using the Froogle data feed for a long time to send data to Google Base. The http://www.oscommerce.com/community/contributions,3876 Froogle contribution worked great.

Yesterday I received this email from the Google Base Team informing me that my bulk load data files will no longer be accepted unless I include five additional attributes. Here's an excerpt from the email:

"That's why we're asking all Froogle merchants to send us bulk uploads in the Google Base format, with five additional attributes (along with the basic ones you've submitted in the past). Because we think it's important to provide a consistent user experience, we will no longer list items that don't meet a minimal number of attributes by the end of February 2007. Please review the following bulk uploads - they do not currently meet the minimum attribute requirement."

"You can review this attribute requirement page to learn about these new guidelines for general product attributes and specific attributes per product type. You can also learn how to submit your feed using Google Base here. We recommend that you include as many attributes as possible; the more information you provide, the better chance you'll have of matching users' "refinement" queries."

Do any of the existing Google Base feeds contributions meet the additional attributes requirements that Google Base wants? I was considering using the http://www.oscommerce.com/community/contributions,4513 contribution. Will it be sufficient for the Google Base folks?

Has anyone else encountered this issue? What does the community recommend I should do?

Thanks!

Rachael w. · February 2, 2007

I have been using the Froogle/Google Base, Yahoo, and Bizrate Feeds contrib and I am very happy with it, I also borrowed just the bidhopper.php page from another.

I also received the email from froogle/google base. I asked them a question and here is some blurbs from their reply:

"Thank you for your email. You can leave the "brand" attribute blank if this attribute is not relevant to some of your items. However, I strongly recommend including another attribute from our optional list, or you can just create a custom attribute to describe your items.

Finally, if your products don't fall under our predefined categories, you can include your own "product_type". "

So, I think what I need to do is have the product_type return my category name. I am by no means a php expert, but I am going to try to tweak my code and see what happens. I'll certainly let you know if I get anything to work.

Rachael w. · February 2, 2007

Well, I got it. This is a basic froogle.php with the following:

* brand

* condition

* description

* expiration_date

* id

* image_link

* link

* price

* product_type

* title

<?php
include('includes/application_top.php');
//  Start TIMER
//  -----------
$stimer = explode( ' ', microtime() );
$stimer = $stimer[1] + $stimer[0];
//  -----------


$OutFile = DIR_FS_DOCUMENT_ROOT."feeds/".FROOGLE_FTP_FILENAME; 
$destination_file = FROOGLE_FTP_FILENAME;  //"CHANGEME-filename-to-upload-to-froogle.txt";
$source_file = $OutFile;
$imageURL = HTTP_CATALOG_SERVER . DIR_WS_CATALOG . DIR_WS_IMAGES;
$productURL = HTTP_CATALOG_SERVER . DIR_WS_CATALOG .'/product_info.php?products_id=';
$already_sent = array();

$home=DB_SERVER;
$user=DB_SERVER_USERNAME;
$pass=DB_SERVER_PASSWORD;
$base=DB_DATABASE;

$ftp_server = FROOGLE_FTP_SERVER;

$ftp_user_name = FROOGLE_FTP_USER;

$ftp_user_pass = FROOGLE_FTP_PASS;

$ftp_directory = ""; // leave blank for froogle

$taxRate = 0; //default = 0 (e.g. for 17.5% tax use "$taxRate = 17.5;")
$taxCalc = ($taxRate/100) + 1;  //Do not edit
$convertCur = false; //default = false
$curType = "USD"; // Converts Currency to any defined currency (eg. USD, EUR, GBP)
if($convertCur)
{
$productURL = "http://CHANGE ME TO YOUR URL/catalog/product_info.php?currency=" . $curType . "&products_id=";  //where CURTYPE is your currency type (eg. USD, EUR, GBP)
}

//START Advance Optional Values

//(0=False 1=True) (optional_sec must be enabled to use any options)
$optional_sec = 1;
$instock = 0;
$shipping = 0;
$lowestShipping = "US:Priority:6.00";  //this is not binary.
$brand = 1;
$upc = 0;   //Not supported by default osC
$manufacturer_id = 0;  //Not supported by default osC
$payment_accepted = 0;
$default_payment_methods = "cash,check,GoogleCheckout"; //Acceptable values: cash, check, GoogleCheckout, Visa, MasterCard, AmericanExpress, Discover, wiretransfer
$product_type = 1;
$currency = 0;
$default_currency = "USD";  //this is not binary.
$feed_language = 0;
$default_feed_language = "en";  //this is not binary.
$ship_to = 1;
$default_ship_to = "ALL"; //this is not binary, not supported by default osC for individual products.
$ship_from = 1;
$default_ship_from = "USD"; //this is not binary, not supported by default osC for individual products.
$condition = 1; 
$default_condition = "used"; //Acceptable values:new, used
$feed_exp_date = date('Y-m-d', time() + 2592000 );

//END of Advance Optional Values

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 AS prodModel, products_weight,
manufacturers.manufacturers_name AS mfgName,
manufacturers.manufacturers_id,
products.products_id AS id,
products_description.products_name AS name,
products_description.products_description AS description,
products.products_quantity AS quantity,
products.products_status AS prodStatus,
FORMAT( IFNULL(specials.specials_new_products_price, products.products_price) * " . $taxCalc . ",2) AS price,
CONCAT( '" . $imageURL . "' ,products.products_image) AS image_url,
products_to_categories.categories_id AS prodCatID,
categories.parent_id AS catParentID,
categories_description.categories_name AS catName
FROM categories,
categories_description,
products,
products_description,
products_to_categories

left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id )
left join specials on ( specials.products_id = products.products_id AND ( ( (specials.expires_date > CURRENT_DATE) OR (specials.expires_date = 0) ) AND ( specials.status = 1 ) ) )

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 categories.categories_id=categories_description.categories_id
AND products.products_status != 0
AND products.products_price != 0
AND products.products_price != ''
ORDER BY
products.products_id ASC
";


$catInfo = "
SELECT
categories.categories_id AS curCatID,
categories.parent_id AS parentCatID,
categories_description.categories_name AS catName
FROM
categories,
categories_description
WHERE categories.categories_id = categories_description.categories_id
";

function findCat($curID, $catTempPar, $catTempDes, $catIndex)
{
if( (isset($catTempPar[$curID])) && ($catTempPar[$curID] != 0) )
{
	if(isset($catIndex[$catTempPar[$curID]]))
	{
		$temp=$catIndex[$catTempPar[$curID]];
	}
	else
	{
		$catIndex = findCat($catTempPar[$curID], $catTempPar, $catTempDes, $catIndex);
		$temp = $catIndex[$catTempPar[$curID]];
	}
}
if( (isset($catTempPar[$curID])) && (isset($catTempDes[$curID])) && ($catTempPar[$curID] == 0) )
{
	$catIndex[$curID] = $catTempDes[$curID];
}
else
{
	$catIndex[$curID] = $temp . " > " . $catTempDes[$curID];
}
return $catIndex;

}

$catIndex = array();
$catTempDes = array();
$catTempPar = array();
$processCat = mysql_query( $catInfo )or die( $FunctionName . ": SQL error " . mysql_error() . "| catInfo = " . htmlentities($catInfo) );
while ( $catRow = mysql_fetch_object( $processCat ) )
{
$catKey = $catRow->curCatID;
$catName = $catRow->catName;
$catParID = $catRow->parentCatID;
if($catName != "")
{
	$catTempDes[$catKey]=$catName;
	$catTempPar[$catKey]=$catParID;
}
}

foreach($catTempDes as $curID=>$des)  //don't need the $des
{
$catIndex = findCat($curID, $catTempPar, $catTempDes, $catIndex);
}

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

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

$output = "link \t title \t description\t expiration_date \t label \t price \t image_link \t id";

//create optional section
if($optional_sec == 1)
{
if($instock == 1)
	$output .= "\t instock ";
if($shipping == 1)
	$output .= "\t shipping ";
if($brand == 1)
	$output .= "\t brand ";
if($upc == 1)
	$output .= "\t upc ";
if($manufacturer_id == 1)
	$output .= "\t manufacturer_id ";
if($product_type == 1)
	$output .= "\t product_type ";
if($currency == 1)
	$output .= "\t currency ";
if($feed_language == 1)
	$output .= "\t language ";
if($ship_to == 1)
	$output .= "\t ship_to ";
if($ship_from == 1)
	$output .= "\t ship_from ";
if($condition == 1) 
	$output .= "\tcondition"; 
}
$output .= "\n";


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

//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 );
}

$loop_counter = 0;

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

if( $row->prodStatus == 1 || ($optional_sec == 1 && $instock == 1) )
{

	if($convertCur)
	{
		$row->price = ereg_replace("[^.0-9]", "", $row->price);
		$row->price = $row->price *  $row3->curUSD;
		$row->price = number_format($row->price, 2, '.', ',');
	}

	if ($imageURL == $row->image_url) {
		$image = "";
	}else{
		$image = $row->image_url;
	}
	$label="";
	if ($row->name != ""){
		$label .= preg_replace($_strip_search,
$_strip_replace, strip_tags( strtr($row->name,
$_cleaner_array) ) );
	}
	if ($row->prodModel != "" && $row->name != ""){
		$label .= ",". $row->prodModel;
	}else{
		$label .= $row->prodModel;
	}
	if ($row->mfgName != "" && ($row->prodModel != "" 
|| $row->name != "")){
		$label .= ",". $row->mfgName;
	}else{
		$label .= $row->mfgName;
	}
	$output .= $row->product_url . "\t" .
	preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->name, $_cleaner_array) ) ) . "\t" .
	preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->description, $_cleaner_array) ) ) . "\t" .
	$feed_exp_date . "\t" .
	$label . "\t" .
	$row->price . "\t" .
	$image . "\t" .
	$row->id;

//optional values section
if($optional_sec == 1)
{
	if($instock == 1)
	{
		if($row->prodStatus == 1)
		{
			$prodStatusOut = "Y";
		}
		else
		{
			$prodStatusOut = "N";
		}
		$output .= " \t " . $prodStatusOut;
	}
	if($shipping == 1)
		$output .= " \t " . $lowestShipping;
	if($brand == 1)
		$output .= " \t " . $row->mfgName;
	if($upc == 1)
		$output .= " \t " . "Not Supported";
	if($manufacturer_id == 1)
		$output .= " \t " . "Not Supported";
	if($product_type == 1)
	{
		$catNameTemp = strtolower($catName);
		if($catNameTemp == "books")
			$productTypeOut = "book";
		else if($catNameTemp == "music")
			$productTypeOut = "music";
		else if($catNameTemp == "videos")
			$productTypeOut = "video";
		else
			$productTypeOut = "other";

		$output .= " \t " . $productTypeOut;
	}
	if($currency == 1)
		$output .= " \t " . $default_currency;
	if($feed_language == 1)
		$output .= " \t " . $default_feed_language;
	if($ship_to == 1)
		$output .= " \t " . $default_ship_to;
	if($ship_from == 1)
		$output .= " \t " . $default_ship_from;
	if($condition == 1)	
		$output .= " \t " . $default_condition; 
}
$output .= " \n";
}
$already_sent[$row->id] = 1;


$loop_counter++;
if ($loop_counter>750) {
$fp = fopen( $OutFile , "a" );
$fout = fwrite( $fp , $output );
fclose( $fp );
$loop_counter = 0;
$output = "";
}
}

$fp = fopen( $OutFile , "a" );
$fout = fwrite( $fp , $output );
fclose( $fp );
echo "File completed: " . $destination_file . "<br>\n";
chmod($OutFile, 0777);


//Start FTP to Froogle

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 Froogle

//  End TIMER
//  ---------
$etimer = explode( ' ', microtime() );
$etimer = $etimer[1] + $etimer[0];
echo '<p style="margin:auto; text-align:center">';
printf( "Script timer: <b>%f</b> seconds.", ($etimer-$stimer) );
echo '</p>';
//  ---------

?>

Of course you'll need to change the CHANGE ME's and edit to suit your particular store. I think I did it right since my feed is working correctly.

Hope this helps someone out there. :)

ezfindit · February 2, 2007

Yes, this does help, not only me but others also. Thank you! :thumbsup:

Quick question, will this work with the SEO contribution installed? :blush: Just making sure.

Thanks again!

Rachael w. · February 2, 2007

Yes, this does help, not only me but others also. Thank you! :thumbsup:

Quick question, will this work with the SEO contribution installed? :blush: Just making sure.

Thanks again!

I am sorry I dont really know if what I posted will work with the seo contribution since I dont use that particular contrib.

The following code was submited by babymand as an update to the Google Base / Froogle Data Feeder v1.00 contrib. This code has information for the seo contribution, perhaps you could edit this file to work for you.

<?php
//  Title: Google Base / Froogle Data Feeder 1.03
//  Author: Calvin K
//  Contact: calvink@conceptulanetworking.com
//	Organization: Conceptual Networking
//  Last Update: 09/24/06

//
// This feeder has been revised to work with Ultimate SEO URLs 2.1d (http://www.oscommerce.com/community/contributions,2823)
//


// Change to 'false' to disable the use of SEO
define('SEO_ENABLED','true');

if(SEO_ENABLED=='true'){
 //********************
 // Modification for SEO
 // Since the ultimate SEO was only installed on the public side, we will include our files from there.
 require_once('../includes/configure.php');
 require_once('../includes/filenames.php');
 require_once('../includes/database_tables.php');
 $languages_id = 2;   // CHANGEME - Change this to the id of your language.  BY default 1 is english


 include_once('../' .DIR_WS_CLASSES . 'seo.class.php');
 $seo_urls = new SEO_URL($languages_id);

 function tep_href_link($page = '', $parameters = '', $connection = 'NONSSL', $add_session_id = true, $search_engine_safe = true) {
 global $seo_urls;
 return $seo_urls->href_link($page, $parameters, $connection, $add_session_id);
 }
}

//********************
//  Start TIMER
//  -----------
$stimer = explode( ' ', microtime() );
$stimer = $stimer[1] + $stimer[0];
//  -----------


$OutFile = "../feeds/googlefeed.txt"; //"CHANGEME-full-path-to-file-with-777-dir-and-file-permissions-your-outfile.txt";
$destination_file = "googlefeed.txt";  //"CHANGEME-filename-to-upload-to-google.txt";
$source_file = $OutFile;
$imageURL = 'http://www.babymand.com/images/';
if(SEO_ENABLED=='true'){
  $productURL = 'product_info.php'; // ***** Revised for SEO
  $productParam = "products_id=";   // ***** Added for SEO
}else{
  $productURL = 'http://www.babymand.com/product_info.php?currency=EUR&language=en&products_id=';
}
$already_sent = array();

if(SEO_ENABLED=='true'){
 $home = DB_SERVER;		  //  ****** revised these because we can....
 $user=DB_SERVER_USERNAME;
 $pass=DB_SERVER_PASSWORD;
 $base=DB_DATABASE;
}else{
$home = "";
$user="";
$pass="";
$base="";
}

$ftp_server = "uploads.google.com";
$ftp_user_name = "********";
$ftp_user_pass = "********";
$ftp_directory = ""; // leave blank

$taxRate = 19; //default = 0 (e.g. for 17.5% tax use "$taxRate = 17.5;")
$taxCalc = ($taxRate/100) + 1;  //Do not edit
$convertCur = true; //default = false
$curType = "USD"; // Converts Currency to any defined currency (eg. USD, EUR, GBP)
if($convertCur)
{
  if(SEO_ENABLED=='true'){
   $productParam="currency=" . $curType . "&products_id=";
  }else{
   $productURL = "http://www.babymand.com/product_info.php?currency=" . $curType . "&products_id=";  //where CURTYPE is your currency type (eg. USD, EUR, GBP)
  }
}

//START Advance Optional Values

//(0=False 1=True) (optional_sec must be enabled to use any options)
$optional_sec = 1;
$shipping = 1;
$lowestShipping = "NL:overnight:6.20";  //this is not binary.  Custom Code is required to provide the shipping cost per product.  ###needs to be an array for per product.
$manufacturer = 1;
$upc = 0;   //Not supported by default osC
$payment_accepted = 1;
$default_payment_methods = "visa,cash,mastercard,americanexpress"; //Acceptable values: cash, check, GoogleCheckout, Visa, MasterCard, AmericanExpress, Discover, wiretransfer
$product_type = 1;
$currency = 2;
$default_currency = "USD";  //this is not binary.
$feed_language = 0;
$default_feed_language = "EN";  //this is not binary.
$feed_manufacturer_id = 0; //this is for the manufacturer model number, NOT your internal store sku
$feed_quantity = 1;
$brand = 1; // 31 jan 2007 Added condition due to Froogle making mandatory
$default_brand = "Babymand"; // 31 jan 2007 Added condition due to Froogle making mandatory)
$condition = 1; // 31 jan 2007 Added condition due to Froogle making mandatory
$default_condition = "New"; // 31 jan 2007 Added condition due to Froogle making mandatory
$age_range = 1; // 31 jan 2007 Added condition due to Froogle making mandatory
$default_age_range = "0-9"; // 31 jan 2007 Added condition due to Froogle making mandatory
$made_in = 1; // 31 jan 2007 Added condition due to Froogle making mandatory
$default_made_in = "Netherlands"; // 31 jan 2007 Added condition due to Froogle making mandatory

//END of Advance Optional Values

$feed_exp_date = date('Y-m-d', time() + 2592000 );


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 AS prodModel, products_weight,
manufacturers.manufacturers_name AS mfgName,
manufacturers.manufacturers_id,
products.products_id AS id,
products_description.products_name AS name,
products_description.products_description AS description,
products.products_quantity AS quantity,
products.products_status AS prodStatus,
FORMAT( IFNULL(specials.specials_new_products_price, products.products_price) * " . $taxCalc . ",2) AS price,
CONCAT( '" . $imageURL . "' ,products.products_image) AS image_url,
products_to_categories.categories_id AS prodCatID,
categories.parent_id AS catParentID,
categories_description.categories_name AS catName
FROM (categories,
categories_description,
products,
products_description,
products_to_categories)

left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id )
left join specials on ( specials.products_id = products.products_id AND ( ( (specials.expires_date > CURRENT_DATE) OR (specials.expires_date is NULL) ) AND ( specials.status = 1 ) ) )

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 categories.categories_id=categories_description.categories_id
ORDER BY
products.products_id ASC
";


$catInfo = "
SELECT
categories.categories_id AS curCatID,
categories.parent_id AS parentCatID,
categories_description.categories_name AS catName
FROM
categories,
categories_description
WHERE categories.categories_id = categories_description.categories_id
";

function findCat($curID, $catTempPar, $catTempDes, $catIndex)
{
if( (isset($catTempPar[$curID])) && ($catTempPar[$curID] != 0) )
{
	if(isset($catIndex[$catTempPar[$curID]]))
	{
		$temp=$catIndex[$catTempPar[$curID]];
	}
	else
	{
		$catIndex = findCat($catTempPar[$curID], $catTempPar, $catTempDes, $catIndex);
		$temp = $catIndex[$catTempPar[$curID]];
	}
}
if( (isset($catTempPar[$curID])) && (isset($catTempDes[$curID])) && ($catTempPar[$curID] == 0) )
{
	$catIndex[$curID] = $catTempDes[$curID];
}
else
{
	$catIndex[$curID] = $temp . ", " . $catTempDes[$curID];
}
return $catIndex;

}

$catIndex = array();
$catTempDes = array();
$catTempPar = array();
$processCat = mysql_query( $catInfo )or die( $FunctionName . ": SQL error " . mysql_error() . "| catInfo = " . htmlentities($catInfo) );
while ( $catRow = mysql_fetch_object( $processCat ) )
{
$catKey = $catRow->curCatID;
$catName = $catRow->catName;
$catParID = $catRow->parentCatID;
if($catName != "")
{
	$catTempDes[$catKey]=$catName;
	$catTempPar[$catKey]=$catParID;
}
}

foreach($catTempDes as $curID=>$des)  //don't need the $des
{
$catIndex = findCat($curID, $catTempPar, $catTempDes, $catIndex);
}

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

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

$output = "link\ttitle\tdescription\texpiration_date\tprice\timage_link\tlabel\tid";

//create optional section
if($optional_sec == 1)
{
if($shipping == 1)
	$output .= "\tshipping";
if($manufacturer == 1)
	$output .= "\tmanufacturer";
if($upc == 1)
	$output .= "\tupc";
if($payment_accepted == 1)
	$output .= "\tpayment_accepted";
if($product_type == 1)
	$output .= "\tproduct_type";
if($currency == 1)
	$output .= "\tcurrency";
if($feed_language == 1)
	$output .= "\tlanguage";
if($feed_manufacturer_id == 1)
	$output .= "\tmanufacturer_id";
if($feed_quantity == 1)
	$output .= "\tquantity";
if($brand == 1)  // 31 jan 2007 Added condition due to Froogle making mandatory
	$output .= "\tbrand"; // 31 jan 2007 Added condition due to Froogle making mandatory
if($condition == 1) // 31 jan 2007 Added condition due to Froogle making mandatory
	$output .= "\tcondition"; // 31 jan 2007 Added condition due to Froogle making mandatory
if($age_range == 1)  // 31 jan 2007 Added condition due to Froogle making mandatory
	$output .= "\tage_range"; // 31 jan 2007 Added condition due to Froogle making mandatory
if($made_in == 1) // 31 jan 2007 Added condition due to Froogle making mandatory
	$output .= "\tmade_in"; // 31 jan 2007 Added condition due to Froogle making mandatory
}
$output .= "\n";


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

//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 );
}

$loop_counter = 0;

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

if( $row->prodStatus == 1 || ($optional_sec == 1 && $quantity == 1) )
{

	if($convertCur)
	{
		$row->price = ereg_replace("[^.0-9]", "", $row->price);
		$row->price = $row->price *  $row3->curUSD;
		$row->price = number_format($row->price, 2, '.', ',');
	}

	if(SEO_ENABLED=='true'){
			$output .= tep_href_link($productURL,$productParam . $row->id) . "\t" .
			preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->name, $_cleaner_array) ) ) . "\t" .
			preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->description, $_cleaner_array) ) ) . "\t" .
			$feed_exp_date . "\t" .
			$row->price . "\t" .
			$row->image_url . "\t" .
			$catIndex[$row->prodCatID] . "\t" .
			$row->id;
	}else{
	$output .= $row->product_url . "\t" .
	preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->name, $_cleaner_array) ) ) . "\t" .
	preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->description, $_cleaner_array) ) ) . "\t" .
	$feed_exp_date . "\t" .
	$row->price . "\t" .
	$row->image_url . "\t" .
	$catIndex[$row->prodCatID] . "\t" .
	$row->id;
	}

//optional values section
if($optional_sec == 1)
{
	if($shipping == 1)
		$output .= " \t " . $lowestShipping;
	if($manufacturer == 1)
		$output .= " \t " . $row->mfgName;
	if($upc == 1)
		$output .= " \t " . "Not Supported";
	if($payment_accepted == 1)
		$output .= " \t " . $default_payment_methods;
	if($product_type == 1)
	{
		$catNameTemp = strtolower($catName);
		$output .= " \t " . $row->catName;
	}
	if($currency == 1)
		$output .= " \t " . $default_currency;
	if($feed_language == 1)
		$output .= " \t " . $default_feed_language;
	if($feed_manufacturer_id == 1)
		$output .= " \t " . $row->prodModel;
	if($feed_quantity == 1)
		$output .= " \t " . $row->quantity;
	if($brand == 1)	// 31 jan 2007 Added condition due to Froogle making mandatory
		$output .= " \t " . $default_brand; // 31 jan 2007 Added condition due to Froogle making mandatory
	if($condition == 1)	 // 31 jan 2007 Added condition due to Froogle making mandatory
		$output .= " \t " . $default_condition; // 31 jan 2007 Added condition due to Froogle making mandatory
	if($age_range == 1)	// 31 jan 2007 Added condition due to Froogle making mandatory
		$output .= " \t " . $default_age_range; // 31 jan 2007 Added condition due to Froogle making mandatory
	if($made_in == 1)	 // 31 jan 2007 Added condition due to Froogle making mandatory
		$output .= " \t " . $default_made_in; // 31 jan 2007 Added condition due to Froogle making mandatory
}
$output .= " \n";
}
$already_sent[$row->id] = 1;


$loop_counter++;
if ($loop_counter>750) {
$fp = fopen( $OutFile , "a" );
$fout = fwrite( $fp , $output );
fclose( $fp );
$loop_counter = 0;
$output = "";
}
}

$fp = fopen( $OutFile , "a" );
$fout = fwrite( $fp , $output );
fclose( $fp );
echo "File completed: <a href=\"" . $OutFile . "\" target=\"_blank\">" . $destination_file . "</a><br>\n";
chmod($OutFile, 0777);


//Start FTP

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


//  End TIMER
//  ---------
$etimer = explode( ' ', microtime() );
$etimer = $etimer[1] + $etimer[0];
echo '<p style="margin:auto; text-align:center">';
printf( "Script timer: <b>%f</b> seconds.", ($etimer-$stimer) );
echo '</p>';
//  ---------

?>

Rachael w. · February 3, 2007

Well, I worked on my froogle.php a little more. I am now getting the following sent to google successfully from it and I thought I would share my code once again:

link

title

description

expiration_date

label

price

image_link

id

instock

shipping

brand

quantity

payment_accepted

product_type (THIS WILL RETURN YOUR CURRENT PRODUCT CATEGORY NAMES)

ship_to

ship_from

condition

<?php
include('includes/application_top.php');
//  Start TIMER
//  -----------
$stimer = explode( ' ', microtime() );
$stimer = $stimer[1] + $stimer[0];
//  -----------


$OutFile = DIR_FS_DOCUMENT_ROOT."feeds/".FROOGLE_FTP_FILENAME; 
$destination_file = FROOGLE_FTP_FILENAME;  //"CHANGEME-filename-to-upload-to-froogle.txt";
$source_file = $OutFile;
$imageURL = HTTP_CATALOG_SERVER . DIR_WS_CATALOG . DIR_WS_IMAGES;
$productURL = HTTP_CATALOG_SERVER . DIR_WS_CATALOG .'/product_info.php?products_id=';
$already_sent = array();

$home=DB_SERVER;
$user=DB_SERVER_USERNAME;
$pass=DB_SERVER_PASSWORD;
$base=DB_DATABASE;

$ftp_server = FROOGLE_FTP_SERVER;

$ftp_user_name = FROOGLE_FTP_USER;

$ftp_user_pass = FROOGLE_FTP_PASS;

$ftp_directory = ""; // leave blank for froogle

$taxRate = 0; //default = 0 (e.g. for 17.5% tax use "$taxRate = 17.5;")
$taxCalc = ($taxRate/100) + 1;  //Do not edit
$convertCur = false; //default = false
$curType = "USD"; // Converts Currency to any defined currency (eg. USD, EUR, GBP)
if($convertCur)
{
$productURL = "http://www.CHANGE ME.com/catalog/product_info.php?currency=" . $curType . "&products_id=";  //where CURTYPE is your currency type (eg. USD, EUR, GBP)
}

//START Advance Optional Values

//(0=False 1=True) (optional_sec must be enabled to use any options)
$optional_sec = 1;
$instock = 1;
$shipping = 1;
$lowestShipping = "US:Priority:6.00";  //this is not binary. CHANGE TO SUIT YOUR STORE
$brand = 1;
$upc = 0;   //Not supported by default osC
$manufacturer_id = 0;  //Not supported by default osC
$feed_quantity = 1;
$payment_accepted = 1;
$default_payment_methods = "cash,check,GoogleCheckout"; //Acceptable values: cash, check, GoogleCheckout, Visa, MasterCard, AmericanExpress, Discover, wiretransfer
$product_type = 1;
$currency = 0;
$default_currency = "USD";  //this is not binary.
$feed_language = 0;
$default_feed_language = "en";  //this is not binary.
$ship_to = 1;
$default_ship_to = "ALL"; //this is not binary, not supported by default osC for individual products.
$ship_from = 1;
$default_ship_from = "USD"; //this is not binary, not supported by default osC for individual products.
$condition = 1; 
$default_condition = "used"; // CHANGE TO SUIT YOUR STORE
$feed_exp_date = date('Y-m-d', time() + 2592000 );

//END of Advance Optional Values

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 AS prodModel, products_weight,
manufacturers.manufacturers_name AS mfgName,
manufacturers.manufacturers_id,
products.products_id AS id,
products_description.products_name AS name,
products_description.products_description AS description,
products.products_quantity AS quantity,
products.products_status AS prodStatus,
FORMAT( IFNULL(specials.specials_new_products_price, products.products_price) * " . $taxCalc . ",2) AS price,
CONCAT( '" . $imageURL . "' ,products.products_image) AS image_url,
products_to_categories.categories_id AS prodCatID,
categories.parent_id AS catParentID,
categories_description.categories_name AS catName
FROM categories,
categories_description,
products,
products_description,
products_to_categories

left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id )
left join specials on ( specials.products_id = products.products_id AND ( ( (specials.expires_date > CURRENT_DATE) OR (specials.expires_date = 0) ) AND ( specials.status = 1 ) ) )

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 categories.categories_id=categories_description.categories_id
AND products.products_status != 0
AND products.products_price != 0
AND products.products_price != ''
ORDER BY
products.products_id ASC
";


$catInfo = "
SELECT
categories.categories_id AS curCatID,
categories.parent_id AS parentCatID,
categories_description.categories_name AS catName
FROM
categories,
categories_description
WHERE categories.categories_id = categories_description.categories_id
";

function findCat($curID, $catTempPar, $catTempDes, $catIndex)
{
if( (isset($catTempPar[$curID])) && ($catTempPar[$curID] != 0) )
{
	if(isset($catIndex[$catTempPar[$curID]]))
	{
		$temp=$catIndex[$catTempPar[$curID]];
	}
	else
	{
		$catIndex = findCat($catTempPar[$curID], $catTempPar, $catTempDes, $catIndex);
		$temp = $catIndex[$catTempPar[$curID]];
	}
}
if( (isset($catTempPar[$curID])) && (isset($catTempDes[$curID])) && ($catTempPar[$curID] == 0) )
{
	$catIndex[$curID] = $catTempDes[$curID];
}
else
{
	$catIndex[$curID] = $temp . " > " . $catTempDes[$curID];
}
return $catIndex;

}

$catIndex = array();
$catTempDes = array();
$catTempPar = array();
$processCat = mysql_query( $catInfo )or die( $FunctionName . ": SQL error " . mysql_error() . "| catInfo = " . htmlentities($catInfo) );
while ( $catRow = mysql_fetch_object( $processCat ) )
{
$catKey = $catRow->curCatID;
$catName = $catRow->catName;
$catParID = $catRow->parentCatID;
if($catName != "")
{
	$catTempDes[$catKey]=$catName;
	$catTempPar[$catKey]=$catParID;
}
}

foreach($catTempDes as $curID=>$des)  //don't need the $des
{
$catIndex = findCat($curID, $catTempPar, $catTempDes, $catIndex);
}

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

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

$output = "link \t title \t description\t expiration_date \t label \t price \t image_link \t id";

//create optional section
if($optional_sec == 1)
{
if($instock == 1)
	$output .= "\t instock ";
if($shipping == 1)
	$output .= "\t shipping ";
if($brand == 1)
	$output .= "\t brand ";
if($upc == 1)
	$output .= "\t upc ";
if($manufacturer_id == 1)
	$output .= "\t manufacturer_id ";
if($feed_quantity == 1)
	$output .= "\t quantity";
if($payment_accepted == 1)
	$output .= "\t payment_accepted";
if($product_type == 1)
	$output .= "\t product_type ";
if($currency == 1)
	$output .= "\t currency ";
if($feed_language == 1)
	$output .= "\t language ";
if($ship_to == 1)
	$output .= "\t ship_to ";
if($ship_from == 1)
	$output .= "\t ship_from ";
if($condition == 1) 
	$output .= "\tcondition"; 
}
$output .= "\n";


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

//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 );
}

$loop_counter = 0;

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

if( $row->prodStatus == 1 || ($optional_sec == 1 && $instock == 1) )
{

	if($convertCur)
	{
		$row->price = ereg_replace("[^.0-9]", "", $row->price);
		$row->price = $row->price *  $row3->curUSD;
		$row->price = number_format($row->price, 2, '.', ',');
	}

	if ($imageURL == $row->image_url) {
		$image = "";
	}else{
		$image = $row->image_url;
	}
	$label="";
	if ($row->name != ""){
		$label .= preg_replace($_strip_search,
$_strip_replace, strip_tags( strtr($row->name,
$_cleaner_array) ) );
	}
	if ($row->prodModel != "" && $row->name != ""){
		$label .= ",". $row->prodModel;
	}else{
		$label .= $row->prodModel;
	}
	if ($row->mfgName != "" && ($row->prodModel != "" 
|| $row->name != "")){
		$label .= ",". $row->mfgName;
	}else{
		$label .= $row->mfgName;
	}
	$output .= $row->product_url . "\t" .
	preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->name, $_cleaner_array) ) ) . "\t" .
	preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->description, $_cleaner_array) ) ) . "\t" .
	$feed_exp_date . "\t" .
	$label . "\t" .
	$row->price . "\t" .
	$image . "\t" .
	$row->id;

//optional values section
if($optional_sec == 1)
{
	if($instock == 1)
	{
		if($row->prodStatus == 1)
		{
			$prodStatusOut = "Y";
		}
		else
		{
			$prodStatusOut = "N";
		}
		$output .= " \t " . $prodStatusOut;
	}
	if($shipping == 1)
		$output .= " \t " . $lowestShipping;
	if($brand == 1)
		$output .= " \t " . $row->mfgName;
	if($upc == 1)
		$output .= " \t " . "Not Supported";
	if($manufacturer_id == 1)
		$output .= " \t " . "Not Supported";
	if($feed_quantity == 1)
		$output .= " \t " . $row->quantity;
	if($payment_accepted == 1)
		$output .= " \t " . $default_payment_methods;
	if($product_type == 1)
	{
		$catNameTemp = strtolower($catName);
		$output .= " \t " . $row->catName;
	}
	if($currency == 1)
		$output .= " \t " . $default_currency;
	if($feed_language == 1)
		$output .= " \t " . $default_feed_language;
	if($ship_to == 1)
		$output .= " \t " . $default_ship_to;
	if($ship_from == 1)
		$output .= " \t " . $default_ship_from;
	if($condition == 1)	
		$output .= " \t " . $default_condition; 
}
$output .= " \n";
}
$already_sent[$row->id] = 1;


$loop_counter++;
if ($loop_counter>750) {
$fp = fopen( $OutFile , "a" );
$fout = fwrite( $fp , $output );
fclose( $fp );
$loop_counter = 0;
$output = "";
}
}

$fp = fopen( $OutFile , "a" );
$fout = fwrite( $fp , $output );
fclose( $fp );
echo "File completed: " . $destination_file . "<br>\n";
chmod($OutFile, 0777);


//Start FTP to Froogle

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 Froogle

//  End TIMER
//  ---------
$etimer = explode( ' ', microtime() );
$etimer = $etimer[1] + $etimer[0];
echo '<p style="margin:auto; text-align:center">';
printf( "Script timer: <b>%f</b> seconds.", ($etimer-$stimer) );
echo '</p>';
//  ---------

?>

Just one note: This code will send products with a quantity of 0 if you still have it marked as active. This should not generate an error with google base unless you chose google checkout as a payment method. You must have a quantity of at least 1 for google checkout. The feed will still go through and all your products with a quantity of 1 will still be approved, but you will see an error on your upload page for each product with a quantity of 0.

pbpools · February 4, 2007

Well, I worked on my froogle.php a little more. I am now getting the following sent to google successfully from it and I thought I would share my code once again:

link

title

description

expiration_date

label

price

image_link

id

instock

shipping

brand

quantity

payment_accepted

product_type (THIS WILL RETURN YOUR CURRENT PRODUCT CATEGORY NAMES)

ship_to

ship_from

condition

<?php
include('includes/application_top.php');
//  Start TIMER
//  -----------
$stimer = explode( ' ', microtime() );
$stimer = $stimer[1] + $stimer[0];
//  -----------


$OutFile = DIR_FS_DOCUMENT_ROOT."feeds/".FROOGLE_FTP_FILENAME; 
$destination_file = FROOGLE_FTP_FILENAME;  //"CHANGEME-filename-to-upload-to-froogle.txt";
$source_file = $OutFile;
$imageURL = HTTP_CATALOG_SERVER . DIR_WS_CATALOG . DIR_WS_IMAGES;
$productURL = HTTP_CATALOG_SERVER . DIR_WS_CATALOG .'/product_info.php?products_id=';
$already_sent = array();

$home=DB_SERVER;
$user=DB_SERVER_USERNAME;
$pass=DB_SERVER_PASSWORD;
$base=DB_DATABASE;

$ftp_server = FROOGLE_FTP_SERVER;

$ftp_user_name = FROOGLE_FTP_USER;

$ftp_user_pass = FROOGLE_FTP_PASS;

$ftp_directory = ""; // leave blank for froogle

$taxRate = 0; //default = 0 (e.g. for 17.5% tax use "$taxRate = 17.5;")
$taxCalc = ($taxRate/100) + 1;  //Do not edit
$convertCur = false; //default = false
$curType = "USD"; // Converts Currency to any defined currency (eg. USD, EUR, GBP)
if($convertCur)
{
$productURL = "http://www.CHANGE ME.com/catalog/product_info.php?currency=" . $curType . "&products_id=";  //where CURTYPE is your currency type (eg. USD, EUR, GBP)
}

//START Advance Optional Values

//(0=False 1=True) (optional_sec must be enabled to use any options)
$optional_sec = 1;
$instock = 1;
$shipping = 1;
$lowestShipping = "US:Priority:6.00";  //this is not binary. CHANGE TO SUIT YOUR STORE
$brand = 1;
$upc = 0;   //Not supported by default osC
$manufacturer_id = 0;  //Not supported by default osC
$feed_quantity = 1;
$payment_accepted = 1;
$default_payment_methods = "cash,check,GoogleCheckout"; //Acceptable values: cash, check, GoogleCheckout, Visa, MasterCard, AmericanExpress, Discover, wiretransfer
$product_type = 1;
$currency = 0;
$default_currency = "USD";  //this is not binary.
$feed_language = 0;
$default_feed_language = "en";  //this is not binary.
$ship_to = 1;
$default_ship_to = "ALL"; //this is not binary, not supported by default osC for individual products.
$ship_from = 1;
$default_ship_from = "USD"; //this is not binary, not supported by default osC for individual products.
$condition = 1; 
$default_condition = "used"; // CHANGE TO SUIT YOUR STORE
$feed_exp_date = date('Y-m-d', time() + 2592000 );

//END of Advance Optional Values

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 AS prodModel, products_weight,
manufacturers.manufacturers_name AS mfgName,
manufacturers.manufacturers_id,
products.products_id AS id,
products_description.products_name AS name,
products_description.products_description AS description,
products.products_quantity AS quantity,
products.products_status AS prodStatus,
FORMAT( IFNULL(specials.specials_new_products_price, products.products_price) * " . $taxCalc . ",2) AS price,
CONCAT( '" . $imageURL . "' ,products.products_image) AS image_url,
products_to_categories.categories_id AS prodCatID,
categories.parent_id AS catParentID,
categories_description.categories_name AS catName
FROM categories,
categories_description,
products,
products_description,
products_to_categories

left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id )
left join specials on ( specials.products_id = products.products_id AND ( ( (specials.expires_date > CURRENT_DATE) OR (specials.expires_date = 0) ) AND ( specials.status = 1 ) ) )

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 categories.categories_id=categories_description.categories_id
AND products.products_status != 0
AND products.products_price != 0
AND products.products_price != ''
ORDER BY
products.products_id ASC
";
$catInfo = "
SELECT
categories.categories_id AS curCatID,
categories.parent_id AS parentCatID,
categories_description.categories_name AS catName
FROM
categories,
categories_description
WHERE categories.categories_id = categories_description.categories_id
";

function findCat($curID, $catTempPar, $catTempDes, $catIndex)
{
if( (isset($catTempPar[$curID])) && ($catTempPar[$curID] != 0) )
{
	if(isset($catIndex[$catTempPar[$curID]]))
	{
		$temp=$catIndex[$catTempPar[$curID]];
	}
	else
	{
		$catIndex = findCat($catTempPar[$curID], $catTempPar, $catTempDes, $catIndex);
		$temp = $catIndex[$catTempPar[$curID]];
	}
}
if( (isset($catTempPar[$curID])) && (isset($catTempDes[$curID])) && ($catTempPar[$curID] == 0) )
{
	$catIndex[$curID] = $catTempDes[$curID];
}
else
{
	$catIndex[$curID] = $temp . " > " . $catTempDes[$curID];
}
return $catIndex;

}

$catIndex = array();
$catTempDes = array();
$catTempPar = array();
$processCat = mysql_query( $catInfo )or die( $FunctionName . ": SQL error " . mysql_error() . "| catInfo = " . htmlentities($catInfo) );
while ( $catRow = mysql_fetch_object( $processCat ) )
{
$catKey = $catRow->curCatID;
$catName = $catRow->catName;
$catParID = $catRow->parentCatID;
if($catName != "")
{
	$catTempDes[$catKey]=$catName;
	$catTempPar[$catKey]=$catParID;
}
}

foreach($catTempDes as $curID=>$des)  //don't need the $des
{
$catIndex = findCat($curID, $catTempPar, $catTempDes, $catIndex);
}

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

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

$output = "link \t title \t description\t expiration_date \t label \t price \t image_link \t id";

//create optional section
if($optional_sec == 1)
{
if($instock == 1)
	$output .= "\t instock ";
if($shipping == 1)
	$output .= "\t shipping ";
if($brand == 1)
	$output .= "\t brand ";
if($upc == 1)
	$output .= "\t upc ";
if($manufacturer_id == 1)
	$output .= "\t manufacturer_id ";
if($feed_quantity == 1)
	$output .= "\t quantity";
if($payment_accepted == 1)
	$output .= "\t payment_accepted";
if($product_type == 1)
	$output .= "\t product_type ";
if($currency == 1)
	$output .= "\t currency ";
if($feed_language == 1)
	$output .= "\t language ";
if($ship_to == 1)
	$output .= "\t ship_to ";
if($ship_from == 1)
	$output .= "\t ship_from ";
if($condition == 1) 
	$output .= "\tcondition"; 
}
$output .= "\n";
$result=mysql_query( $sql )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql = " . htmlentities($sql) );

//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 );
}

$loop_counter = 0;

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

if( $row->prodStatus == 1 || ($optional_sec == 1 && $instock == 1) )
{

	if($convertCur)
	{
		$row->price = ereg_replace("[^.0-9]", "", $row->price);
		$row->price = $row->price *  $row3->curUSD;
		$row->price = number_format($row->price, 2, '.', ',');
	}

	if ($imageURL == $row->image_url) {
		$image = "";
	}else{
		$image = $row->image_url;
	}
	$label="";
	if ($row->name != ""){
		$label .= preg_replace($_strip_search,
$_strip_replace, strip_tags( strtr($row->name,
$_cleaner_array) ) );
	}
	if ($row->prodModel != "" && $row->name != ""){
		$label .= ",". $row->prodModel;
	}else{
		$label .= $row->prodModel;
	}
	if ($row->mfgName != "" && ($row->prodModel != "" 
|| $row->name != "")){
		$label .= ",". $row->mfgName;
	}else{
		$label .= $row->mfgName;
	}
	$output .= $row->product_url . "\t" .
	preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->name, $_cleaner_array) ) ) . "\t" .
	preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->description, $_cleaner_array) ) ) . "\t" .
	$feed_exp_date . "\t" .
	$label . "\t" .
	$row->price . "\t" .
	$image . "\t" .
	$row->id;

//optional values section
if($optional_sec == 1)
{
	if($instock == 1)
	{
		if($row->prodStatus == 1)
		{
			$prodStatusOut = "Y";
		}
		else
		{
			$prodStatusOut = "N";
		}
		$output .= " \t " . $prodStatusOut;
	}
	if($shipping == 1)
		$output .= " \t " . $lowestShipping;
	if($brand == 1)
		$output .= " \t " . $row->mfgName;
	if($upc == 1)
		$output .= " \t " . "Not Supported";
	if($manufacturer_id == 1)
		$output .= " \t " . "Not Supported";
	if($feed_quantity == 1)
		$output .= " \t " . $row->quantity;
	if($payment_accepted == 1)
		$output .= " \t " . $default_payment_methods;
	if($product_type == 1)
	{
		$catNameTemp = strtolower($catName);
		$output .= " \t " . $row->catName;
	}
	if($currency == 1)
		$output .= " \t " . $default_currency;
	if($feed_language == 1)
		$output .= " \t " . $default_feed_language;
	if($ship_to == 1)
		$output .= " \t " . $default_ship_to;
	if($ship_from == 1)
		$output .= " \t " . $default_ship_from;
	if($condition == 1)	
		$output .= " \t " . $default_condition; 
}
$output .= " \n";
}
$already_sent[$row->id] = 1;
$loop_counter++;
if ($loop_counter>750) {
$fp = fopen( $OutFile , "a" );
$fout = fwrite( $fp , $output );
fclose( $fp );
$loop_counter = 0;
$output = "";
}
}

$fp = fopen( $OutFile , "a" );
$fout = fwrite( $fp , $output );
fclose( $fp );
echo "File completed: " . $destination_file . "<br>\n";
chmod($OutFile, 0777);
//Start FTP to Froogle

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 Froogle

//  End TIMER
//  ---------
$etimer = explode( ' ', microtime() );
$etimer = $etimer[1] + $etimer[0];
echo '<p style="margin:auto; text-align:center">';
printf( "Script timer: <b>%f</b> seconds.", ($etimer-$stimer) );
echo '</p>';
//  ---------

?>

Just one note: This code will send products with a quantity of 0 if you still have it marked as active. This should not generate an error with google base unless you chose google checkout as a payment method. You must have a quantity of at least 1 for google checkout. The feed will still go through and all your products with a quantity of 1 will still be approved, but you will see an error on your upload page for each product with a quantity of 0.

Will this include the "$expiration_date" in the feed to base google?

Edited February 4, 2007 by pbpools

Rachael w. · February 4, 2007

yes, it does include the expiration date (30 days from the day you create the feed)

ezfindit · February 4, 2007

Rachael,

Thanks so much for sharing your changes. :thumbsup: I merged them with my own version of Froogle and it works GREAT! :D

There's still an issue I am encountering with the Encoding Option (UTF-8) that I'm puzzled about. If I upload the txt Output file from the Froogle program "as is" I get many "Encoding Errors" from Google.

However, if I open the txt Output file in Notepad, Save it as "Text", with Encoding = "UTF-8", and then upload it to Google Base it loads perfectly with no errors.

Is there some change needed in the program to avoid having to open the txt Output file and re-saving it with UTF-8 encoding before uploading to Google Base? I have researched this issue all over the forums and the web but have found nothing to help me. B)

Thanks again for help, it's much appreciated! :thumbsup: :thumbsup: :thumbsup:

Edited February 4, 2007 by ezfindit

Rachael w. · February 4, 2007

I wish I could help on the encoding but I have no idea why it would be doing that. Not that it is necessarily a hard problem to solve, only that I have no clue how to solve it :)

I'm glad you were able to make the changes successfully.

knott · February 27, 2007

Rachael,

Thanks so much for sharing your changes. :thumbsup: I merged them with my own version of Froogle and it works GREAT! :D

There's still an issue I am encountering with the Encoding Option (UTF-8) that I'm puzzled about. If I upload the txt Output file from the Froogle program "as is" I get many "Encoding Errors" from Google.

However, if I open the txt Output file in Notepad, Save it as "Text", with Encoding = "UTF-8", and then upload it to Google Base it loads perfectly with no errors.

Is there some change needed in the program to avoid having to open the txt Output file and re-saving it with UTF-8 encoding before uploading to Google Base? I have researched this issue all over the forums and the web but have found nothing to help me. B)

Thanks again for help, it's much appreciated! :thumbsup: :thumbsup: :thumbsup:

I used to manually make froogle feeds from another shopping cart system - this involved opening the xml file in excel and combing the data so it was correct for froogle.

I had a simular error on some occasions & it was caused by invalid characters... things like TM symbol or C sometimes even '.

I dont really understand what was happening but maybe by changing the encoding it replaces the unrecognized characters with somthing acceptable.

This is only a guess as since moving to OSC and using Automatic Store Feeds v3.0 no matter what text is pasted in it always seems to work ok.

ceddy · March 10, 2007

not sure if it is causing a problem yet, but i did notice this contribution outputs a space before the new fields required by google

example " new" " Y" etc

ezfindit · July 2, 2007

New error is now showing up when loading to Google Base:

"This attribute has been deprecated. - label 1 "

and

"This attribute has been deprecated. - shipping 1 "

It appears it has to do with how Google is handling Shipping charges. Here's the Google Base Discussion Thread on this issue: http://groups.google.com/group/base-help-b...aac11519bd5dc7e

Anyone else having the same problem? How do we fix this? :blush:

Google Base - New Attributes Requirement for Data Feeds

Recommended Posts

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Create an account or sign in to comment

Create an account

Sign in