Syntax error blues

[vancomtech]

:'(

Hi,

 

First, thank you for taking the time to look at my problem. Im probably not the first to have this problem but it making me go grey! I have a few pages showing syntax errors. The first one to go was the about.PHP. I fixed this by simply replacing it with a html page i created and renamed it to about.php. But now i have others going down the same path.

 

I deletedall the products in the specials catalog and now I cant get into it, either through my web site or admin panel.

The error shows up is:

 

Get Them While They're Hot!

 

 

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

 

select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from products p, products_description pd, specials s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and s.status = '1' order by s.specials_date_added DESC limit -9, 9

 

[TEP STOP]

 

 

 

My web site is www.vancomtech.com

 

I used the cpanel and went to file access, and used edit on specials.php but cant for the life of me figure just where the problem is.

 

Any help on this will be immensly appretiated!

Thanks in advance

James

[Velveeta]

:'(

Hi,

 

First, thank you for taking the time to look at my problem. Im probably not the first to have this problem but it making me go grey! I have a few pages showing syntax errors. The first one to go was the about.PHP. I fixed this by simply replacing it with a html page i created and renamed it to about.php. But now i have others going down the same path.

 

I deletedall the products in the specials catalog and now I cant get into it, either through my web site or admin panel.

The error shows up is:

 

Get Them While They're Hot!

 

 

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

 

select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from products p, products_description pd, specials s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and s.status = '1' order by s.specials_date_added DESC limit -9, 9

 

[TEP STOP]

 

 

 

My web site is www.vancomtech.com

 

I used the cpanel and went to file access, and used edit on specials.php but cant for the life of me figure just where the problem is.

 

Any help on this will be immensly appretiated!

Thanks in advance

James

 

This problem can be traced back to the split pages class (includes/classes/split_page_results.php)... Around line 65 of that file, you should find a line that assigns a value to $offset... Change this line to:

 

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

 

This way, the lowest value that can be inserted into the lower limit of the query is 0, right now, it's multiplying the number_of_rows_per_page * -1, and coming out to -9, and so it's trying to set a limit of -9 to 9 for the query results, which is throwing that sql error...

 

Richard.

[vancomtech]

Thank you soooo much richard!!! Really apretiate this, it worked a treat for the specials page. However I am still getting a simualar message come back when I use the admin panel. when i try to go to reports, I can see products viewd correctly, When I click on Products Purchased i get this message:

 

Best Products Purchased

 

No. Products Purchased

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

 

select p.products_id, p.products_ordered, pd.products_name from products p, products_description pd where pd.products_id = p.products_id and pd.language_id = '1' and p.products_ordered > 0 group by pd.products_id order by p.products_ordered DESC, pd.products_name limit -20, 20

 

[TEP STOP]

 

And when I click on Customer Orders-Total I get:

 

Best Customer Orders-Total

 

No. Customers Total Purchased

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

 

select c.customers_firstname, c.customers_lastname, sum(op.products_quantity * op.final_price) as ordersum from customers c, orders_products op, orders o where c.customers_id = o.customers_id and o.orders_id = op.orders_id group by c.customers_firstname, c.customers_lastname order by ordersum DESC limit -20, 20

 

I take it they are in the same file as my specials problem?

 

Again, I will be forever grateful for any insite.

(I can see light!!!!) :D

[Jack_mcs]

The fix is the same. There is a split_page_results.php file in admin too.

 

Jack

[vancomtech]

:thumbsup:

Thanks guys! now im on my way! all I have got to do now is figure out how to customise this site :'( scary for us newbies but i will eventually get there.

Regards and hopefully I can return the favour or atleast help some else in the future.

James

[partsace]

I tried this fix of replacing the line and now I cannot go to the next page. When I click ">>", I see that it moved to page 2, but the customers or orders are still showing the same information as page 1.

 

Scott

[Jack_mcs]

Double check the change you made or paste that part of the code here so someone can look at it.

 

Jack

[partsace]

