Jump to content



Photo
* * * * * 5 votes

Automatically send data feed to Froogle


  • Please log in to reply
3373 replies to this topic

#21   EricK

EricK
  • Members
  • 315 posts
  • Real Name:Eric_K
  • Gender:Male
  • Location:Atlanta, GA USA

Posted 09 February 2004 - 23:36

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

Thanks,
EricK

#22   wvmlt

wvmlt
  • Members
  • 322 posts
  • Real Name:Keith
  • Location:USA

Posted 11 February 2004 - 04:46

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'?

#23   dyland

dyland
  • Members
  • 109 posts
  • Real Name:Dylan Downhill
  • Location:Phoenix Arizona

Posted 11 February 2004 - 15:29

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

#24   Bitterone

Bitterone
  • Members
  • 2 posts
  • Real Name:Aaron G

Posted 24 February 2004 - 19:15

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


#25   FlyingMonkey

FlyingMonkey
  • Members
  • 882 posts
  • Real Name:Calvin K

Posted 13 March 2004 - 12:48

Is this in a form of a contribution anywhere? Thanks.
Most likely your question has been answered, please do a search first.

#26   jcall

jcall
  • Members
  • 3,093 posts
  • Real Name:Jared Call
  • Gender:Male
  • Location:Saratoga Springs, UT

Posted 13 March 2004 - 15:37

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

#27   EricK

EricK
  • Members
  • 315 posts
  • Real Name:Eric_K
  • Gender:Male
  • Location:Atlanta, GA USA

Posted 13 March 2004 - 16:28

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 /tongue.gif' class='bbc_emoticon' alt=':P' />

#28   jcall

jcall
  • Members
  • 3,093 posts
  • Real Name:Jared Call
  • Gender:Male
  • Location:Saratoga Springs, UT

Posted 18 March 2004 - 06:21

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

*/

?>

#29   jcall

jcall
  • Members
  • 3,093 posts
  • Real Name:Jared Call
  • Gender:Male
  • Location:Saratoga Springs, UT

Posted 18 March 2004 - 07:09

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

#30   FlyingMonkey

FlyingMonkey
  • Members
  • 882 posts
  • Real Name:Calvin K

Posted 02 April 2004 - 11:53

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. /biggrin.gif' class='bbc_emoticon' alt=':D' />

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.
Most likely your question has been answered, please do a search first.

#31   FlyingMonkey

FlyingMonkey
  • Members
  • 882 posts
  • Real Name:Calvin K

Posted 02 April 2004 - 11:54

opps, duplicate. look at the previous post. /wink.gif' class='bbc_emoticon' alt=';)' />

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

Most likely your question has been answered, please do a search first.

#32   jcall

jcall
  • Members
  • 3,093 posts
  • Real Name:Jared Call
  • Gender:Male
  • Location:Saratoga Springs, UT

Posted 03 April 2004 - 08:15

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

#33   FlyingMonkey

FlyingMonkey
  • Members
  • 882 posts
  • Real Name:Calvin K

Posted 04 April 2004 - 02:13

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.mywebserv...min/froogle.php


where user:pass is the user:password into the admin folder?
Most likely your question has been answered, please do a search first.

#34   Marg

Marg
  • Members
  • 160 posts
  • Real Name:Marg

Posted 05 April 2004 - 09:58

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

#35   Marg

Marg
  • Members
  • 160 posts
  • Real Name:Marg

Posted 05 April 2004 - 10:22

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

#36   Marg

Marg
  • Members
  • 160 posts
  • Real Name:Marg

Posted 05 April 2004 - 10:32

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

#37   FlyingMonkey

FlyingMonkey
  • Members
  • 882 posts
  • Real Name:Calvin K

Posted 05 April 2004 - 15:38

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?
Most likely your question has been answered, please do a search first.

#38   Marg

Marg
  • Members
  • 160 posts
  • Real Name:Marg

Posted 05 April 2004 - 17:31

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

#39   FlyingMonkey

FlyingMonkey
  • Members
  • 882 posts
  • Real Name:Calvin K

Posted 05 April 2004 - 18:25

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?
Most likely your question has been answered, please do a search first.

#40   Marg

Marg
  • Members
  • 160 posts
  • Real Name:Marg

Posted 05 April 2004 - 19:21

My Webpage

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