3289 replies to this topic

[EricK]

Tim, I also have the duplicate products problem.  Did you modify the script, or delete them manually?

Thanks,
EricK

[wvmlt]

I sent my feed to froogle and got back this email

Quote

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);
?>

[dyland]

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

[Bitterone]

I think a fix for the froogle rejection would be... changing

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

to

$output = "product_url\tname\tdescription\tprice\timage_url\tcategory\n";

[FlyingMonkey]

Is this in a form of a contribution anywhere?  Thanks.

[jcall]

sort of.  There is a contrib that has the same goal, but it has the same weakness that this one does - - it can't strip out the duplicate product entries when they are in multiple categories.

Now that I think about it, it seems like a buddy of mine wrote up a shell script for me to do just that - - remove lines from a file based on one of the pieces of a file.

I bet that we can figure this out . . .

Probably something like this would remove the duplicates:
1) "sort" based on column offer_id and/or column product_id
2) if (current_line.offer_id == previous_line.offer_id) OR (current_line.product_id == previous_line.product_id) then "cut" current_line.

I'm not a good shell script author, but I'm sure that one of us can figure this out and add it to the existing code.

-jared

[EricK]

Steps to remove duplicate products using MS Excel.

1. Open Excel, click Data, Get External Data, Import Text File then import your "froogle.txt" file

2. click Data, Filter, Advanced Filter, OK

Select one column to filter, in my case the "name" column, from B5 to B555.

Action = Filter the list, in-place
List Range: Sheet1!$B$5:$B$555
Criteria range: Sheet1!$B$5:$B$555
Unique records only = yes

Confirm that your duplicate products have been removed.

3. Highlight all fields, Copy, open Sheet2, Paste into Sheet 2
4. File, Save As, Text (Tab delimited), OK, No

Now FTP your new froogle.txt file per your instructions to Google.

EricK  

[jcall]

With some tweaking and help from Tim, here is the final code I came up with that worked (and was accepted) for me:

NOTE:  The FTP upload portion is commented out, as you should probably test that your file is accepted by froogle before beginning to automatically send it . . .

Once it's accepted, then uncomment the FTP lines at the bottom, and use the crontab entry.

Thanks everyone, and ENJOY!!

-jared

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


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

$ftp_server = "hedwig.google.com" ;
$ftp_user_name = "CHANGEME-froogle-username" ;
$ftp_user_pass = "CHANGEME-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_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
" ;


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

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

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

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

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

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

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

*/

?>

[jcall]

anyone plan on releasing this as a contrib?  It's better than the Froogle contrib that's out there now, IMO.

The other one doesn't deal with products in duplicate categories (yet?), requires manual upload, etc.

-jared

[FlyingMonkey]

after a few hours of learning basic sql and fiddling around with it i got it to upload the specials (if available) and it no longer duplicates the first entry.  i'll try and release it as a contribution tomorrow once i get some sleep.  

btw... how do i setup the cron job.   ssh in and type?
0 4 * * 3 /usr/bin/php -q /home/<unix route to your file>/admin/froogle.php

and how do i check if it's working?  thanks.

[FlyingMonkey]

opps, duplicate.  look at the previous post.  

Edited by FlyingMonkey, 02 April 2004 - 11:58 AM.

[jcall]

If your hosting provider uses cpanel (fairly popular, it seems), there will be "Cron" button for you to set this up.  You can either use the basic mode or the advanced mode, try them both - - they're not that hard.

I'd comment out the part about the froogle upload until you're sure it works.

How do you know it works, you ask?  Well, when you specify a cron job, cpanel will ask you what email address you want the cron results sent to.  If all goes well, and you have the FTP section uncommented out, then you'll get an email with the text messages that are in the froogle.php, like
  Logging in to FTP Server
  Attempted to connect to hedwig.google.com for user joe_bagodonuts
  Connected to hedwig.google.com, User joe_bagodonuts
  Uploaded mystuff.txt to hedwig.google.com as mywebsite.txt

Enjoy!

-jared

[FlyingMonkey]

thanks jared!  unfortunately my hosting provider doesn't use cpanel.  I entered one through ssh.  but i'm not sure if it's correct.

This is what I entered:

0 4 * * 3 /usr/bin/curl -s -u user:pass http://www.mywebserver.com/catalog/admin/froogle.php


where user:pass is the user:password into the admin folder?

[Marg]

Anyone know why all the products wouldn't show up in the froogle feed?
I am missing items and can't seem to see why.
They aren't duplicates nor anything common with the items that are being left out.
Only 800 of 1200 items are showing up.
Thanks in advance,
Marg

[Marg]

OK... I see why... It won't parse duplicate product names. The field that should be used, one would think, is the model number field since the model numbers are always different.

Here's froogle.php with the model number as the identifier

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

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

$ftp_server = "hedwig.google.com";
$ftp_user_name = "CHANGEME-froogle-username";
$ftp_user_pass = "CHANGEME-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_model , products_weight ,
manufacturers.manufacturers_name ,
products.products_id AS id,
products_description.products_name AS name,
products.products_model AS model,
products_description.products_description AS description,
FORMAT(products.products_price,2) AS price,
CONCAT( '" . $imageURL . "' ,products.products_image) AS image_url,
concat_ws( ' > ' , catdescparent.categories_name , catdesccurrent.categories_name ) AS category
FROM categories ,
products,
products_description,
categories_description as catdesccurrent,
products_to_categories


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

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


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

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

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

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

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

//while( $row2 = mysql_fetch_object( $result2 ) )
//{
//	echo "   Row2 ID: " . $row2->idS . "<BR>";
//}

$i = 0;
while( $row = mysql_fetch_object( $result ) )
{
	if( $i == 1 )  //first time init
	{
  $already_sent[$row->model] = 1;
	}
	$i++;

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

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

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

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

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

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

/*  //Start FTP to Froogle

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

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

*/  //End FTP to Froogle


// Now the cron entry
// 0 4 * * 3 /usr/bin/php -q /home/<path>/admin/froogle.php

?>

I think this is right...
Could someone double check or tell me why this may not work?
Thanks,
Marg

[Marg]

And I still don't get all the products..
But I do get more...!
I need help....
Marg

[FlyingMonkey]

It parses by Product Name, incase two products are listed twice but at different prices.  Like if a certain quantity of product X is on special.

It only adds, products that have an "available" status (green).  Is that it?

[Marg]

Nope....
The items are available.
It's a mystery...
Marg

[FlyingMonkey]

It sorts the items by product id in ascending order... and your saying that it's randomly leaving some out?  what's the url to your store?

[Marg]

My Webpage

Yep, that's what I am saying...
Marg