Jump to content
gottaloveit

Automatically send data feed to Froogle

Recommended Posts

Ok Thanks Jack,

 

The previous code you gave me is almost there and I have spent hours trying to sort it to no avail.

 

I will make a post under General support to see if anyone can spot what needs changing.

 

Thanks


Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Share this post


Link to post
Share on other sites

Sorry in advance if I missed the answer to this question (this thread is long), but I am getting the following error:

"Missing Unique Product Identifiers (50 items)

 

Some of your items do not have valid unique product identifiers. Please add valid unique product identifiers. Otherwise it's possible that none of your items will appear in Google search results."

 

 

This is almost all of my items, is there an easy way around this? I don't have a box in my admin to enter UPC or anything. I am not sure if I need to create one (or how) or if there is some simple way in the code of the mod that I can do it.

 

Thanks for the help in advance, great of you to keep this up!

Share this post


Link to post
Share on other sites

Sorry in advance if I missed the answer to this question (this thread is long), but I am getting the following error:

"Missing Unique Product Identifiers (50 items)

Usually, all that is needed is to enalbe the brand and mpn settings.

Share this post


Link to post
Share on other sites

My Store is Based in the UK and I sell some items that are taxable and some that are not taxable (Vat at 20%) eg. childrens clothing is not taxable but adults clothing is. Using the Google feeder in its standard form added tax to my non-taxable items resulting in warnings from Google for price missmatches between my data-feed and prices displayed on the store, and threats of impending account suspension.

 

After several days trying to alter the SQL of admin/googlefeeder.php to recalculate based on the products tax class without success Sakwoya suggested some php code to achieve the desired result. After playing with the code for a day or so (Im no PHP expert) I have managed to get the data-feed to correctly price the taxable and non-taxable products.

 

The changes required are as below and are probably not well coded - but they do work.

 

All the changes are ito file: Admin/googlefeeder.php

 

find:

products.products_quantity AS quantity,

Add After:

products.products_tax_class_id AS taxid,

 

Find:

	  $output .= $row->product_url . $google_utm . "\t";
  }

Add After:

// Non Taxable Items Change - strips off tax added above - change division if tax rate changes
$the_price=$row->price;
if ($row->taxid ==0){$the_price=($the_price/1.2);}
// EOF Non Taxable Items Change

 

Find:

$row->price . "\t" .

 

Replace With:

//	  $row->price . "\t" .
  $the_price."\t".

 

Thats It !

 

For this to work your taxable items must have a tax class id of 1 and non taxable items must have a tax class of 0.

 

Hope this helps someone - as it made me pull my hair out !!


Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Share this post


Link to post
Share on other sites

I just pgraded to version 2.9, and when I try to upload our items, I am getting the following error:

 

FTP open connection failed to uploads.google.com

 

I have made sure that the username and password are correct in the googlefeeder.php file. What else could be wrong?

 

Thanks!

Share this post


Link to post
Share on other sites

I just pgraded to version 2.9, and when I try to upload our items, I am getting the following error:

 

FTP open connection failed to uploads.google.com

 

I have made sure that the username and password are correct in the googlefeeder.php file. What else could be wrong?

 

Thanks!

There's nothing different in the upload code so if the previous version worked, the login is all it could be, I think. You might want to doublecheck the username/password to be sure they are the same as before.

Share this post


Link to post
Share on other sites

I just pgraded to version 2.9, and when I try to upload our items, I am getting the following error:

 

FTP open connection failed to uploads.google.com

 

I have made sure that the username and password are correct in the googlefeeder.php file. What else could be wrong?

 

Thanks!

 

If you cannot get the ftp to work you can, instead, tell Google to go and collect the file itself - look at the bit under 'Automatic Upload' here:

http://support.google.com/merchants/bin/answer.py?hl=en&answer=188477

 

Set the feed to run as a cron job and then give Google a time to collect a few hours after that.

 

Graeme

Share this post


Link to post
Share on other sites

Hi Jack or anybody that may know..

 

Google requires us to modify our listing to show the price per minimum order.

So I replaced

FORMAT( IFNULL(specials.specials_new_products_price, products.products_price) * " . $taxCalc . ",2) AS price,

With

FORMAT( IFNULL(specials.specials_new_products_price, products.products_price) * products.minorder * " . $taxCalc . ",2) AS price,

 

This works great.

 

But I'm having trouble finding where the title is getting generated, as I would like the output to be `title` && (pack of `minimum order`)

 

