Page 1 of 1

PHP/SQL problem

Posted: Tue Aug 16, 2011 7:06 am
by madmega
[quote author=madmega link=topic=341628.msg1611456#msg1611456 date=1313494827]
Hi, i am editing a google_feeder script, normally, this script takes a column from a SQL database table but what i want is to take another column from another database table in the same SQL database.This is the script i am currently running

Code: Select all

<?php

define('SEO_URLS_GEBRUIKEN','true'); 
define('BESTANDSNAAM', 'googleshopping_feed.txt');       
define('WEBSITE_LINK', ''.$_SERVER['SERVER_NAME'].'/'); 
define('FTP_USERNAME', 'naam'); 
define('FTP_PASSWORD', 'password'); 
define('FTP_ENABLED', (isset($_GET['noftp']) ? '0' : '0'));      
define('CONVERT_CURRENCY', '0'); 
define('VALUTA', 'EUR');  
define('TAAL_ID_LANGUAGE', 5);   
define('QUOTES_CATEGORY_NAME','');

define('OPTIONS_TONEN', 1);
define('OPTIONS_TONEN_AGE_RANGE', 0);
define('OPTIONS_TONEN_ATTRIBUTES', 1);
define('OPTIONS_TONEN_BRAND', 1);
define('OPTIONS_TONEN_CONDITION', 1);
define('OPTIONS_TONEN_CURRENCY', 0);
define('OPTIONS_TONEN_EXPIRATION', 1);
define('OPTIONS_TONEN_FEED_LANGUAGE', 0);
define('OPTIONS_TONEN_FEED_QUANTITY', 1);
define('OPTIONS_TONEN_GTIN', 0);
define('OPTIONS_TONEN_GOOGLE_UTM', 0);
define('OPTIONS_TONEN_ISBN', 0);
define('OPTIONS_TONEN_MADE_IN', 0);
define('OPTIONS_TONEN_MANUFACTURERS_ID', 0);         
define('OPTIONS_TONEN_PAYMENT_ACCEPTED', 0);
define('OPTIONS_TONEN_PRODUCT_MODEL', 1);
define('OPTIONS_TONEN_PRODUCT_TYPE', 1);
define('OPTIONS_TONEN_SHIPPING', 1);
define('OPTIONS_TONEN_INCLUDE_TAX', 0);
define('OPTIONS_TONEN_UPC', 0);
define('OPTIONS_TONEN_WEIGHT', 1);

define('OPTIONS_AGE_RANGE', '10-50 years');
define('OPTIONS_BRAND', 'Gadgets');
define('OPTIONS_CONDITION', 'Nieuw');  //possible entries are New, Refurbished, Used
define('OPTIONS_DEFAULT_CURRENCY', 'EUR');
define('OPTIONS_DEFAULT_FEED_LANGUAGE', 'NL');
define('OPTIONS_DEFAULT_GOOGLE_UTM', '?utm_source=GoogleBase1&utm_medium=BaseFeed1&utm_campaign=products'); //see http://www.google.com/support/googleanalytics/bin/answer.py?hl=en&answer=55578
define('OPTIONS_GTIN', '');
define('OPTIONS_ISBN', '');
define('OPTIONS_MADE_IN', 'US');
define('OPTIONS_MANUFACTURERS_NAME', '1'); 
define('OPTIONS_PAYMENT_ACCEPTED_METHODS', '');  
define('OPTIONS_PRODUCT_TYPE', 'full'); 
//define('OPTIONS_TAX_STRING', ''); 

//the following is for the shipping override option - enter multiple values separated by a comma
//Format entries follow. A colon must be present for each field, whether it is entered or not.
// COUNTRY - OPTIONAL - If country isn't included, we'll assume the shipping price applies to the target country of the item. If region isn't included, the shipping price will apply across the entire country.
// REGION  - OPTIONAL - blank for entire country, otherwise, us two-letter State (CA), full zip code (90210) or wildcard zip code (902*)
// SERVICE - OPTIONAL - The service class or delivery speed, i.e. ground
// PRICE   - REQUIRED - Fixed shipping price (assumes the same currency as the price attribute)
//define('OPTIONS_SHIPPING_STRING', 'nl::Ground:19.00'); //says charge tax to US for residents of Florida at 5% and don't apply tax to shipping

//the following is for the tax override option - enter multiple values separated by a comma
//Format entries follow. A colon must be present for each field, whether it is entered or not.
// COUNTRY  - OPTIONAL - country the tax applies to - only US for now
// REGION   - OPTIONAL - blank for entire country, otherwise, us two-letter State (CA), full zip code (90210) or wildcard zip code (902*)
// TAX      - REQUIRED - default = 0 (e.g. for 5.76% tax use 5.76)
// SHIPPING - OPTIONAL - do you charge tax on shipping - choices are y or n
//define('OPTIONS_TAX_STRING', 'NL::19.00:n'); //says charge tax to US for residents of Florida at 5% and don't apply tax to shipping

define('OPTIONS_UPC', '');
define('OPTIONS_WEIGHT_ACCEPTED_METHODS', 'g'); 

//the following allow skipping certain items
define('OPTIONS_IGNORE_PRODUCT_ZERO', 0); 

$taxRate = 19;  // (Prijs tonen inclusief XX BTW. Prijzen worden EX BTW uit de dabase gehaald.)



/***********************************************************************************************/

// Configuratie instellingen ophalen
require_once('../includes/configure.php');

if (! function_exists("tep_not_null")) {
   function tep_not_null($value) {
      if (is_array($value)) {
         return ((sizeof($value) > 0) ? true : false);
      } else {
         return  ((($value != '') && (strtolower($value) != 'null') && (strlen(trim($value)) > 0)) ? true : false);
      }
   }
}

if(SEO_URLS_GEBRUIKEN=='true'){
  //********************
  // Modification for SEO
  // Since the ultimate SEO was only installed on the public side, we will include our files from there.
  require_once('../includes/filenames.php');
  require_once('../includes/database_tables.php');

  include_once('../' .DIR_WS_CLASSES . 'seo.class.php');
  $seo_urls = new SEO_URL(TAAL_ID_LANGUAGE);

  function tep_href_link($page = '', $parameters = '', $connection = 'NONSSL', $add_session_id = true, $search_engine_safe = true) {
     global $seo_urls;
     return $seo_urls->href_link($page, $parameters, $connection, $add_session_id);
  }
}

//  -----------
$stimer = explode( ' ', microtime() );
$stimer = $stimer[1] + $stimer[0];
//  -----------


$OutFile = "../feeds/" . BESTANDSNAAM;
$destination_file = BESTANDSNAAM;
$source_file = $OutFile;
$imageURL = 'http://' . WEBSITE_LINK . 'images/products_large/';
if(SEO_URLS_GEBRUIKEN=='true'){
   $productURL = 'product_info.php'; // ***** Revised for SEO
   $productParam = "products_id=";   // ***** Added for SEO
}else{
   $productURL = 'http://' . WEBSITE_LINK . '/product_info.php?products_id=';
}

$already_sent = array();

if(CONVERT_CURRENCY)
{
   if(SEO_URLS_GEBRUIKEN=='true'){
       $productParam="currency=" . VALUTA . "&products_id=";
   }else{
       $productURL = "http://" . WEBSITE_LINK . "/product_info.php?currency=" . VALUTA . "&products_id=";  //where VALUTA is your currency type (eg. USD, EUR, GBP)
   }
}

$taxCalc = ($taxRate/100) + 1; 

$feed_exp_date = date('Y-m-d', time() + 2419200 );

if (!($link=mysql_connect(DB_SERVER,DB_SERVER_USERNAME,DB_SERVER_PASSWORD))) {
    echo "Error when connecting itself to the data base";
    exit();
}
if (!mysql_select_db( DB_DATABASE , $link )) {
   echo "Error the data base does not exist";
   exit();
}

$quotes = '';
if (QUOTES_CATEGORY_NAME !== '') {
   $quotes = " and products.customers_email_address = '' and products.quotes_email_address = ''";
}

$extraFields = '';
if (OPTIONS_TONEN_BRAND == 1  && strlen(OPTIONS_BRAND) == 0) { //brand is enabled but not set so load from database
   $extraFields .= ' products.products_brand as brand, ';
}
if (OPTIONS_TONEN_GTIN == 1  && strlen(OPTIONS_GTIN) == 0) {
   $extraFields .= ' products.products_gtin as gtin, ';
}
if (OPTIONS_TONEN_ISBN == 1  && strlen(OPTIONS_ISBN) == 0) {
   $extraFields .= ' products.products_isbn as isbn, ';
}
if (OPTIONS_TONEN_UPC == 1  && strlen(OPTIONS_UPC) == 0) {
   $extraFields .= ' products.products_upc as upc, ';
}


$sql = "
SELECT concat( '" . $productURL . "' ,products.products_id) AS product_url,
products_model AS prodModel,
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_quantity AS quantity,
products.products_status AS prodStatus,
products.products_weight AS prodWeight, " . $extraFields . "

FORMAT( IFNULL(specials.specials_new_products_price, products_groups.customers_group_price) * " . $taxCalc . ",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 )
left join specials on ( specials.products_id = products.products_id AND ( ( (specials.expires_date > CURRENT_DATE) OR (specials.expires_date is NULL) OR (specials.expires_date = 0) ) AND ( specials.status = 1 ) ) )

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 " . $quotes . "
AND categories_description.language_id = " . TAAL_ID_LANGUAGE . "
AND products_description.language_id = " . TAAL_ID_LANGUAGE . "
ORDER BY
products.products_id ASC
";

$quotes = '';
if (QUOTES_CATEGORY_NAME !== '') {
    $quotes = " and categories_description.categories_name NOT LIKE '" . QUOTES_CATEGORY_NAME . "' ";
}

$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 " . $quotes . "
AND categories_description.language_id = " . TAAL_ID_LANGUAGE . "";

function findCat($curID, $catTempPar, $catTempDes, $catIndex) {
   if( (isset($catTempPar[$curID])) && ($catTempPar[$curID] != 0) ) {
       if(isset($catIndex[$catTempPar[$curID]])) {
           $temp=$catIndex[$catTempPar[$curID]];
       } else {
           $catIndex = findCat($catTempPar[$curID], $catTempPar, $catTempDes, $catIndex);
           $temp = $catIndex[$catTempPar[$curID]];
       }
   }
   if( (isset($catTempPar[$curID])) && (isset($catTempDes[$curID])) && ($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);
}

$_strip_search = array(
"![~ ]+$|^[~ ]+!m", // remove leading/trailing space chars
'%[\r\n]+%m'); // remove CRs and newlines
$_strip_replace = array(
'',
' ');
$_cleaner_array = array(">" => "> ", "&reg;" => "", "®" => "", "&trade;" => "", "™" => "", "~" => "", "	" => "", """ => "\"");


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

$output = "link~titel~beschrijving~prijs~afbeeldingslink~id";
$attributesColumns = array();

//create optional section
if(OPTIONS_TONEN == 1) {
   if(OPTIONS_TONEN_AGE_RANGE == 1) 		      $output .= "~age_range";
   if(OPTIONS_TONEN_BRAND == 1)            	$output .= "~merk";
   if(OPTIONS_TONEN_CONDITION == 1)       		$output .= "~staat";
   if(OPTIONS_TONEN_CURRENCY == 1)        		$output .= "~currency";
   if(OPTIONS_TONEN_EXPIRATION == 1)      		$output .= "~vervaldatum";
   if(OPTIONS_TONEN_FEED_LANGUAGE == 1)   		$output .= "~language";
   if(OPTIONS_TONEN_FEED_QUANTITY == 1)   		$output .= "~hoeveelheid";
   if(OPTIONS_TONEN_GTIN == 1)            		$output .= "~gtin";
   if(OPTIONS_TONEN_ISBN == 1)            		$output .= "~isbn";
   if(OPTIONS_TONEN_MADE_IN == 1)         		$output .= "~made_in";
   if(OPTIONS_TONEN_MANUFACTURERS_ID == 1)    		$output .= "~manufacturer";
   if(OPTIONS_TONEN_PAYMENT_ACCEPTED == 1) 	$output .= "~payment_accepted";
   if(OPTIONS_TONEN_PRODUCT_MODEL == 1)   		$output .= "~mpn";
   if(OPTIONS_TONEN_PRODUCT_TYPE == 1)    		$output .= "~producttype";
   if(OPTIONS_TONEN_SHIPPING == 1)         	$output .= "~Google productcategorie ";
   if(OPTIONS_TONEN_INCLUDE_TAX == 1)         $output .= "~tax";
   if(OPTIONS_TONEN_UPC == 1)             		$output .= "~upc";
   if(OPTIONS_TONEN_WEIGHT == 1)          		$output .= "~verzendgewicht";



   if (OPTIONS_TONEN_ATTRIBUTES == 1)  {
       $products_options_name_query = mysql_query("select distinct popt.products_options_id, popt.products_options_name from products_options popt, products_attributes patrib where popt.language_id = '" . (int)1 . "' order by popt.products_options_name") or die(mysql_error());
       while ($products_options_name = mysql_fetch_object($products_options_name_query)) {
           $attributesColumns[] = $products_options_name->products_options_name;
           $name = strtolower($products_options_name->products_options_name);
           $name = str_replace(" ","_", $name);
           $output .= "~c:" . $name;
       }

       /*
       //If you want to only show particular attributes, comment out the above and uncomment this section.
       //Then enter two lines for each one you want to show. For example, if the atttributes you want to
       //show are named Color and Fabric, the entries would appear as follows:

       $attributesColumns[] = "Color";
       $attributesColumns[] = "Fabric";

       $output .= "~c:" . strtolower("Color");
       $output .= "~c:" . strtolower("Fabric");
       */
   }
}
$output .= "\n";


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

//Currency Information
if(CONVERT_CURRENCY) {
   $sql3 = "
   SELECT
   currencies.value AS curEUR
   FROM
   currencies
   WHERE currencies.code = '" . VALUTA . "'";

   $result3=mysql_query( $sql3 )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql3 = " . htmlentities($sql3) );
   $row3 = mysql_fetch_object( $result3 );
}

$loop_counter = 0;

while( $row = mysql_fetch_object( $result ) ) {
   if (OPTIONS_IGNORE_PRODUCT_ZERO > 0 && $row->quantity < 1) continue; //skip products with 0 qty
   if (isset($already_sent[$row->id])) continue; // if we've sent this one, skip the rest of the while loop

   if ( $row->prodStatus == 1 ) {
      if (CONVERT_CURRENCY) {
          $row->price = preg_replace("/[^.0-9]/", "", $row->price);
          $row->price = $row->price *  $row3->curEUR;
          $row->price = number_format($row->price, 2, '.', ',');
      }


      $google_utm = (OPTIONS_TONEN_GOOGLE_UTM ? OPTIONS_DEFAULT_GOOGLE_UTM : '');

      if(SEO_URLS_GEBRUIKEN=='true'){
          $output .= tep_href_link($productURL,$productParam . $row->id) . $google_utm . "~" .


          strip_tags($row->name) . "~" .


		preg_replace("/\s+[^a-zA-Z0-9]/", "", $row->description) . "~" .


          $row->price . "~" .
          $row->image_url . "~" .
        //  $catIndex[$row->prodCatID] . "~" .
          $row->id;
      } else {
          $output .= $row->product_url . $google_utm . "~" .


          strip_tags($row->name) . "~" .
	 	preg_replace("/\s+[^a-zA-Z0-9]/", "", $row->description) . "~" .

          $row->price . "~" .
          $row->image_url . "~" .
     //     $catIndex[$row->prodCatID] . "~" .
          $row->id;
      }

      //optional values section
      if(OPTIONS_TONEN == 1) {
         if(OPTIONS_TONEN_AGE_RANGE == 1)
            $output .= "~" . OPTIONS_AGE_RANGE;
         if(OPTIONS_TONEN_BRAND == 1)
         // $output .= "~" . (isset($row->brand) ? $row->brand : (strlen(OPTIONS_BRAND) ? OPTIONS_BRAND : "Not Supported"));

        //  $output .= "~" . (isset($row->mfgName) ? $row->mfgName : (strlen(OPTIONS_BRAND) ? OPTIONS_BRAND : "Not Supported"));


          $output .= "~" . (in_array($row->mfgName,explode(",",OPTIONS_MANUFACTURERS_NAME)) ? '' : $row->mfgName);


          //  $output .= "~" . (in_array($row->mfgName,explode(",",OPTIONS_MANUFACTURERS_NAME)) ? '' : $row->mfgName);
         if(OPTIONS_TONEN_CONDITION == 1)
            $output .= "~" . OPTIONS_CONDITION;
         if(OPTIONS_TONEN_CURRENCY == 1)
            $output .= "~" . OPTIONS_DEFAULT_CURRENCY;
         if(OPTIONS_TONEN_EXPIRATION == 1)
            $output .= "~" . $feed_exp_date;
         if(OPTIONS_TONEN_FEED_LANGUAGE == 1)
            $output .= "~" . OPTIONS_DEFAULT_FEED_LANGUAGE;
         if(OPTIONS_TONEN_FEED_QUANTITY == 1)
            $output .= "~" . $row->quantity;
         if(OPTIONS_TONEN_GTIN == 1)
            $output .= "~" . (isset($row->gtin) ? $row->gtin : (strlen(OPTIONS_GTIN) ? OPTIONS_GTIN : "Not Supported"));
         if(OPTIONS_TONEN_ISBN == 1)
            $output .= "~" . (isset($row->isbn) ? $row->isbn : (strlen(OPTIONS_ISBN) ? OPTIONS_ISBN : "Not Supported"));
         if(OPTIONS_TONEN_MADE_IN == 1)
            $output .= "~" . OPTIONS_MADE_IN;
         if(OPTIONS_TONEN_MANUFACTURERS_ID == 1)

            //$output .= "~" . (in_array($row->mfgName,explode(",",OPTIONS_MANUFACTURERS_NAME)) ? '' : $row->mfgName);
            $output .= "~" . nl2br($row->prodModel);

         if(OPTIONS_TONEN_PAYMENT_ACCEPTED == 1)
            $output .= "~" . OPTIONS_PAYMENT_ACCEPTED_METHODS;
         if(OPTIONS_TONEN_PRODUCT_MODEL == 1)
            $output .= "~" . $row->prodModel;
         if(OPTIONS_TONEN_PRODUCT_TYPE == 1)
            $output .= "~" . ((OPTIONS_PRODUCT_TYPE == strtolower('full')) ? $catIndex[$row->prodCatID] : $row->catName);
         if(OPTIONS_TONEN_SHIPPING == 1)
            $output .= "~" . ((OPTIONS_PRODUCT_TYPE == strtolower('full')) ? $catIndex[$row->prodCatID] : $row->catName);
         if(OPTIONS_TONEN_INCLUDE_TAX == 1)
            $output .= "~" . OPTIONS_TAX_STRING;
         if(OPTIONS_TONEN_UPC == 1)
            $output .= "~" . (isset($row->upc) ? $row->upc : (strlen(OPTIONS_UPC) ? OPTIONS_UPC : "Not Supported"));
         if(OPTIONS_TONEN_WEIGHT == 1)
            $output .= "~" . $row->prodWeight . ' ' .OPTIONS_WEIGHT_ACCEPTED_METHODS;

         /******************* BEGIN HANDLING THE ATTRIBUTES ********************/
         if (OPTIONS_TONEN_ATTRIBUTES == 1)
         {
            $products_attributes_query = mysql_query("select count(*) as total from products_options popt, products_attributes patrib where patrib.products_id='" . $row->id . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . (int)1 . "'");
            $products_attributes = mysql_fetch_object($products_attributes_query);
            if ($products_attributes->total > 0) {
              $products_options_name_query = mysql_query("select distinct popt.products_options_id, popt.products_options_name from products_options popt, products_attributes patrib where patrib.products_id='" . (int)$row->id . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . (int)1 . "' order by popt.products_options_name") or die(mysql_error());

              $trackTabs = '';

              while ($products_options_name = mysql_fetch_object($products_options_name_query)) {
                $products_options_array = array();
                $products_options_query = mysql_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from products_attributes pa, products_options_values pov where pa.products_id = '" . (int)$row->id . "' and pa.options_id = '" . $products_options_name->products_options_id . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)1 . "'");
                while ($products_options = mysql_fetch_object($products_options_query)) {
                  $products_options_array[] = array('id' => $products_options->products_options_values_id, 'text' => $products_options->products_options_values_name);
                }

                for ($a = 0; $a < count($attributesColumns); ++$a)
                {
                   if ($products_options_name->products_options_name == $attributesColumns[$a])
                   {
                     if ($a == 0)
                       $trackTabs = "~";
                     else
                     {
                       if (empty($trackTabs))
                         $trackTabs = str_repeat("~", $a);
                       $trackTabs .= "~";
                     }

                     $output .= $trackTabs;
                     foreach ($products_options_array as $arr)
                       $output .=  $arr['text'] . ',';
                     $output = substr($output, 0, -1);
                   }
                }
              }
            }
         }
         /******************* END HANDLING THE ATTRIBUTES ********************/
      }
      $output .= " \n";
   }

   $already_sent[$row->id] = 1;
   $loop_counter++;

   if ($loop_counter>750) {
      $fp = fopen( $OutFile , "a" );
      $fout = fwrite( $fp , $output );
      fclose( $fp );
      $loop_counter = 0;
      $output = "";
   }
}

$fp = fopen( $OutFile , "a" );
$fout = fwrite( $fp , $output );
fclose( $fp );
echo "<table><tr><td><img src=\"images/google-shopping.jpg\" alt=\"Google Shopping\" align=\"left\"></td><td style=\"font-family: verdana; font-size:11px;\"><b>Google shopping feed is geupdate!</b><br><br>Bestand: <a href=\"" . $OutFile . "\" target=\"_blank\">" . $destination_file . "</a></td></tr></table>\n";
chmod($OutFile, 0777);


//Start FTP

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 is mislukt!<BR>\n";
      return false;
   } else {
      echo "Uploaded " . $ftpsourcefile . " naar " . $ftpservername . " als " . $ftpdestinationfile . "<BR>\n";
   }

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

   return true;
}

if (FTP_ENABLED)
   ftp_file( "uploads.google.com", FTP_USERNAME, FTP_PASSWORD, $source_file, "", $destination_file);

//End FTP


//  ---------
$etimer = explode( ' ', microtime() );
$etimer = $etimer[1] + $etimer[0];
echo '<p style="font-family: verdana; font-size:11px; margin:auto; text-align:center">';
printf( "Feed is gemaakt in: <b>%f</b> seconden.", ($etimer-$stimer) );
echo '</p>';
//  ---------

/*


?>
The line:

Code: Select all

FORMAT( IFNULL(specials.specials_new_products_price, products_groups.customers_group_price) * " . $taxCalc . ",2) AS price,
Is the line that is not working, that either happened because the database is not allowing me acces to that table and column or it is because every entry in that column has 3 different values.
Let me explain
The table PRODUCTS_GROUPS has 3 columns, "product_id", "customers_group_price" and "customers_group_id", that last column is what i want, but there is also the customers_group_id, that has 3 different values, and all i want is to take only the prices from "customers_group_price" that have a ID 0 in the "customers_group_id"

From another script on my site i have this code

Code: Select all

<?php
  } else {
//    $product_info_query = tep_db_query("select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, pd.products_url, p.products_price, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");
//    $product_info = tep_db_fetch_array($product_info_query);
   $product_info_query = tep_db_query("select p.products_new, p.algam_products_number, p.products_id, pd.products_youtube, p.products_levertijd_aanvraag, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, pd.products_url, p.products_price, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id,  p.products_availability_id, p.products_subimage1, p.products_subimage2, p.products_subimage3, p.products_subimage4, p.products_subimage5, p.products_subimage6, pd.products_sound, pd.products_video, p.products_quantity from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");
   $product_info = tep_db_fetch_array($product_info_query);

    tep_db_query("update " . TABLE_PRODUCTS_DESCRIPTION . " set products_viewed = products_viewed+1 where products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and language_id = '" . (int)$languages_id . "'");

   // BOF Separate Price per Customer
   
        $scustomer_group_price_query = tep_db_query("select customers_group_price from " . TABLE_PRODUCTS_GROUPS . " where products_id = '" . (int)$HTTP_GET_VARS['products_id']. "' and customers_group_id =  '" . $customer_group_id . "'");
        if ($scustomer_group_price = tep_db_fetch_array($scustomer_group_price_query)) {
        	
        	if ($customer_group_id == 2) {
	        	$sql = "select * from customers_dealer_manufacturers where customers_id=".(int)$customer_id." and manufacturers_id=".(int)$product_info['manufacturers_id'];
	        	$q = tep_db_query($sql);
	        	
	        	if ($r = tep_db_fetch_array($q)) {
	        		$product_info['products_price']= $scustomer_group_price['customers_group_price'];
	        	}
        	} else {
        		$product_info['products_price']= $scustomer_group_price['customers_group_price'];
        	}
		}
So the way, this last script gets it's price, that is how MY script should get his price, so how do i do this ?
[/quote]

Re: PHP/SQL problem

Posted: Tue Aug 16, 2011 5:30 pm
by twinedev
Well, to get any help, you are going to need to edit your post and post the correct code, so that it is actual readable. Then more people will take an interest in it.

-Greg

Re: PHP/SQL problem

Posted: Tue Aug 16, 2011 5:59 pm
by califdon
I don't mean to pile on, but let me suggest a few ways you might attract somebody to actually try to answer you:

The very first thing you need to do is show the exact error message you are receiving.

Then show us the specific line of code that actually caused the error, as reported by the error message (you show us a line that you say isn't working, but without seeing the exact error message, it is impossible for us to know if it is or is not). You won't find many people willing to spend an hour or more trying to make sense of an extremely long script that is not enclosed in BBCode syntax tags to make it at least a little easier to read.

This might be followed by a brief explanation of what you are trying to do. Don't make us guess.

In most cases it is not necessary to show an entire script. If you don't know enough PHP to know how much of the code is involved in your particular problem, you're unlikely to be able to make use of the advice that people will probably give you. We do have a desire to help you and others, even beginners, but we are NOT a substitute for learning the basics of the language and we do NOT write or rewrite entire scripts for those who haven't learned how to do so for themselves.

If you follow the suggestions above, I think you will have a much better chance that someone will be willing to invest some of their time to help you.

Re: PHP/SQL problem

Posted: Wed Aug 17, 2011 6:19 pm
by phphelpme
Well said califdon. That was a moving speech but you are totally correct. I have checked this thread out earlier but just looking at it makes my head hurt and not knowing what is actually wrong with no error messages makes me feel like I am finding a needle in a very very large haystack.

Best wishes

Re: PHP/SQL problem

Posted: Thu Aug 18, 2011 4:03 am
by madmega
Yeps, your right, it looks like a mess.
This is the SQL error i am getting
: SQL error Unknown column 'products_groups.customers_group_price' in 'field list'| sql = SELECT concat(

Re: PHP/SQL problem

Posted: Thu Aug 18, 2011 5:19 am
by phphelpme
That basically means that within your database it can not find the column 'products_groups.customers_group_price' within your table name 'field list'.

Best wishes

Re: PHP/SQL problem

Posted: Fri Aug 19, 2011 5:08 am
by madmega
Yes, i know, but i do not understand why, because the table and column are present

Re: PHP/SQL problem

Posted: Fri Aug 19, 2011 5:19 am
by phphelpme
All I can suggest is you supple the structure of your database and the code which this script uses so we can test it out for you.

Another piece of advice which was said earlier is you need to add <?php // Your code in the middle ?> to your above script because its very hard to read it without the correct formating.

Best wishes

Re: PHP/SQL problem

Posted: Fri Aug 19, 2011 12:09 pm
by califdon
madmega wrote:Yes, i know, but i do not understand why, because the table and column are present
Wrong. Believe me, if the error message says "Unknown column 'products_groups.customers_group_price' in 'field list'", that is exactly what it means. If you are ever going to learn how to program, you need to understand that these error messages mean what they say. In every case, it is your interpretation that is wrong. No exceptions. Your table "products_groups" does not have a column "customers_group_price", so MySQL has told you that it can't process your query, which specifies that column.

Re: PHP/SQL problem

Posted: Fri Aug 19, 2011 2:31 pm
by mikosiko
califdon wrote:Your table "products_groups" does not have a column "customers_group_price"
well... that is not necessarily correct ... error could mean also that in the query the OP is using a table.field that has not been included in the FROM (or joins)... and looks like that in this case he didn't include the table products_groups ... either way your suggestions for the OP still 100% valid.

Code: Select all

$sql = "
SELECT concat( ' " . $productURL . "'' ,products.products_id) AS product_url,
products_model AS prodModel,
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_quantity AS quantity,
products.products_status AS prodStatus,
products.products_weight AS prodWeight, " . $extraFields . "
FORMAT( IFNULL(specials.specials_new_products_price, products_groups.customers_group_price) * " . $taxCalc . ",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 )
left join specials on ( specials.products_id = products.products_id AND ( ( (specials.expires_date > CURRENT_DATE) OR (specials.expires_date is NULL) OR (specials.expires_date = ) ) AND ( specials.status = 1 ) ) )

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 " . $quotes . "
AND categories_description.language_id = " . TAAL_ID_LANGUAGE . "
AND products_description.language_id = " . TAAL_ID_LANGUAGE . "
ORDER BY
products.products_id ASC
";

Re: PHP/SQL problem

Posted: Fri Aug 19, 2011 5:56 pm
by califdon
Touchè, mikosiko. I didn't even try to pile through that query.