3289 replies to this topic

[gottaloveit]

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  

• 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  

[ChrisHoward]

Looks Good, ermmm!


     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!!!

[gottaloveit]

Thanks ctechgroup, and yeah, I have already started working on pulling the script into osCommerce and as a contrib.  Stay tuned.

[tbell45219]

Can all this (including the script) be put in a cron job? If so, can you please tell me the code to put in cron?

Thanks.

[dyland]

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, 01 January 2006 - 03:48 PM.

[JanR]

Hi All,

Great script.  
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

[wizardsandwars]

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

You guys re-inventing the wheel?

[dyland]

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

[wizardsandwars]

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.

[JanR]

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

[dyland]

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

[wvmlt]

When I tried to run this I get these errors

Quote

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

[dyland]

$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

[wvmlt]

I changed the output from

/home/dropshi/public_html/dropshipvideo.txt

to this

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

and it works perfectly!!!!!

Quote

Connected to hedwig.google.com, for user dropshipvideo
Uploaded /home/dropshi/public_html/temp/dropshipvideo.txt to hedwig.google.com as dropshipvideo.txt

[wvmlt]

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?

[thewitt]

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

[dyland]

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

[jloos]

hi !

What about specials........ ???? this one does not generate a list with the actual special prices ....... ??

anyone... ?

Hans

Edited by jloos, 29 January 2004 - 12:12 PM.

[thewitt]

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

[jcall]

Tim, do you do that programatically?  If so, can you share?  I have the same problem.

TIA

-jared