Modifying our actual title wouldn't make sense because they are not sold in packs, you can order 20 or 21, but not 19. For Google display reasons we would like our price we are required to give them to be indicitive of the amount they would receive.

 

Can you help with this?

 

Thanks,

Don

Share this post


Link to post
Share on other sites

Hi Jack or anybody that may know..

 

Google requires us to modify our listing to show the price per minimum order.

So I replaced

FORMAT( IFNULL(specials.specials_new_products_price, products.products_price) * " . $taxCalc . ",2) AS price,

With

FORMAT( IFNULL(specials.specials_new_products_price, products.products_price) * products.minorder * " . $taxCalc . ",2) AS price,

 

This works great.

 

But I'm having trouble finding where the title is getting generated, as I would like the output to be `title` && (pack of `minimum order`)

 

Modifying our actual title wouldn't make sense because they are not sold in packs, you can order 20 or 21, but not 19. For Google display reasons we would like our price we are required to give them to be indicitive of the amount they would receive.

 

Can you help with this?

 

Thanks,

Don

If you mean the entries in the column nmaed title, those are generated with this line
  preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->name, $_cleaner_array) ) ) . "\t" .

If that is not what you mean, please explain further and I will take another look.

Share this post


Link to post
Share on other sites

If you mean the entries in the column nmaed title, those are generated with this line

  preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->name, $_cleaner_array) ) ) . "\t" .

If that is not what you mean, please explain further and I will take another look.

That is exactly what I meant, thanks Jack. I'll take a crack a modifing that to include the min-order number.

 

Thanks,

Don

Share this post


Link to post
Share on other sites

I tried Jack,

 

Just can't figure this one out.

 

I just need to add text to the name title that says Pack of $minorder

 

But I don't understand your coding enough to make it work.

 

I tried adding below this

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

while $result=mysql_fetch_array($sql) {

$minorder =$row[minorder]

}

Then changing

preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->name, $_cleaner_array) ) ) . "\t" .

to

preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->name, $_cleaner_array) ) ) . "(Pack of " . $minorder . ")". "\t" .

 

That just causes a infinite loop.

 

I tried a few others things but I just can't seem to get that $minorder appended to anything. I'm able to use the products.minorder column in the $sql query but not able to use its value anywhere else.

 

Any help is appreciated,

Don

Share this post


Link to post
Share on other sites

I tried Jack,

 

Just can't figure this one out.

 

I just need to add text to the name title that says Pack of $minorder

 

But I don't understand your coding enough to make it work.

 

I tried adding below this

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

while $result=mysql_fetch_array($sql) {

$minorder =$row[minorder]

}

Then changing

preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->name, $_cleaner_array) ) ) . "\t" .

to

preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->name, $_cleaner_array) ) ) . "(Pack of " . $minorder . ")". "\t" .

 

That just causes a infinite loop.

 

I tried a few others things but I just can't seem to get that $minorder appended to anything. I'm able to use the products.minorder column in the $sql query but not able to use its value anywhere else.

 

Any help is appreciated,

Don

I don't know think that sql code you are using is correct, from the looks of it. But it shouldn't be necessary if $row[minorder] exists. In that case, this should work
preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->name, $_cleaner_array) ) ) . "(Pack of " . $row[minorder] . ")". "\t" .

Share this post


Link to post
Share on other sites
I don't know think that sql code you are using is correct, from the looks of it. But it shouldn't be necessary if $row[minorder] exists. In that case, this should work
preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->name, $_cleaner_array) ) ) . "(Pack of " . $row[minorder] . ")". "\t" .

 

Yep you are correct,

The sql query wasn't formatted correctly

 

 

Took a lot of trys but this finally worked

 

Changed the $sql query to

$sql = "
SELECT concat( '" . $productURL . "' ,products.products_id) AS product_url,
products_model AS prodModel,
manufacturers.manufacturers_id,
products.products_id AS id,
products.minorder AS minorder,
products_description.products_name AS name,
products_description.products_description AS description,
products.products_quantity AS quantity,
products.products_status AS prodStatus,
products.products_weight AS prodWeight, " . $extraFields . "
FORMAT( IFNULL(specials.specials_new_products_price, products.products_price) * products.minorder * " . OPTIONS_TAX_CALC . ",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) 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 " . $quotes . "
AND categories_description.language_id = " . DEFAULT_LANGUAGE_ID . "
AND products_description.language_id = " . DEFAULT_LANGUAGE_ID . "
ORDER BY
products.products_id ASC
";

 

