1064 SQL Syntax error order by pd.products_name in categories

[remixstore]

I get this error as a user when cruising around in any category's subcategory, whether logged in or not... whether this is an item there or not.

 

I can access the product thru New Products and I can view the product...

 

It seems to be categories.php ???

 

I have Downloads Controller, Easy Populate, Category Fields installed.

 

 

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by pd.products_name' at line 1

 

select count(p.products_id) as total order by pd.products_name

 

[TEP STOP]

 

 

Any insight is greatly appreciated

[Jan Zonjee]

I get this error as a user when cruising around in any category's subcategory, whether logged in or not... whether this is an item there or not.

 

I can access the product thru New Products and I can view the product...

 

It seems to be categories.php ???

 

I have Downloads Controller, Easy Populate, Category Fields installed.

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by pd.products_name' at line 1

 

select count(p.products_id) as total order by pd.products_name

 

[TEP STOP]

Any insight is greatly appreciated

This seems to originate from catalog/includes/classes/split_page_results.php. Apparently the sql string it gets fed is messed up.

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

[remixstore]

ok, i'll tear thru that.

 

this is what I have currently:

 

<?php
/*
 $Id: split_page_results.php,v 1.15 2003/06/09 22:35:34 hpdl Exp $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/

 class splitPageResults {
var $sql_query, $number_of_rows, $current_page_number, $number_of_pages, $number_of_rows_per_page, $page_name;

/* class constructor */
function splitPageResults($query, $max_rows, $count_key = '*', $page_holder = 'page') {
  global $HTTP_GET_VARS, $HTTP_POST_VARS;

  $this->sql_query = $query;
  $this->page_name = $page_holder;

  if (isset($HTTP_GET_VARS[$page_holder])) {
	$page = $HTTP_GET_VARS[$page_holder];
  } elseif (isset($HTTP_POST_VARS[$page_holder])) {
	$page = $HTTP_POST_VARS[$page_holder];
  } else {
	$page = '';
  }

  if (empty($page) || !is_numeric($page)) $page = 1;
  $this->current_page_number = $page;

  $this->number_of_rows_per_page = $max_rows;

  $pos_to = strlen($this->sql_query);
  $pos_from = strpos($this->sql_query, ' from', 0);

  $pos_group_by = strpos($this->sql_query, ' group by', $pos_from);
  if (($pos_group_by < $pos_to) && ($pos_group_by != false)) $pos_to = $pos_group_by;

  $pos_having = strpos($this->sql_query, ' having', $pos_from);
  if (($pos_having < $pos_to) && ($pos_having != false)) $pos_to = $pos_having;

  $pos_order_by = strpos($this->sql_query, ' order by', $pos_from);
  if (($pos_order_by < $pos_to) && ($pos_order_by != false)) $pos_to = $pos_order_by;

  if (strpos($this->sql_query, 'distinct') || strpos($this->sql_query, 'group by')) {
	$count_string = 'distinct ' . tep_db_input($count_key);
  } else {
	$count_string = tep_db_input($count_key);
  }

  $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);

  $this->number_of_rows = $count['total'];

  $this->number_of_pages = ceil($this->number_of_rows / $this->number_of_rows_per_page);

// BEGIN Modified 2005-09-21
if ($this->number_of_pages == 0) {
$this->number_of_rows_per_page=1;
}
// END Modified 2005-09-21

if ($this->current_page_number > $this->number_of_pages) {
$this->current_page_number = $this->number_of_pages;
}


/* class functions */

