Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Automatically send data feed to Froogle


gottaloveit

Recommended Posts

Forgive me if this has been talked about, but I could not find a solution, so I figured I would post it. I particularly did not want to worry about sending my data feed to Google / Froogle once a month, as they expire your listings monthly, unless you resend your feed. Also, per their feed requirements, and because my store is relatively small and simple, I needed to modify what Easy Contribute outputted.

 

I decided to set up a CRON job that actually runs every 3 weeks, just to stay a little ahead of Google expiring my listings. The CRON calls a MySQL script that I wrote to export my products.

 

A couple notes so I don't get too flamed B)

  • The product line in our store does not change that often, so I did not incorporate limiting the export to ACTIVE products. If you have inactive products, you might need to change the script.
  • Also, my category levels are only 2 deep, and the 2nd level would not make too much sense to Froogle. Again, if you have a different method for your categories and naming, you might need to change the script.
  • Also, even though I got my feed approved / validated by Google, please make sure to go through the process to get it validated by Google for your store.

mysql -uXXXX -pXXXXX -DXXXXX -e "select concat('http://YOUR_DOMAIN_GOES_HERE/product_info.php?products_id=',products.products_id) AS product_url, products_description.products_name AS name, products_description.products_description AS description, FORMAT(products.products_price,2) AS price, CONCAT('http://YOUR_DOMAIN_GOES_HERE/images/',products.products_image) AS image_url, categories_description.categories_name AS category FROM categories, products, products_description, categories_description, products_to_categories 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_description.categories_id=IF(categories.parent_id='0',categories.categories_id,categories.parent_id)" > /tmp/FILENAME_PER_GOOGLE_SPECS.txt;

 

Once the script is performed, I have CRON FTP the file to Google. Now, I never have to worry about it :D

Link to comment
Share on other sites

:huh: Looks Good, ermmm!

 

 

:D :lol: :D Joking Man, thats one great script and you have really shown that anythihng is possible, the whole point of OSC, I think you should add a friendlyier verion to contribs as that requires alittle more scripting and "Voila".

 

Keep it up man!!!

Link to comment
Share on other sites

  • 3 weeks later...

I took the SQL listed above and added parent category to it. To automate add the following file to your site (somewhere - mine is at /admin/froogle.php) - look later and I'll list the cron entry:

 

<?php

$OutFile = "<route to your unix directory>/<frooglefilename>.txt" ;

$destination_file = "<frooglefilename>.txt" ;

$source_file = $OutFile ;

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

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

 

 

$home = "localhost" ;

$user="<mysql user id>";

$pass="<mysql password>";

$base="<mysql database>";

 

$ftp_server = "hedwig.google.com" ;

$ftp_user_name = "<froogle login>" ;

$ftp_user_pass = "<froogle password>" ;

 

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_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 )

 

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

" ;

 

 

$_strip_search = array(

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

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

$_strip_replace = array(

'',

'');

 

 

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

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

while( $row = mysql_fetch_object( $result ) )

{

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

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

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

$row->price . "\t" .

$row->image_url . "\t" .

$row->category . "\n" ;

}

if ( file_exists( $OutFile ) )

unlink( $OutFile ) ;

 

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

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

fclose( $fp ) ;

 

 

// 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 failed!<BR>";

} else {

echo "Uploaded $source_file to $ftp_server as $destination_file<BR>";

}

 

// close the FTP stream

ftp_close($conn_id);

?>

 

 

 

**** Now the cron entry

0 4 * * 3 /usr/bin/php -q /home/<unix route to your file>/admin/froogle.php

 

 

Regards

 

Dylan

Edited by Vger
Link to comment
Share on other sites

Hi All,

 

Great script. :D

Still I have a question, how can I got the price including tax.

The now selected price is excluding tax.

 

Also is it possible to make some calculation for the delivery cost?

 

I'm not so familair with sql so who can help

 

Thnks

 

Jan :D

Link to comment
Share on other sites

Just wondering how this script is different from the current Froogle exporter in the contributions section.

 

You guys re-inventing the wheel?

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

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

Nope - not reinventing the wheel. The easypopulate froogle feed needs someone logged into the admin console. The contrib you mention has the same limitation. This feed can be automated from Cron, it does the lot including FTPing - no manual intervention.

 

Tax and shipping - I didn't think Froogle wanted that info.

 

Dylan

Link to comment
Share on other sites

I think that the contribution 'Froogle Exporter' can be run from cron as well.

 

However, since we have our own cron initiated froogle exporter solution, I can't honestly say that I've looked into it very far.

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

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

Hi Dylan,

 