Added an if statement above the output for the name

if ( $row->minorder > 1) {
            $minorder="(Pack of " . $row->minorder . ")";
       } else {
           $minorder="";
       }

Then added in the variable into the statement

preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->name, $_cleaner_array) ) ) .  $minorder . "\t" .

 

Many thanks for your time and guidance, and knowledge!

Share this post


Link to post
Share on other sites

Many thanks for your time and guidance, and knowledge!

You're welcome. I'm glad it worked out. :)

Share this post


Link to post
Share on other sites

Cron jobs are internal and should not be bothered by password protection. You should ask your host how to set it up. If you can't, you can use the Cron Simulator contribution.

 

Jack, until recently my google feed cron (set up as per your instructions in the install files) worked but something must have changed on my hosts end; meaning the cron ceased to work. I contacted the host support for syntax to pass the username and password along in the cron but the tech guy said it couldn't be done.

 

I see on the googlebase site I could set up a scheduled upload there, There were input boxes for the username and password, which I entered. A test run last night worked, but I'd have preferred not to put the shops username and password on the google site.

 

I tried search (google) for the cron syntax but came up with zero, so for now I'll rely on the googlebase site to run automated uploads.

 

Just FYI, thanks


I am not a professional webmaster or PHP coder by background or training but I will try to help as best I can.

I remember what it was like when I first started with osC. It can be overwhelming.

However, I strongly recommend considering hiring a professional for extensive site modifications, site cleaning, etc.

There are several good pros here on osCommerce. Look around, you'll figure out who they are.

Share this post


Link to post
Share on other sites

Jack, until recently my google feed cron (set up as per your instructions in the install files) worked but something must have changed on my hosts end; meaning the cron ceased to work. I contacted the host support for syntax to pass the username and password along in the cron but the tech guy said it couldn't be done.

 

I see on the googlebase site I could set up a scheduled upload there, There were input boxes for the username and password, which I entered. A test run last night worked, but I'd have preferred not to put the shops username and password on the google site.

The username/password can be passed via cron but the code is not setup to work that way so it wouldn't make any difference. Plus, I don't understand why you would need that in the first place since the user/pass are in the file. As for the google scheduler, that is safe but you will have to remember to manually run the googlebase script to make sure your products are accurate.

Share this post


Link to post
Share on other sites

The username/password can be passed via cron but the code is not setup to work that way so it wouldn't make any difference. Plus, I don't understand why you would need that in the first place since the user/pass are in the file. As for the google scheduler, that is safe but you will have to remember to manually run the googlebase script to make sure your products are accurate.

 

I am not sure why that quit working.

 

I have four different crons set up on my sites, the only one not working is the googlefeeder cron. But it did work once.

 

I was presuming because googlefeeder is under the .htaccess password protected area that might be the cause, especially if the host changed something?

 

Just to see what happens, I will delete the current googlefeeder cron and set up another to see if that does anything.

 

I'm just taking a stab in the dark here, as the only other thing that occurred I am aware of on my host is I had my shops consolidated to a VPS acct. I presume (presuming is what I do best) that the host did something that may have effected things.


I am not a professional webmaster or PHP coder by background or training but I will try to help as best I can.

I remember what it was like when I first started with osC. It can be overwhelming.

However, I strongly recommend considering hiring a professional for extensive site modifications, site cleaning, etc.

There are several good pros here on osCommerce. Look around, you'll figure out who they are.

Share this post


Link to post
Share on other sites

I am not sure why that quit working.

 

I have four different crons set up on my sites, the only one not working is the googlefeeder cron. But it did work once.

 

I was presuming because googlefeeder is under the .htaccess password protected area that might be the cause, especially if the host changed something?

 

Just to see what happens, I will delete the current googlefeeder cron and set up another to see if that does anything.

 

I'm just taking a stab in the dark here, as the only other thing that occurred I am aware of on my host is I had my shops consolidated to a VPS acct. I presume (presuming is what I do best) that the host did something that may have effected things.

It might be that the settings are different on the new server. Cron doesn't need login for the secure areas since it is handled from the server and has access already. You should check, if you haven't already, that the googlebase script can be manually ran from admin. If it can, then the cron job is probably failing due to a server setting, assuming the admin's name hasn't changed. If it is failing, then your host should be able to run it from their side to see why it is failing.

Share this post


Link to post
Share on other sites

