Syntax error in advanced_search_result.php

[aogden]

A few weeks ago I added Separate Pricing Per Customer (SPPC) Version 4.2.1a to a store I have been working on. Everything is working great, but now we find that when we go to do a search we get the following error:

 

Products meeting the search criteria

 

1109 - Unknown table 'pd' in where clause

 

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id) where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%hello%' or p.products_model like '%hello%' or m.manufacturers_name like '%hello%' or p2pef.products_extra_fields_value like '%hello%') )

 

 

[TEP STOP]

 

I'm understanding that this is a syntax error in the code. I've looked at it over and over again but so far haven't figured out where the problem is. I believe the following lines of code are the relevant ones. Could anyone help me figure out where the problem is?? (If you need the whole file I can post that as well.)

 

 

   if ($status_tmp_product_prices_table == true) {
  $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, tmp_pp.products_price, p.products_tax_class_id, if(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price ";
  } elseif ($status_tmp_special_prices_table == true) {
 $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, if(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, if(s.status, s.specials_new_products_price, p.products_price) as final_price ";	
  } else {
 $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, NULL as specials_new_products_price, NULL as final_price ";	
  }
  // next line original select query
  // $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price ";


 if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {
$select_str .= ", SUM(tr.tax_rate) as tax_rate ";
 }

  if ($status_tmp_product_prices_table == true) {
 $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . $product_prices_table . " as tmp_pp using(products_id)";
  } elseif ($status_tmp_special_prices_table == true) {
 $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id ";
  } else {
$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) ";
  }
 // EOF Separate Pricing Per Customer

/* START: Extra Fields Contribution
 $from_str = "from (" . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " p2pef on p.products_id=p2pef.products_id) left join " . TABLE_MANUFACTURERS . " m on m.manufacturers_id=p.manufacturers_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";
// END: Extra Fields Contribution  */

 $where_str = " where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id ";

 

Alex

[spooks]

This may be related to your version of sql, what is it?

 

try changing the line to:

 

$where_str = " where p.products_status = 1 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id ";

 

be careful when cutting parts out of the doc, there are many occurances of '$where_str = ' in this doc.

[♥FWR Media]

That's the wrong bit of code

 

select count

 

is the clue

[aogden]

This may be related to your version of sql, what is it?

When I ran info.php5 it gave me a page titled "PHP Version 5.0.5". In the mysql section it had this:

Client API version 3.23.54

 

This site is hosted by Earthlink. When I went into the SQL section of the admin panel I clicked on the button for documentation for SQL. It took me to a page titled:

MySQL 3.23, 4.0, 4.1 Reference Manual

 

try changing the line to:

 

$where_str = " where p.products_status = 1 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id ";

 

be careful when cutting parts out of the doc, there are many occurances of '$where_str = ' in this doc.

Tried it but it didn't help. Only found one place $where_str was found followed by "where p.products_status...".

 

I noticed in the original error message that there were a couple of places where there were double spaces and then I also noticed a section toward the end of that message where it opened with "((" but closed with ") )" (space added). Is that right?

 

I appreciate the help on this.

 

Alex

[spooks]

That's the wrong bit of code

 

select count

 

is the clue

 

 

I`m sure you're right, its not easy to track that one down though, its done in classes/split_page_results.php

[aogden]

That's the wrong bit of code

 

select count

 

is the clue

I searched advanced_search_result.php for "select count" and that phrase is not in the file anywhere.

 

Is it possible I am looking in the wrong place?? The search box is found on the index.php page (that's what shows in the address bar). I type a search term into the search box and hit enter and the error appears with advanced_search_result.php now in the address bar.

 

"select count" appears 3 times in index.php. Here are the two sections where the 3 uses are found:

// the following cPath references come from application_top.php
 $category_depth = 'top';
 if (isset($cPath) && tep_not_null($cPath)) {
$categories_products_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS_TO_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'");
$cateqories_products = tep_db_fetch_array($categories_products_query);
if ($cateqories_products['total'] > 0) {
  $category_depth = 'products'; // display products
} else {
  $category_parent_query = tep_db_query("select count(*) as total from " . TABLE_CATEGORIES . " where parent_id = '" . (int)$current_category_id . "'");
  $category_parent = tep_db_fetch_array($category_parent_query);
  if ($category_parent['total'] > 0) {
	$category_depth = 'nested'; // navigate through the categories
  } else {
	$category_depth = 'products'; // category has no products, but display the 'no products' message
  }
}
 }
.
.code left out for brevity
.
// check to see if there are deeper categories within the current category
  $category_links = array_reverse($cPath_array);
  for($i=0, $n=sizeof($category_links); $i<$n; $i++) {
	$categories_query = tep_db_query("select count(*) as total from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$category_links[$i] . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "'");
	$categories = tep_db_fetch_array($categories_query);
	if ($categories['total'] < 1) {
	  // do nothing, go through the loop
	} else {
	  $categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$category_links[$i] . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name");
	  break; // we've found the deepest category the customer is in
	}
  }
} else {
  $categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$current_category_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name");
}

Alex

[aogden]

I`m sure you're right, its not easy to track that one down though, its done in classes/split_page_results.php

There is only one place in classes/split_page_results.php where "select count" is found. Here it is:

	  $count_query = tep_db_query("select count(" . $count_string . ") as total " . substr($this->sql_query, $pos_from, ($pos_to - $pos_from)));
  $count = tep_db_fetch_array($count_query);

Alex

[spooks]

Your going to have to go over what you have modified exactly to track this down, I could not see any error other than the minor one I pointed out before.

 

To explain the process:

 

advanced_search_result.php sets up the query with your search params, this is then passed to modules/product_listing.php which process the query after first passing it to the split_page_results.php class.

[aogden]

Your going to have to go over what you have modified exactly to track this down, I could not see any error other than the minor one I pointed out before.

 

To explain the process:

 

advanced_search_result.php sets up the query with your search params, this is then passed to modules/product_listing.php which process the query after first passing it to the split_page_results.php class.

So I guess I have no alternative but to do this the hard way. If I must I must. I feel fairly certain that the problem lies in combining the Products Extra Field and the Special Pricing Per Customer contributions into these files.

 

You've been a big help and I DO appreciate it.

 

Alex

[Jan Zonjee]

I feel fairly certain that the problem lies in combining the Products Extra Field and the Special Pricing Per Customer contributions into these files.

Indeed, because you messed up seriously in adding (and commenting out the Products Extra Field code) when you deleted this part above the $where_str:

  if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {
if (!tep_session_is_registered('customer_country_id')) {
  $customer_country_id = STORE_COUNTRY;
  $customer_zone_id = STORE_ZONE;
}
$from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left join " . TABLE_ZONES_TO_GEO_ZONES . " gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')";
 }

 $from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

 $where_str = " where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id ";

[aogden]

All,

 

I finally found the time to look at it properly. The problem was that the file had both Product Extra Fields and the Special Pricing Per Customer contribution installed. I realized that we didn't have any need to search those extra fields so could just forget the lines added for that contribution. Thus, I was able to just upload the new file provided with SPPC. Now it works great! Thanks to all for your help.

 

Alex