1064 - You have an error in your SQL syntax

[sagitario 2]

Hey

 

I have a problem with my catalog. When a client try to see his account history he they receive the follow error.

 

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

select o.orders_id, o.date_purchased, o.delivery_name, o.billing_name, ot.text as order_total, s.orders_status_name from orders o, orders_total ot, orders_status s where o.customers_id = '4' and o.orders_id = ot.orders_id and ot.class = 'ot_total' and o.orders_status = s.orders_status_id and s.language_id = '2' order by orders_id DESC limit -0, 0

[TEP STOP]

 

I try to edit /includes/classes/split_page_results.php and change:

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

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

 

To this:

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

if ($offset < 0) { $offset = 0 ; }

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

 

The error problem is resolve but it cause other like de link page became some like this -> -4, -3, -2, -1, 0

And the in new produtos in first page became bad organized.

 

I have already made that update, but because i have made some change in my catalog design, i only update the following files:

 

catalog/admin/includes/classes/email.php

catalog/admin/includes/classes/split_page_results.php

catalog/admin/includes/classes/upload.php

catalog/admin/includes/functions/database.php

catalog/admin/includes/functions/general.php

catalog/admin/includes/functions/html_output.php

catalog/admin/categories.php

catalog/admin/file_manager.php

catalog/admin/orders.php

 

catalog/includes/classes/email.php

catalog/includes/classes/shopping_cart.php

catalog/includes/classes/split_page_results.php

catalog/includes/functions/database.php

catalog/includes/functions/general.php

catalog/includes/functions/html_output.php

catalog/includes/functions/sessions.php

catalog/address_book_process.php (051113)

catalog/advanced_search_result.php

catalog/index.php

catalog/redirect.php

 

And the error continues...

 

Someone can help me?

 

Thanks

[LynnL 0]

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

 

Did you figure out a solution to this? I am having the same error.

[jeffers 0]

I had a similar problem recently but my error was 1054

 

The cause of it after a bit of searching around seemed to be the newer versions of Mysql and PHP.

My live shop server uses Mysql 4 and PHP 4 whereas my offline test server uses PHP 4 and mysql 5

 

So as more and more servers upgrade i think this problem will increase for many users.

 

Solution i used a contribution to replace some files which has done the trick for me and possibly do this for you.

 

http://www.oscommerce.com/community/contributions,3727

 

 

JCProgrammes

[xXDesertRoseXx 0]

I was also havin the same error and I added a line to

 

admin/includes/classes/split_page_results.php

 

just look for the added by me comment. This works for sure :)

 

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

}

// XXX Added by Me **************************************************

if($current_page_number == 0) { $current_page_number = 1; }

// XXX ************************************************************

$offset = ($max_rows_per_page * ($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);

}

}

?>

 

if any one else has the same error and uses my fix please post it here wud be nice to know if it's helped someone else :)

[zpeedfreak 0]

Thank you Rose you helped me with my issue:

http://forums.oscommerce.com/index.php?showtopic=183802

[xXDesertRoseXx 0]

Ur welcome :)

[greyhunter 0]

Hi,

 

I encounter these problems too.

I looked at the contribution with all the replacement files but that doesn;t look like a pleasant solution since a complete reinstall is needed (based on the txt-file included in the package)

 

Is there another way to solve these issues once a shop is already running?

 

Gr,

Marcel

[xXDesertRoseXx 0]

Not sure hey :) i got mine working and its looking good so i dont want to fiddle around too much I may try introducing some or other module or contribution but that i'll do with a nother fresh installation and not with my curent shop.

[greyhunter 0]

Hi Rose,

 

Can you please explain how you solved the "1064-issue"?

 

I like to solve this as well since there are to many areas in my shop that result in this error now.

 

Gr,

Marcel

[Skypadelic 0]

DeserRose's solution worked for me too.

[n2advnture 0]

I am having the same error and tried adding the code but I still get this error:

 

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

select o.orders_id, o.date_purchased, o.delivery_name, o.billing_name, ot.text as order_total, s.orders_status_name from orders o, orders_total ot, orders_status s where o.customers_id = '152' and o.orders_id = ot.orders_id and ot.class = 'ot_total' and o.orders_status = s.orders_status_id and s.language_id = '1' order by orders_id DESC limit -10, 10

[TEP STOP]

 

ANY help would be greatly appreicated!

 

Thanks,

Mark

[bw92116 0]

Another possible solution to some of these problems is to check your values in the "Maximum Values" area of the Admin - Configuration panel.

 

I was getting a "divide by zero" error on the account_history page, and I checked the Configurations settings, and the Order History was set to 0. I changed it to 10, and that got rid of that error.

 

So if you are experiencing any of these errors, particularly a "divide by zero" error, try going to Admin > Configuration > Maximum values, and see if the value associated with the trouble you are having is set to 0. Try setting it to something else and see if that clears up the problem.

[sog001 0]

I found a solution!

 

In catalog/shopping_cart.php

 

In the lines containing:

 

$attributes = tep_db_query("select popt.products_options_name, poval.products_options_values_name, pa.options_values_price, pa.price_prefix

from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_OPTIONS_VALUES . " poval, " . // Patch osc-060817TABLE_PRODUCTS_ATTRIBUTES . " pa

where pa.products_id = '" . (int)$products[$i]['id'] . "'

and pa.options_id = '" . (int)$option . "'

and pa.options_id = popt.products_options_id

and pa.options_values_id = '" . (int)$value . "'// Patch osc-060817

and pa.options_values_id = poval.products_options_values_id// Patch osc-060817

and popt.language_id = '" . (int)$languages_id . "'

and poval.language_id = '" . (int)$languages_id . "'"); // Patch osc-060817

 

REMOVE the //patch osc-060817

This is a Syntax error and makes the SQL statement corrupt!

 

Works fine!

[kcaile 0]

I was also havin the same error and I added a line to

 

admin/includes/classes/split_page_results.php

 

just look for the added by me comment. This works for sure :)

 

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

}

// XXX Added by Me **************************************************

if($current_page_number == 0) { $current_page_number = 1; }

// XXX ************************************************************

$offset = ($max_rows_per_page * ($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);

}

}

?>

 

if any one else has the same error and uses my fix please post it here wud be nice to know if it's helped someone else :)

 

Thank you so much, I had the same problem and your solution has fixed it. Was panicking for a bit.

 

Karen

[bobsi18 2]

Thank you so much, I had the same problem and your solution has fixed it. Was panicking for a bit.

 

Karen

 

Thanks from me too :)