I know that Froogle don't want that. They also don't want my store because

the used langauage is not in dutch.

My store is located in the Netherlands and also for the Dutch market.

 

Here we have also a posibility to do upload to specials sites like froogle.

So I want to use this script for doint this. And of course they want to have the

mentioned price including tax, also they want to know shipping/deliver cost even

till when the special price is available.

 

So that's the reason for my question.

 

So how must I add the above mentioned fields.

 

Thanks in advantage

 

Jan

Link to comment
Share on other sites

For tax, the field product.products_tax_class_id gives you how the item is to be taxed, and a function tep_get_tax_rate (found in general.php) gives you the SQL/PHP to convert this into a value. You could combine all this into the SQL above but its going to take a bit of playing to ensure you get everything correct.

 

Shipping is more diffidult as its based on the person's location and your location. There's a 'shipping_estimator.php' module that contains the code - I think this is an add-on.

 

Dylan

Link to comment
Share on other sites

When I tried to run this I get these errors

 

Warning: fopen(/home/dropshi/public_html/dropshipvideo.txt): failed to open stream: Permission denied in /home/dropshi/public_html/froogle.php on line 75

 

Warning: fwrite(): supplied argument is not a valid stream resource in /home/dropshi/public_html/froogle.php on line 76

 

Warning: fclose(): supplied argument is not a valid stream resource in /home/dropshi/public_html/froogle.php on line 77

Connected to hedwig.google.com, for user dropshipvideo

FTP upload has failed!

 

Lines 75, 76 and 77 are

 

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

 

I've tried chmod 666, 755, 766, 777 on froogle.php

Keith

 

 

What the hell was I thinkin'?

Link to comment
Share on other sites

$OutFile you defined yourself (hopefully) - I defined mine to (OSC homedir)/temp/froogle.txt. Make sure your output directory exists and is writable (you were chmod the code - need to chmod the output directory). Make it a temp directory - get it away from the OSC code - also allows expansion to other shopping engines (see later).

 

BTW I changed the SQL just a tad ... it was including out of stock items so I added a criteria to stop that. The new sql code is as follows:

 

$sql = "

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

products_model , products_weight ,

manufacturers.manufacturers_name ,

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

" ;

 

I've also expanded to include Yahoo Shopping, I'm working on shopping.com (ugly ... needs at least 3 new fields in the database), then to the rest (bizrate, etc). If you want the Yahoo shopping version PM me and I'll email you the code.

 

Dylan

Link to comment
Share on other sites

I changed the output from

/home/dropshi/public_html/dropshipvideo.txt

 

to this

/home/dropshi/public_html/temp/dropshipvideo.txt

 

and it works perfectly!!!!!

 

Connected to hedwig.google.com, for user dropshipvideo

Uploaded /home/dropshi/public_html/temp/dropshipvideo.txt to hedwig.google.com as dropshipvideo.txt

Keith

 

 

What the hell was I thinkin'?

Link to comment
Share on other sites

Dylan-

 

This code doesn't seem to work for me

$sql = "
select concat( '" . PRODUCTURL . "' ,products.products_id) AS product_url, 
products_model , products_weight ,
manufacturers.manufacturers_name ,
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
";

 

In the output file the product url is listed as PRODUCTURL with a number on the end for every item in the store. The first code you posted has the product url listed right. Maybe it's just me?

Keith

 

 

What the hell was I thinkin'?

Link to comment
Share on other sites

How would I modify the SQL query to ignore duplicate products?

 

The other thing that Froogle does not like is products that are in multiple categories.

 

I have a bunch of categories that also have child categories. I sell golf clubs, and an example of this is:

 

Irons

Irons -> Left Handed

Irons -> Left Handed -> Kids Clubs

 

In this case, the same product is in all three categories - linked of course so it's really only one product record.

 

I have many of these, and have to "choose" the top category to report them from. I'm not really sure how to approach this one yet - whether it makes sense to try to modify the SQL to only pull out unique product names, or to post process the data and discard all the products that are duplicates.

 

Of course, I also have valid subcategories that have products in them that are NOT in a parent, so I can't simply drop everything that is in a subcategory either.

 

Any suggestions?

 

-t

Link to comment
Share on other sites

wvmit: I was using a define at the top of the code I didn'tinclude. I've gone away from that so I can cut and paste the SQL between PHP and mySQL.

 

I have expanded the whole code to feed shopping.com, bizrate.com and no doubt more will follow. However my paying job (I own an SEO company) has dragged me away to programming ASP for a few days. I have added a few custom table columns to the mix:

