Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Automatically send data feed to Froogle


gottaloveit

Recommended Posts

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

Link to comment
Share on other sites

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 :P

Link to comment
Share on other sites

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

 

*/

 

?>

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 3 weeks later...

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. :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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Thanks... I must be going crazy.....

I was sure I was looking through product numbers where the product was showing up and not in the froogle list, now I can't find a single one. The skipped numbers have no product....

Ignore me....... please....

Marg

Sorry for the trouble..

thank for helping, FM

Link to comment
Share on other sites

Quick Note: For All Users. By default the file generated is froogle.txt, this needs to be changed to generally your username(from froogle).txt

 

For instance, if your username was applestore then your txt file would be applestore.txt This change applies to both $OutFile and $destination_file

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

Link to comment
Share on other sites

Can I use this on a Windows box? I see the cron is used to FTP to the froogle server. Should I just setup a scheduled task to perform this function? Or do you already have that built in?

The cron entry, is only used to automate the monthly upload and is not required. you can set up a scheduled task if you prefer on a windows server.

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

Link to comment
Share on other sites

I was wondering about the cron entry,

my webhosting allows me to schedual a cron entry but I don't understand what do I need to change in the froogle.php file?

I don't get a number for the cron job or anything like that...

what should come instead of the line:

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

what do they mean by unix route?

what is the 0 4 * * 3?

 

Thanks in advance...

Ami

Link to comment
Share on other sites

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

what do they mean by unix route?

what is the 0 4 * * 3?

I am totally clueless about cron jobs. After spending hours of trial and error, I had to email my webhost provider for the answer to this. Here is what I put into my cron job, and it works perfectly! All that I needed to do was drop off the numbers at the beginning. Of course your <unix route> will be different, so just put yours in.

 

/usr/local/bin/php -q /home/<unix route>/admin/froogle.php

 

Good luck!

James

Edited by stoopies
Link to comment
Share on other sites

James,

Thank you very much for your help.

I contacted my webhost just as you did and they first need to approve the cron job.

my other question though what do the number 0 4 * * 3 mean specifically?

does it mean feed the file every 4 days or every 3 weeks?

also, do I need to specify the cron job number? if so are those the numbers?

thank you again,

Amitai

Link to comment
Share on other sites

James,

Thank you very much for your help.

I contacted my webhost just as you did and they first need to approve the cron job.

my other question though what do the number 0 4 * * 3 mean specifically?

does it mean feed the file every 4 days or every 3 weeks?

also, do I need to specify the cron job number? if so are those the numbers?

thank you again,

Amitai

hmm.... i didn't write the original cron entry. but i believe it actually uploads weekly on wednesday at 4am.

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

Link to comment
Share on other sites

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