dreammediauk

Hi, I've installed the following contribution: http://www.oscommerce.com/community/contributions,2220/. Everything's been backed up, so I'm not panicking (yet!). In the Tools section of the Admin Panel, under Kelkoo, I'm receiving the following SQL error: 1064 - You have an error in your SQL syntax near '(select p.products_id, p.products_compare_status, p.products_quant' at line 1 (select p.products_id, p.products_compare_status, p.products_quantity, p.products_price, p.products_tax_class_id, pd.products_name, cd.categories_name from products p, products_description pd, products_to_categories p2c, categories c, categories_description cd WHERE p.products_id = pd.products_id AND cd.categories_id = c.parent_id AND p.products_id = p2c.products_id AND c.categories_id = p2c.categories_id AND p.products_status = 1 AND p.products_price <> 0 AND pd.language_id = '1' AND cd.language_id = '1' AND pd.products_name like '%%' group by p.products_id, cd.categories_id) Has anyone else used this contribution and had the same error? I'm not even sure where to start looking!! Any suggestions would be gratefully received - thanks. John.

OK, I tried amending the code. The file runs and creates the txt file fine, other than the price, which is now displaying as 0, instead of adding the VAT. Would you mind having a look for me? I've deliberately deleted the usernames etc. at the top of the file. Many thanks in advance! $convertCur = false; //default = false $curType = "GBP"; // Converts Currency to any defined currency (eg. USD, EUR, GBP) if($convertCur) { $productURL = 'http://www.droolonline.co.uk/product_info.php?currency=GBP&products_id='; //where CURTYPE is your currency type (eg. USD, EUR, GBP) } //START Advance Optional Values //(0=False 1=True) (optional_sec must be enabled to use any options) $optional_sec = 1; $instock = 0; $shipping = 0; $lowestShipping = "4.95"; //this is not binary. $brand = 0; $upc = 0; //Not supported by default osC $manufacturer_id = 0; //Not supported by default osC $product_type = 0; $currency = 0; $default_currency = "GBP"; //this is not binary. $language = 0; $default_language = "en"; //this is not binary. $ship_to = 0; $default_ship_to = "ALL"; //this is not binary, not supported by default osC for individual products. $ship_from = 0; $default_ship_from = "USD"; //this is not binary, not supported by default osC for individual products. //END of Advance Optional Values 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 AS prodModel, products_weight, manufacturers.manufacturers_name AS mfgName, manufacturers.manufacturers_id, products.products_id AS id, products_description.products_name AS name, products_description.products_description AS description, products.products_status AS prodStatus, FORMAT(products.products_price,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 ) 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 ORDER BY products.products_id ASC "; $catInfo = " SELECT categories.categories_id AS curCatID, categories.parent_id AS parentCatID, categories_description.categories_name AS catName FROM categories, categories_description WHERE categories.categories_id = categories_description.categories_id "; function findCat($curID, $catTempPar, $catTempDes, $catIndex) { if($catTempPar[$curID] != 0 ) { if($catIndex[$catTempPar[$curID]] != null) { $temp=$catIndex[$catTempPar[$curID]]; } else { $catIndex = findCat($catTempPar[$curID], $catTempPar, $catTempDes, $catIndex); $temp = $catIndex[$catTempPar[$curID]]; } } if($catTempPar[$curID] == 0) { $catIndex[$curID] = $catTempDes[$curID]; } else { $catIndex[$curID] = $temp . " > " . $catTempDes[$curID]; } return $catIndex; } $catIndex = array(); $catTempDes = array(); $catTempPar = array(); $processCat = mysql_query( $catInfo )or die( $FunctionName . ": SQL error " . mysql_error() . "| catInfo = " . htmlentities($catInfo) ); while ( $catRow = mysql_fetch_object( $processCat ) ) { $catKey = $catRow->curCatID; $catName = $catRow->catName; $catParID = $catRow->parentCatID; if($catName != "") { $catTempDes[$catKey]=$catName; $catTempPar[$catKey]=$catParID; } } foreach($catTempDes as $curID=>$des) //don't need the $des { $catIndex = findCat($curID, $catTempPar, $catTempDes, $catIndex); } //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 \t offer_id"; //create optional section if($optional_sec == 1) { if($instock == 1) $output .= "\t instock "; if($shipping == 1) $output .= "\t shipping "; if($brand == 1) $output .= "\t brand "; if($upc == 1) $output .= "\t upc "; if($manufacturer_id == 1) $output .= "\t manufacturer_id "; if($product_type == 1) $output .= "\t product_type "; if($currency == 1) $output .= "\t currency "; if($language == 1) $output .= "\t language "; if($ship_to == 1) $output .= "\t ship_to "; if($ship_from == 1) $output .= "\t ship_from "; } $output .= "\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 ); //Currency Information if($convertCur) { $sql3 = " SELECT currencies.value AS curUSD FROM currencies WHERE currencies.code = '$curType' "; $result3=mysql_query( $sql3 )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql3 = " . htmlentities($sql3) ); $row3 = mysql_fetch_object( $result3 ); } while( $row = mysql_fetch_object( $result ) ) { if ($already_sent[$row->id] == 1) continue; // if we've sent this one, skip the rest of the while loop if( $row->prodStatus == 1 || ($optional_sec == 1 && $instock == 1) ) { // 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 } } if($convertCur) { $row->price = $row->price * $row3->curUSD; $row->price = number_format($row->price, 2, '.', ''); } $vat = 1.175; //where 17.5% is your tax rate $row->price = $row->price * vat; $output .= $row->product_url . "\t" . preg_replace($_strip_search, $_strip_replace, strip_tags( str_replace(">", "> ", $row->name) ) ) . "\t" . preg_replace($_strip_search, $_strip_replace, strip_tags( str_replace(">", "> ", $row->description) ) ) . "\t" . $row->price . "\t" . $row->image_url . "\t" . $catIndex[$row->prodCatID] . "\t" . $row->id; //optional values section if($optional_sec == 1) { if($instock == 1) { if($row->prodStatus == 1) { $prodStatusOut = "Y"; } else { $prodStatusOut = "N"; } $output .= " \t " . $prodStatusOut; } if($shipping == 1) $output .= " \t " . $lowestShipping; if($brand == 1) $output .= " \t " . $row->mfgName; if($upc == 1) $output .= " \t " . "Not Supported"; if($manufacturer_id == 1) $output .= " \t " . "Not Supported"; if($product_type == 1) { $catNameTemp = strtolower($catName); if($catNameTemp == "books") $productTypeOut = "book"; else if($catNameTemp == "music") $productTypeOut = "music"; else if($catNameTemp == "videos") $productTypeOut = "video"; else $productTypeOut = "other"; $output .= " \t " . $productTypeOut; } if($currency == 1) $output .= " \t " . $default_currency; if($language == 1) $output .= " \t " . $default_language; if($ship_to == 1) $output .= " \t " . $default_ship_to; if($ship_from == 1) $output .= " \t " . $default_ship_from; } $output .= " \n"; } $already_sent[$row->id] = 1; } if ( file_exists( $OutFile ) ) unlink( $OutFile ); $fp = fopen( $OutFile , "w" ); $fout = fwrite( $fp , $output ); fclose( $fp ); /* //Start FTP to Froogle function ftp_file( $ftpservername, $ftpusername, $ftppassword, $ftpsourcefile, $ftpdirectory, $ftpdestinationfile ) { // set up basic connection $conn_id = ftp_connect($ftpservername); if ( $conn_id == false ) { echo "FTP open connection failed to $ftpservername <BR>\n"; return false; } // login with username and password $login_result = ftp_login($conn_id, $ftpusername, $ftppassword); // check connection if ((!$conn_id) || (!$login_result)) { echo "FTP connection has failed!<BR>\n"; echo "Attempted to connect to " . $ftpservername . " for user " . $ftpusername . "<BR>\n"; return false; } else { echo "Connected to " . $ftpservername . ", for user " . $ftpusername . "<BR>\n"; } if ( strlen( $ftpdirectory ) > 0 ) { if (ftp_chdir($conn_id, $ftpdirectory )) { echo "Current directory is now: " . ftp_pwd($conn_id) . "<BR>\n"; } else { echo "Couldn't change directory on $ftpservername<BR>\n"; return false; } } ftp_pasv ( $conn_id, true ); // upload the file $upload = ftp_put( $conn_id, $ftpdestinationfile, $ftpsourcefile, FTP_ASCII ); // check upload status if (!$upload) { echo "$ftpservername: FTP upload has failed!<BR>\n"; return false; } else { echo "Uploaded " . $ftpsourcefile . " to " . $ftpservername . " as " . $ftpdestinationfile . "<BR>\n"; } // close the FTP stream ftp_close($conn_id); return true; } ftp_file( $ftp_server, $ftp_user_name, $ftp_user_pass, $source_file, $ftp_directory, $destination_file); //End FTP to Froogle */ // End TIMER // --------- $etimer = explode( ' ', microtime() ); $etimer = $etimer[1] + $etimer[0]; echo '<p style="margin:auto; text-align:center">'; printf( "Script timer: <b>%f</b> seconds.", ($etimer-$stimer) ); echo '</p>'; // --------- ?>

