Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Syntax error in advanced_search_result.php


aogden

Recommended Posts

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

Link to comment
Share on other sites

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.

Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Link to comment
Share on other sites

That's the wrong bit of code

 

select count

 

is the clue

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 ";

Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...