Jump to content
dev osCommerce
Forum
  • Checkout
  • Login
  • Contact Us
  • Get in touch

osCommerce

The e-commerce.

New Demo
Our Partners

Catalog Side 1064 - You have an error in your SQL syntax;

iondarie

By iondarie
in General Support

Recommended Posts

iondarie

iondarie

Posted
Posted

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 limit 20, 20' at line 1

 

select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, 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_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id = s.products_id, products_to_categories p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '51' order by order by pd.products_name limit 20, 20

 

This is the big eror that i get when i try to view the second page of a subcategory :(

 

here check it out: http://www.fanatics.ro/index.php?cPath=34_...t=2a&page=2

Link to comment
Share on other sites
iondarie

iondarie

Posted
  • Author
Posted

here is the code from split_page_results.php

 

<?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 © 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);

 

 

 

if ($this->current_page_number > $this->number_of_pages) {

 

$this->current_page_number = $this->number_of_pages;

 

}

 

 

 

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

 

if ($offset < 0)

{

$offset = 0 ;

}

 

$this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

 

}

 

 

 

/* 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 . ' ">' . PREVNEXT_BUTTON_PREV . '</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) . ' ">' . $jump_to_page . '</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 . ' ">' . PREVNEXT_BUTTON_NEXT . '</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);

 

}

 

}

 

?>

Link to comment
Share on other sites
Jan Zonjee

Jan Zonjee

Posted
Posted
here is the code from split_page_results.php

Nothing out of the ordinary. Doesn't mean it cannot be in the module that is calling that class, like includes/modules/products_listing.php.

 

Code is better posted in between code tags ([ then the word code and then ] as the opening code tag).

Link to comment
Share on other sites
iondarie

iondarie

Posted
  • Author
Posted

here is the product_listing.php code

 

<?php
$product_desc['products_description'] = substr(strip_tags($product_desc['products_description']), 0, 100);
$listing_split = new splitPageResults($listing_sql, MAX_DISPLAY_SEARCH_RESULTS, 'p.products_id');

 if ( ($listing_split->number_of_rows > 0) && ( (PREV_NEXT_BAR_LOCATION == '1') || (PREV_NEXT_BAR_LOCATION == '3') ) ) {
?>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr>
<td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
<td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
<?php
 }

 $list_box_contents = array();

 for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
switch ($column_list[$col]) {
  case 'PRODUCT_LIST_MODEL':
	$lc_text = TABLE_HEADING_MODEL;
	$lc_align = '';
	break;
  case 'PRODUCT_LIST_NAME':
	$lc_text = TABLE_HEADING_PRODUCTS;
	$lc_align = '';
	break;
  case 'PRODUCT_LIST_MANUFACTURER':
	$lc_text = TABLE_HEADING_MANUFACTURER;
	$lc_align = '';
	break;
  case 'PRODUCT_LIST_PRICE':
	$lc_text = TABLE_HEADING_PRICE;
	$lc_align = 'right';
	break;
  case 'PRODUCT_LIST_QUANTITY':
	$lc_text = TABLE_HEADING_QUANTITY;
	$lc_align = 'right';
	break;
  case 'PRODUCT_LIST_WEIGHT':
	$lc_text = TABLE_HEADING_WEIGHT;
	$lc_align = 'right';
	break;
  case 'PRODUCT_LIST_IMAGE':
	$lc_text = TABLE_HEADING_IMAGE;
	$lc_align = 'center';
	break;

}



if ( ($column_list[$col] != 'PRODUCT_LIST_IMAGE') ) {

  $lc_text = tep_create_sort_heading($HTTP_GET_VARS['sort'], $col+1, $lc_text);

}



$list_box_contents[0][] = array('align' => $lc_align,

								'params' => 'class="productListing-heading"',


								'text' => ' ' . $lc_text . ' ');
 }

 if ($listing_split->number_of_rows > 0) {
$rows = 0;

$column = 0;

echo '	 

		  <table cellspacing=0 cellpadding=0>

		   <tr><td colspan=10><img src=images/m35.gif width=526 height=4></td></tr>

		   <tr><td valign=top class=bg2><table cellspacing=0 cellpadding=0><tr>

';



$listing_query = tep_db_query($listing_split->sql_query);
while ($listing = tep_db_fetch_array($listing_query)) {

  $product_contents = array();

  $rows++;

  if (($rows/2) == floor($rows/2)) {
	$list_box_contents[] = array('params' => 'class="productListing-even"');
  } else {
	$list_box_contents[] = array('params' => 'class="productListing-odd"');
  }

  $cur_row = sizeof($list_box_contents) - 1;

  for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
	$lc_align = '';

	switch ($column_list[$col]) {
	  case 'PRODUCT_LIST_MODEL':
		$lc_align = '';
		$lc_text = ' ' . $listing['products_model'] . ' ';
		break;
	  case 'PRODUCT_LIST_NAME':
		$lc_align = '';
		if (isset($HTTP_GET_VARS['manufacturers_id'])) {

		  $lc_text = '<a  href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing['products_id']) . '">' . $listing['products_name'] . '</a>';

		} else {

		  $lc_text = '<a  href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing['products_id']) . '">' . $listing['products_name'] . '</a>';

		}
		break;

	  case 'PRODUCT_LIST_MANUFACTURER':

		$lc_align = '';
		$lc_text = ' <a href="' . tep_href_link(FILENAME_DEFAULT, 'manufacturers_id=' . $listing['manufacturers_id']) . '">' . $listing['manufacturers_name'] . '</a> ';
		break;
	  case 'PRODUCT_LIST_PRICE':
		$lc_align = 'right';
		if (tep_not_null($listing['specials_new_products_price'])) {

		  $lc_text = '<span style="color:#7F7F7F;font-size:12px"><s>' .  $currencies->display_price($listing['products_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . '</s></span><br><span class=t15>' . $currencies->display_price($listing['specials_new_products_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . '</span>';

		} else {

		  $lc_text = '<span> ' . $currencies->display_price($listing['products_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . ' </span>';

		}

		break;
	  case 'PRODUCT_LIST_QUANTITY':
		$lc_align = 'right';
		$lc_text = ' ' . $listing['products_quantity'] . ' ';
		break;
	  case 'PRODUCT_LIST_WEIGHT':
		$lc_align = 'right';
		$lc_text = ' ' . $listing['products_weight'] . ' ';

		break;

		case 'PRODUCT_DESCRIPTION':

		$lc_align = 'right';

		$lc_text = ' ' . $listing['products_description'] . ' ';

		break;
	  case 'PRODUCT_LIST_IMAGE':
		$lc_align = 'center';
		if (isset($HTTP_GET_VARS['manufacturers_id'])) {

		  $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing['products_image'], $listing['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT, ' class=br') . '</a>';

		} else {

		  $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing['products_image'], $listing['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT, ' class=br') . '</a>';

		}
		break;


	}



	$list_box_contents[$cur_row][] = array('align' => $lc_align,


										   'params' => 'class="productListing-data"',
										   'text'  => $lc_text);

	$product_contents[] = $lc_text;	   

  }
require('includes/configure.php');
$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
$new_products['products_name'] = tep_get_products_name($new_products['products_id']);
$product_query = tep_db_query("select products_description  from " . TABLE_PRODUCTS_DESCRIPTION . " where products_id = '" . $listing['products_id'] . "' and language_id = '" . (int)1 . "'");
$product = tep_db_fetch_array($product_query);
$new_products['products_description'] = $product['products_description'];


  echo '										

<td width=173 valign=top>


 <div style="width:160px; vertical-align:top; margin-left:10px;">
<div style="width:78px; height:78px; float:left;  margin-top:5px;">'.$product_contents[0].'</div>
<div style="width:82px; height:78px; float:left; text-align:right; margin:0px; padding:0px; overflow:hidden;" class="cy">'.$product_contents[1].'</div>
<div style="width:160px; vertical-align:top; text-align:right; margin-top:5px; margin-bottom:5px;" class="cy1" >'.$product_contents[2].'</div>
<div style="height:1px; text-align:center; margin-top:5px; margin-bottom:5px;"><img src=images/m36.gif width=143 height=1></div>

<div style="width:160px; vertical-align:top; text-align:left; padding-left:8px;">'.preg_replace('/\s\S*$/i', '', substr($new_products['products_description'], 0, 80)).' ...</div>

<div style="height:1px; text-align:center; font-size:1px;  margin-top:5px; margin-bottom:5px;"><img src=images/m36.gif width=143 height=1></div>

<div style="width:150px; text-align:center; margin-bottom:5px;"><a href="'.tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $listing['products_id']) . '">' . tep_image_button('small_view.gif') . '</a></div>
 </div>


				 </td>



  ';

  $column ++;

  if ($column >= 3) {

	$rows ++;

	$column = 0;

	echo '			

				 </tr>

				</table>

				<table cellspacing=0 cellpadding=0>

				 <tr><td><img src=images/m37.gif width=526 height=1></td></tr>

				</table>	 

				<table cellspacing=0 cellpadding=0 border=0>					 

				 <tr>

		 ';

  } else echo '<td width=0></td>';







}



echo '

			</tr></table></td>

			</tr>

		   <tr><td><img src=images/m43.gif width=526 height=7></td></tr>

		  </table>

	 ';



//new productListingBox($list_box_contents);

 } else {
$list_box_contents = array();

$list_box_contents[0] = array('params' => 'class="productListing-odd"');
$list_box_contents[0][] = array('params' => 'class="productListing-data"',
							   'text' => TEXT_NO_PRODUCTS);



echo '<div class=PageHeading>Categories</div>';

new productListingBox($list_box_contents);
 }

 if ( ($listing_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '2') || (PREV_NEXT_BAR_LOCATION == '3')) ) {
?>
<table border="0" width="100%" cellspacing="0" cellpadding="2">

 <tr><td height=20></td></tr>

 <tr>
<td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
<td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
<?php
 }

?>

Link to comment
Share on other sites
iondarie

iondarie

Posted
  • Author
Posted

ok i found the bastard and fixed it :D

 

it seems that my error was coming from here:

 

line 238:

if ( (!isset($HTTP_GET_VARS['sort'])) || (!ereg('^[1-8][ad]', $HTTP_GET_VARS['sort'])) || (substr($HTTP_GET_VARS['sort'], 0, 1) > sizeof($column_list)) ) {
     for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
       if ($column_list[$i] == 'PRODUCT_LIST_NAME') {
         $HTTP_GET_VARS['sort'] = $i+1 . 'a';
         $listing_sql .= " order by pd.products_name";
         break;

 

to fix my problem i simplly removed the a from $HTTP_GET_VARS['sort'] = $i+1 . 'a';

 

if ( (!isset($HTTP_GET_VARS['sort'])) || (!ereg('^[1-8][ad]', $HTTP_GET_VARS['sort'])) || (substr($HTTP_GET_VARS['sort'], 0, 1) > sizeof($column_list)) ) {
     for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
       if ($column_list[$i] == 'PRODUCT_LIST_NAME') {
         $HTTP_GET_VARS['sort'] = $i+1 . '';
         $listing_sql .= " order by pd.products_name";
         break;

 

so thank you OceanRanch for the hint otherwise i think i would have gone mad...

Link to comment
Share on other sites

Archived

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

Go to topic listing
×
×
  • Create New...