It might be that the settings are different on the new server. Cron doesn't need login for the secure areas since it is handled from the server and has access already. You should check, if you haven't already, that the googlebase script can be manually ran from admin. If it can, then the cron job is probably failing due to a server setting, assuming the admin's name hasn't changed. If it is failing, then your host should be able to run it from their side to see why it is failing.

 

Jack the script does run manually from admin, that's how I've been updated my feeds until I got around to tackling this. The three other crons run, a couple are within the admin folder (these are other add ons of yours). In my four shops with this add on, all crons work except this one.

 

I contact my host support and submitted a ticket for the one shop. The tech guy said the script did generate errors as follows:

 

 

PHP Warning: require(includes/configure.php): failed to open stream: No such file or directory in /home/my_user_name/public_html/my_admin/includes/application_top.php on line 29 PHP Warning: require(includes/configure.php): failed to open stream: No such file or directory in /home/my_user_name/public_html/my_admin/includes/application_top.php on line 29 PHP Fatal error: require(): Failed opening required 'includes/configure.php' (include_path='.:/usr/lib/php:/usr/local/lib/php:/home/my_user_name/php') in /home/my_user_name/public_html/my_admin/includes/application_top.php on line 29

 

Warning: require(includes/configure.php): failed to open stream: No such file or directory in /home/my_user_name/public_html/my_admin/includes/application_top.php on line 29

 

Warning: require(includes/configure.php): failed to open stream: No such file or directory in /home/my_user_name/public_html/my_admin/includes/application_top.php on line 29

 

Fatal error: require(): Failed opening required 'includes/configure.php' (include_path='.:/usr/lib/php:/usr/local/lib/php:/home/my_user_name/php') in /home/my_user_name/public_html/my_admin/includes/application_top.php on line 29


I am not a professional webmaster or PHP coder by background or training but I will try to help as best I can.

I remember what it was like when I first started with osC. It can be overwhelming.

However, I strongly recommend considering hiring a professional for extensive site modifications, site cleaning, etc.

There are several good pros here on osCommerce. Look around, you'll figure out who they are.

Share this post


Link to post
Share on other sites

Jack the script does run manually from admin, that's how I've been updated my feeds until I got around to tackling this. The three other crons run, a couple are within the admin folder (these are other add ons of yours). In my four shops with this add on, all crons work except this one.

 

I contact my host support and submitted a ticket for the one shop. The tech guy said the script did generate errors as follows:

 

 

PHP Warning: require(includes/configure.php): failed to open stream: No such file or directory in /home/my_user_name/public_html/my_admin/includes/application_top.php on line 29 PHP Warning: require(includes/configure.php): failed to open stream: No such file or directory in /home/my_user_name/public_html/my_admin/includes/application_top.php on line 29 PHP Fatal error: require(): Failed opening required 'includes/configure.php' (include_path='.:/usr/lib/php:/usr/local/lib/php:/home/my_user_name/php') in /home/my_user_name/public_html/my_admin/includes/application_top.php on line 29

 

Warning: require(includes/configure.php): failed to open stream: No such file or directory in /home/my_user_name/public_html/my_admin/includes/application_top.php on line 29

 

Warning: require(includes/configure.php): failed to open stream: No such file or directory in /home/my_user_name/public_html/my_admin/includes/application_top.php on line 29

 

Fatal error: require(): Failed opening required 'includes/configure.php' (include_path='.:/usr/lib/php:/usr/local/lib/php:/home/my_user_name/php') in /home/my_user_name/public_html/my_admin/includes/application_top.php on line 29

The code in this contribution is different than in others since it loads from both the shop and admin so having one cron work and another fail doesn't mean anything. You can see that in the error. It is failing in admin but trying to find a file in the shop. It is, most likely, a path problem. You may need to add an explicit path to php in the cron job and then cd to the admin to run script.

Share this post


Link to post
Share on other sites

You may need to add an explicit path to php in the cron job and then cd to the admin to run script.

 

OK, I will try explicit coding to php in the cron. if that fails, plan B is to just let the Google site take care the uploads since it will do that for me.

Thanks


I am not a professional webmaster or PHP coder by background or training but I will try to help as best I can.

I remember what it was like when I first started with osC. It can be overwhelming.

However, I strongly recommend considering hiring a professional for extensive site modifications, site cleaning, etc.

There are several good pros here on osCommerce. Look around, you'll figure out who they are.

Share this post


Link to post
Share on other sites