The simpliest way is to: Find: ?$output .= $row->product_url . "\t" . ?preg_replace($_strip_search, $_strip_replace, strip_tags( str_replace(">", "> ", $row->name) ) ) . "\t" . ?preg_replace($_strip_search, $_strip_replace, strip_tags( str_replace(">", "> ", $row->description) ) ) . "\t" . ?$row->price . "\t" . ?$row->image_url . "\t" . ?$catIndex[$row->prodCatID] . "\t" . ?$row->id; replace with: $vat = 1.175; //where 17.5% is your tax rate $row->price = $row->price * vat; $output .= $row->product_url . "\t" . preg_replace($_strip_search, $_strip_replace, strip_tags( str_replace(">", "> ", $row->name) ) ) . "\t" . preg_replace($_strip_search, $_strip_replace, strip_tags( str_replace(">", "> ", $row->description) ) ) . "\t" . $row->price . "\t" . $row->image_url . "\t" . $catIndex[$row->prodCatID] . "\t" . $row->id; Thanks, I'll give it a go!

Hi, sorry to jump in. I've managed to set up the feed and Froogle have sent an email to say that although the feed has uploaded, it needs changes making. The prices uploaded in the feed differ from the prices displaying on our site. This is not acceptable to Froogle (obviously!) and is caused because the feed prices do not include VAT at 17.5%. Our site displays prices including VAT - we don't want to display prices without it. How can I get the froogle.php file to write prices including VAT to the txt file? I know this subject was mentioned briefly towards the start of this thread (believe me, I've trawled all 53 pages trying to find an answer!), but my PHP experience is somewhat lacking :rolleyes: and I could really do with the help of someone who knows what they're talking about or may have solved this themselves to talk me through it... I'm suprised that no-one else has picked this up, or are we the only store only displaying prices inclusive of tax? Please help!!!

Hi, I've installed the Credit Class module, and all seems to be working OK in the Admin section. However, if I try to send a voucher to a customer for a value of say ?10, the email the customer receives states the value of the voucher as ?0.00. If I then check the admin section for Vouchers Sent, the records confirm the voucher amount as 0.00. I have tried this a number of times to make sure I'm not making mistakes when sending the voucher, and can't work out what is going wrong. Can anyone help? PS - sorry for the double post - I should have added this message to this thread in the first place!

Hi, I've installed the Credit Class module, and all seems to be working OK in the Admin section. However, if I try to send a voucher to a customer for a value of say ?10, the email the customer receives states the value of the voucher as ?0.00. If I then check the admin section for Vouchers Sent, the records confirm the voucher amount as 0.00. I have tried this a number of times to make sure I'm not making mistakes when sending the voucher, and can't work out what is going wrong. Can anyone help?