// display split-page-number-links
function display_links($max_page_links, $parameters = '') {
  global $PHP_SELF, $request_type;

  $display_links_string = '';

  $class = 'class="pageResults"';

  if (tep_not_null($parameters) && (substr($parameters, -1) != '&')) $parameters .= '&';

// previous button - not displayed on first page
  if ($this->current_page_number > 1) $display_links_string .= '<a href="' . tep_href_link(basename($PHP_SELF), $parameters . $this->page_name . '=' . ($this->current_page_number - 1), $request_type) . '" class="pageResults" title=" ' . PREVNEXT_TITLE_PREVIOUS_PAGE . ' "><u>' . PREVNEXT_BUTTON_PREV . '</u></a>  ';

// check if number_of_pages > $max_page_links
  $cur_window_num = intval($this->current_page_number / $max_page_links);
  if ($this->current_page_number % $max_page_links) $cur_window_num++;

  $max_window_num = intval($this->number_of_pages / $max_page_links);
  if ($this->number_of_pages % $max_page_links) $max_window_num++;

// previous window of pages
  if ($cur_window_num > 1) $display_links_string .= '<a href="' . tep_href_link(basename($PHP_SELF), $parameters . $this->page_name . '=' . (($cur_window_num - 1) * $max_page_links), $request_type) . '" class="pageResults" title=" ' . sprintf(PREVNEXT_TITLE_PREV_SET_OF_NO_PAGE, $max_page_links) . ' ">...</a>';

// page nn button
  for ($jump_to_page = 1 + (($cur_window_num - 1) * $max_page_links); ($jump_to_page <= ($cur_window_num * $max_page_links)) && ($jump_to_page <= $this->number_of_pages); $jump_to_page++) {
	if ($jump_to_page == $this->current_page_number) {
	  $display_links_string .= ' <b>' . $jump_to_page . '</b> ';
	} else {
	  $display_links_string .= ' <a href="' . tep_href_link(basename($PHP_SELF), $parameters . $this->page_name . '=' . $jump_to_page, $request_type) . '" class="pageResults" title=" ' . sprintf(PREVNEXT_TITLE_PAGE_NO, $jump_to_page) . ' "><u>' . $jump_to_page . '</u></a> ';
	}
  }

// next window of pages
  if ($cur_window_num < $max_window_num) $display_links_string .= '<a href="' . tep_href_link(basename($PHP_SELF), $parameters . $this->page_name . '=' . (($cur_window_num) * $max_page_links + 1), $request_type) . '" class="pageResults" title=" ' . sprintf(PREVNEXT_TITLE_NEXT_SET_OF_NO_PAGE, $max_page_links) . ' ">...</a> ';

// next button
  if (($this->current_page_number < $this->number_of_pages) && ($this->number_of_pages != 1)) $display_links_string .= ' <a href="' . tep_href_link(basename($PHP_SELF), $parameters . 'page=' . ($this->current_page_number + 1), $request_type) . '" class="pageResults" title=" ' . PREVNEXT_TITLE_NEXT_PAGE . ' "><u>' . PREVNEXT_BUTTON_NEXT . '</u></a> ';

  return $display_links_string;
}

// display number of total products found
function display_count($text_output) {
  $to_num = ($this->number_of_rows_per_page * $this->current_page_number);
  if ($to_num > $this->number_of_rows) $to_num = $this->number_of_rows;

  $from_num = ($this->number_of_rows_per_page * ($this->current_page_number - 1));

  if ($to_num == 0) {
	$from_num = 0;
  } else {
	$from_num++;
  }

  return sprintf($text_output, $from_num, $to_num, $this->number_of_rows);
}
 }
?>

 

I think it's the lines 61-65

// BEGIN Modified 2005-09-21
if ($this->number_of_pages == 0) {
$this->number_of_rows_per_page=1;
}
// END Modified 2005-09-21

[remixstore]

http://mastershot.tv/shop

[Jan Zonjee]

It has nothing to do with the class split_page_results and everything to do with the adapted sql queries that are coming from index.php. When I use the manufacturers drop-down I get this:

1054 - Unknown column 'p.products_artist' in 'field list'

select p.products_model, m.manufacturers_name, p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_artist, 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 from products p, products_description pd, manufacturers m left join specials s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '14' order by pd.products_name limit 0, 20

So you are changing your queries for new features and make mistakes. For example if you use FROM instead of from I think it already goes wrong, because the split page results class can't find "from" in the sql query.

[remixstore]

ah, that's a great piece of insight actually. I know what that is from... it's the NewFolders4 mod I had installed and pulled out. I thought I got em all... apparently not. I'll tackle that. THANKS

 

 

It has nothing to do with the class split_page_results and everything to do with the adapted sql queries that are coming from index.php. When I use the manufacturers drop-down I get this:

1054 - Unknown column 'p.products_artist' in 'field list'

select p.products_model, m.manufacturers_name, p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_artist, 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 from products p, products_description pd, manufacturers m left join specials s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '14' order by pd.products_name limit 0, 20

So you are changing your queries for new features and make mistakes. For example if you use FROM instead of from I think it already goes wrong, because the split page results class can't find "from" in the sql query.

 

 

that certainly fixed the manufacturer drop down in a heartbeat... thanks... but the other part when you navigate the subcategories still is broken... :(

 

u were right... it was a stupid error that I overlooked. it's fixed and I can't even find an error now :) Thanks

[netstep]

So you are changing your queries for new features and make mistakes. For example if you use FROM instead of from I think it already goes wrong, because the split page results class can't find "from" in the sql query.

 

Being a self-taught LAMP person, I had no idea this would be case sensitive.

...I've wasted dozens of hours over the years trying to figure this out.

Sometimes the right answer is the simple answer....

Thank you for sharing this.