<?php
/*
 $Id: split_page_results.php,v 1.13 2003/05/05 17:56:50 dgw_ Exp $

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

 Copyright (c) 2002 osCommerce

 Released under the GNU General Public License
*/

 class splitPageResults {
function splitPageResults(&$current_page_number, $max_rows_per_page, &$sql_query, &$query_num_rows) {
  if (empty($current_page_number)) $current_page_number = 1;

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

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

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

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

  $reviews_count_query = tep_db_query("select count(*) as total " . substr($sql_query, $pos_from, ($pos_to - $pos_from)));
  $reviews_count = tep_db_fetch_array($reviews_count_query);
  $query_num_rows = $reviews_count['total'];

  $num_pages = ceil($query_num_rows / $max_rows_per_page);
  if ($current_page_number > $num_pages) {
	$current_page_number = $num_pages;
  }
  $offset = ($this->number_of_rows_per_page * max(0,($this->current_page_number - 1)));
  $sql_query .= " limit " . $offset . ", " . $max_rows_per_page;
}

function display_links($query_numrows, $max_rows_per_page, $max_page_links, $current_page_number, $parameters = '', $page_name = 'page') {
  global $PHP_SELF;

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

// calculate number of pages needing links
  $num_pages = ceil($query_numrows / $max_rows_per_page);

  $pages_array = array();
  for ($i=1; $i<=$num_pages; $i++) {
	$pages_array[] = array('id' => $i, 'text' => $i);
  }

  if ($num_pages > 1) {
	$display_links = tep_draw_form('pages', basename($PHP_SELF), '', 'get');

	if ($current_page_number > 1) {
	  $display_links .= '<a href="' . tep_href_link(basename($PHP_SELF), $parameters . $page_name . '=' . ($current_page_number - 1), 'NONSSL') . '" class="splitPageLink">' . PREVNEXT_BUTTON_PREV . '</a>  ';
	} else {
	  $display_links .= PREVNEXT_BUTTON_PREV . '  ';
	}

	$display_links .= sprintf(TEXT_RESULT_PAGE, tep_draw_pull_down_menu($page_name, $pages_array, $current_page_number, 'onChange="this.form.submit();"'), $num_pages);

	if (($current_page_number < $num_pages) && ($num_pages != 1)) {
	  $display_links .= '  <a href="' . tep_href_link(basename($PHP_SELF), $parameters . $page_name . '=' . ($current_page_number + 1), 'NONSSL') . '" class="splitPageLink">' . PREVNEXT_BUTTON_NEXT . '</a>';
	} else {
	  $display_links .= '  ' . PREVNEXT_BUTTON_NEXT;
	}

	if ($parameters != '') {
	  if (substr($parameters, -1) == '&') $parameters = substr($parameters, 0, -1);
	  $pairs = explode('&', $parameters);
	  while (list(, $pair) = each($pairs)) {
		list($key,$value) = explode('=', $pair);
		$display_links .= tep_draw_hidden_field(rawurldecode($key), rawurldecode($value));
	  }
	}

	if (SID) $display_links .= tep_draw_hidden_field(tep_session_name(), tep_session_id());

	$display_links .= '</form>';
  } else {
	$display_links = sprintf(TEXT_RESULT_PAGE, $num_pages, $num_pages);
  }

  return $display_links;
}

function display_count($query_numrows, $max_rows_per_page, $current_page_number, $text_output) {
  $to_num = ($max_rows_per_page * $current_page_number);
  if ($to_num > $query_numrows) $to_num = $query_numrows;
  $from_num = ($max_rows_per_page * ($current_page_number - 1));
  if ($to_num == 0) {
	$from_num = 0;
  } else {
	$from_num++;
  }

  return sprintf($text_output, $from_num, $to_num, $query_numrows);
}
 }
?>

 

All I did is change the one line.

 

Scott

[Jack_mcs]

This line

$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

should be

$sql_query .= " limit " . max($offset, 0) . ", " . $max_rows_per_page;

 

Jack

[partsace]

This line

$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

should be

$sql_query .= " limit " . max($offset, 0) . ", " . $max_rows_per_page;

 

Jack

 

NOTE TO EVERYONE WITH THIS PROBLEM!!!

 

Jack has the correct fix!

 

Do not use

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

This code will fix your 1064 error, but not fix the next page issue. Jack's code will fix both.

 

Thanks Jack!

 

Scott

[Jack_mcs]

To be clear, it is not my code. It is a fix from the oscommerce update, which contains other fixes that should also be applied.

 

Jack

[partsace]

Sorry Jack!

 

But now....

 

I have a new problem. When I go to the catalog section, I get this error when clicking on a catagory that has less than 20 items (or no 2nd page).

 

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 'select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p' at line 1

 

select count(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, products_to_categories p2c left join specials s on p.products_id = s.products_id 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 = \'35\' order by pd.products_name) as total

 

Do you know of a fix for this?

 

Scott

[Jack_mcs]

Is this from standard oscommerce code or a contribution?

 

Jack

[Velveeta]

Sorry Jack!

 

But now....

 

I have a new problem. When I go to the catalog section, I get this error when clicking on a catagory that has less than 20 items (or no 2nd page).

 

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 'select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p' at line 1

 

select count(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, products_to_categories p2c left join specials s on p.products_id = s.products_id 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 = \'35\' order by pd.products_name) as total

 

Do you know of a fix for this?

 

Scott

This doesn't look like anything I've ever seen in a standard install, but you can probably fix it like so:

 

select count(*) as total from (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, products_to_categories p2c left join specials s on p.products_id = s.products_id 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 = \'35\' order by pd.products_name) as newtbl

 

So you need to take the "as total" off the end, move it next to count(*), add "from", and at the end, make it "as newtbl"

 

Richard.