shopeng_shopping_com - shopping.com has its own categories you need to use - this is a lookup table for them.

products.products_MPN , products.products_UPC - both needed for shopping.com & bizrate.com.

 

If you don't plan on using either system then these lines can be deleted.

 

The current SQL is as follows:

$ImageURL = 'http://www.custommadecrafts.com/images/' ;

$ProductURL = 'http://www.custommadecrafts.com/product_info.php?products_id=' ;

$StandardDelimiter = " > " ;

 

$sql = "

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

products_model , products_weight , products_quantity ,

manufacturers.manufacturers_name ,

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( '$StandardDelimiter' , catdescparent.categories_name , catdesccurrent.categories_name ) AS category ,

categories.shopping_com_id ,

shopeng_shopping_com.shopping_category ,

products.products_MPN ,

products.products_UPC

 

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 )

left join shopeng_shopping_com on ( shopeng_shopping_com.shopping_com_id = categories.shopping_com_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

" ;

 

 

thewitt:

You have a nasty dilemma. You can't use SQL - at least not without subselects and other deep SQL changes, perhaps an associative PHP array will help. Do you know or care which one of your duplicate products is output? If not something alone the lines of (I haven't tested this):

 

 

$AlreadySent = array() ;

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

while( $row = mysql_fetch_object( $result ) )

{

if ( $AlreadySent[ $row->products_model ] == 1 )

continue ;

 

$AlreadySent[ $row->products_model ] = 1 ;

 

....format output bit.

} // end of while loop

 

If you care which ones are output - how can you tell which ones to do?

 

Dylan

Link to comment
Share on other sites

Thanks for the suggestion Dylan.

 

I decided to simply sort the output by the product name and then as I walk the result rows, I queue them if I find a duplicate product name and then throw out all but one of them.

 

I keep the one in a parent category if possible. If there isn't one in just a parent, I keep a random child.

 

Froogle is happy now.

 

-t

Link to comment
Share on other sites

I sent my feed to froogle and got back this email

 

Thank you for submitting your data feed. We have reviewed your feed and

need you to make some changes so that we can successfully process your

feed.

 

Please make sure that the fields in your header are separated by tabs. To

successfully create a feed, please try doing the following in a new Excel

worksheet:

 

Step 1: Create the header

 

The required fields are product_url, name, description, price, image_url,

and category.

 

1. In the first cell, type product_url and then press the Tab key to move

to the next cell.

2. In the second cell, type name and then press the Tab key to move to the

next cell.

3. Continue this process using all the required field names in the header.

4. After you type category, instead of pressing the Tab key, press Enter.......

Is the script not formatting the output correctly?

 

Here's my froogle.php

<?php
$OutFile = "/home/wvdropsh/public_html/temp/wvdropshipping.txt";
$destination_file = "wvdropshipping.txt";
$source_file = $OutFile;
$imageURL = 'http://www.wvdropshipping.com/images/images_big/';
$productURL = 'http://www.wvdropshipping.com/product_info.php?products_id=';


// Now the cron entry
//0 4 * * 3 /usr/bin/php -q /home/wvdropsh/public_html/admin/froogle.php

$home = "localhost";
$user="*******";
$pass="*******";
$base="***********";

$ftp_server = "hedwig.google.com";
$ftp_user_name = "wvdropshipping";
$ftp_user_pass = "********";

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_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 )

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
";





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


$output = "product_url name description price image_url category\n";
$result=mysql_query( $sql )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql = " . htmlentities($sql) );
while( $row = mysql_fetch_object( $result ) )
{
$output .= $row->product_url . "\t" .
preg_replace($_strip_search, $strip_replace, strip_tags( $row->name ) ) . "\t" .
preg_replace($_strip_search, $strip_replace, strip_tags( $row->description ) ) . "\t" .
$row->price . "\t" .
$row->image_url . "\t" .
$row->category . "\n";
}
if ( file_exists( $OutFile ) )
unlink( $OutFile );

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



// 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 failed!<BR>";
} else {
echo "Uploaded $source_file to $ftp_server as $destination_file<BR>";
}

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

Keith

 

 

What the hell was I thinkin'?

Link to comment
Share on other sites

Looks OK (though a very early version of the code). Take the output file and load it into Excel or equivalent as tab delimited see if all the columns line up.

 

As for specials, I wasted an hour on Monday looking at correlated subqueries, dang I couldn't get the things to work. Probably a left join with some programmic logic will do the trick - probably a better solution anyway. Let me look at it and see what I can get happening.

 

Dylan

Link to comment
Share on other sites

  • 2 weeks later...
  • 3 weeks later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...