Guest Posted March 13, 2004 Share Posted March 13, 2004 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 Quote Link to comment Share on other sites More sharing options...
EricK Posted March 13, 2004 Share Posted March 13, 2004 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 Quote Link to comment Share on other sites More sharing options...
Guest Posted March 18, 2004 Share Posted March 18, 2004 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 */ ?> Quote Link to comment Share on other sites More sharing options...
Guest Posted March 18, 2004 Share Posted March 18, 2004 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 Quote Link to comment Share on other sites More sharing options...
FlyingMonkey Posted April 2, 2004 Share Posted April 2, 2004 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. Quote Most likely your question has been answered, please do a search first. Link to comment Share on other sites More sharing options...
FlyingMonkey Posted April 2, 2004 Share Posted April 2, 2004 (edited) opps, duplicate. look at the previous post. ;) Edited April 2, 2004 by FlyingMonkey Quote Most likely your question has been answered, please do a search first. Link to comment Share on other sites More sharing options...
Guest Posted April 3, 2004 Share Posted April 3, 2004 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 Quote Link to comment Share on other sites More sharing options...
FlyingMonkey Posted April 4, 2004 Share Posted April 4, 2004 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? Quote Most likely your question has been answered, please do a search first. Link to comment Share on other sites More sharing options...
Marg Posted April 5, 2004 Share Posted April 5, 2004 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 Quote Link to comment Share on other sites More sharing options...
Marg Posted April 5, 2004 Share Posted April 5, 2004 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 Quote Link to comment Share on other sites More sharing options...
Marg Posted April 5, 2004 Share Posted April 5, 2004 And I still don't get all the products.. But I do get more...! I need help.... Marg Quote Link to comment Share on other sites More sharing options...
FlyingMonkey Posted April 5, 2004 Share Posted April 5, 2004 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? Quote Most likely your question has been answered, please do a search first. Link to comment Share on other sites More sharing options...
Marg Posted April 5, 2004 Share Posted April 5, 2004 Nope.... The items are available. It's a mystery... Marg Quote Link to comment Share on other sites More sharing options...
FlyingMonkey Posted April 5, 2004 Share Posted April 5, 2004 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? Quote Most likely your question has been answered, please do a search first. Link to comment Share on other sites More sharing options...
Marg Posted April 5, 2004 Share Posted April 5, 2004 My Webpage Yep, that's what I am saying... Marg Quote Link to comment Share on other sites More sharing options...
FlyingMonkey Posted April 5, 2004 Share Posted April 5, 2004 That's a really nice site. where's your froogle data stored? and do you have several examples of missing products? Quote Most likely your question has been answered, please do a search first. Link to comment Share on other sites More sharing options...
Marg Posted April 5, 2004 Share Posted April 5, 2004 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 Quote Link to comment Share on other sites More sharing options...
FlyingMonkey Posted April 6, 2004 Share Posted April 6, 2004 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 Quote Most likely your question has been answered, please do a search first. Link to comment Share on other sites More sharing options...
lparks Posted April 6, 2004 Share Posted April 6, 2004 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? Quote Link to comment Share on other sites More sharing options...
FlyingMonkey Posted April 6, 2004 Share Posted April 6, 2004 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. Quote Most likely your question has been answered, please do a search first. Link to comment Share on other sites More sharing options...
asasson Posted April 6, 2004 Share Posted April 6, 2004 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 Quote Link to comment Share on other sites More sharing options...
Guest Posted April 6, 2004 Share Posted April 6, 2004 (edited) 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 April 6, 2004 by stoopies Quote Link to comment Share on other sites More sharing options...
FlyingMonkey Posted April 7, 2004 Share Posted April 7, 2004 The numbers are used to schedule when and how often the ftp upload should occur. Glad to hear you guys got it to work. Quote Most likely your question has been answered, please do a search first. Link to comment Share on other sites More sharing options...
asasson Posted April 7, 2004 Share Posted April 7, 2004 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 Quote Link to comment Share on other sites More sharing options...
FlyingMonkey Posted April 7, 2004 Share Posted April 7, 2004 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. Quote Most likely your question has been answered, please do a search first. Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.