OK, I will try explicit coding to php in the cron. if that fails, plan B is to just let the Google site take care the uploads since it will do that for me.

Thanks

Google will upload the datafeed but not create it. You'll need to remember to do that if don't use cron.

Share this post


Link to post
Share on other sites

I am using V2.9 oscommerce 2.3

 

I have three attributes defined: brand, condition, isbn

 

When I create the google feed, the condition is moved into the ISBN field if the first field is full.

If the first two fields are blank, the ISBN number is correct

 

product #1 c.brand = books, c.condition = blank, c.isbn = new

product #2 c.brand = blank, c.condition = blank, c,isbn = number

product #3 c.brand = books, c.condition = blank, c.isbn = blank

 

 

 

 

 

 

if (OPTIONS_ENABLED_ATTRIBUTES == 1)

{

$products_attributes_query = mysql_query("select count(*) as total from products_options popt, products_attributes patrib where patrib.products_id='" . $row->id . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . (int)1 . "'");

$products_attributes = mysql_fetch_object($products_attributes_query);

if ($products_attributes->total > 0) {

$products_options_name_query = mysql_query("select distinct popt.products_options_id, popt.products_options_name from products_options popt, products_attributes patrib where patrib.products_id='" . (int)$row->id . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . (int)1 . "' order by popt.products_options_name") or die(mysql_error());

 

$trackTabs = '';

 

while ($products_options_name = mysql_fetch_object($products_options_name_query)) {

$products_options_array = array();

$products_options_query = mysql_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from products_attributes pa, products_options_values pov where pa.products_id = '" . (int)$row->id . "' and pa.options_id = '" . $products_options_name->products_options_id . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)1 . "'");

while ($products_options = mysql_fetch_object($products_options_query)) {

$products_options_array[] = array('id' => $products_options->products_options_values_id, 'text' => $products_options->products_options_values_name);

}

 

for ($a = 0; $a < count($attributesColumns); ++$a)

{

if ($products_options_name->products_options_name == $attributesColumns[$a])

{

if ($a == 0)

$trackTabs = "\t";

else

{

if (empty($trackTabs))

$trackTabs = str_repeat("\t", $a);

$trackTabs .= "\t";

}

 

$output .= $trackTabs;

foreach ($products_options_array as $arr)

$output .= $arr['text'] . ',';

$output = substr($output, 0, -1);

}

}

}

}

}

/******************* END HANDLING THE ATTRIBUTES ********************/

Edited by booksfromhome

Share this post


Link to post
Share on other sites

I am using V2.9 oscommerce 2.3

 

I have three attributes defined: brand, condition, isbn

 

When I create the google feed, the condition is moved into the ISBN field if the first field is full.

If the first two fields are blank, the ISBN number is correct

 

product #1 c.brand = books, c.condition = blank, c.isbn = new

product #2 c.brand = blank, c.condition = blank, c,isbn = number

product #3 c.brand = books, c.condition = blank, c.isbn = blank

I tried it here and it works as expected so I can't think of any reason why it does that or even what to check.

Share this post


Link to post
Share on other sites

Google will upload the datafeed but not create it. You'll need to remember to do that if don't use cron.

 

Jack I worked with the php path yesterday, involving the host tech support. Can't figure out the glitch, so I am going to go with just having google process these. I have an upload set up with google on one shop to do that and it is processing correctly.

 

for the file source I enter

"http://myshop.com/myadminfolder/googlefeeder.php"

then on googlebase there's a place to enter the username and password

 

It runs...the resulting 3 email confirmations from google are

 

 

Google Merchant Center data feed file status on "datafeed.txt": No items inserted

then two with

Google Merchant Center data feed file status on "datafeed.txt": 146 of 146 items inserted

 

Double checking the feed status at google base, the feed shows processed correctly with all the items inserted as of the date/time I scheduled. Which was today at 0200 hours.

 

Just FYI on that.

 

Anyway, so neither the tech folks nor I don't know exactly what the issue is, but the cron problem seemed to show up about when i went to a VPS account, but that may be coincidental.

 

Because I have at least a solution I will let this go now and focus on other shop maintenance issues, including getting around to the latest Headertags update.

 

Thanks


I am not a professional webmaster or PHP coder by background or training but I will try to help as best I can.

I remember what it was like when I first started with osC. It can be overwhelming.

However, I strongly recommend considering hiring a professional for extensive site modifications, site cleaning, etc.

There are several good pros here on osCommerce. Look around, you'll figure out who they are.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×