sagitario Posted December 2, 2005 Share Posted December 2, 2005 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 Link to comment Share on other sites More sharing options...
LynnL Posted December 5, 2005 Share Posted December 5, 2005 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. Link to comment Share on other sites More sharing options...
jeffers Posted December 5, 2005 Share Posted December 5, 2005 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 Link to comment Share on other sites More sharing options...
xXDesertRoseXx Posted December 5, 2005 Share Posted December 5, 2005 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 :) Rose The strong survive BUT the best succeed !! 1064 - You have an error in your SQL syntax Link to comment Share on other sites More sharing options...
zpeedfreak Posted December 6, 2005 Share Posted December 6, 2005 Thank you Rose you helped me with my issue: http://www.oscommerce.com/forums/index.php?showtopic=183802 Link to comment Share on other sites More sharing options...
xXDesertRoseXx Posted December 6, 2005 Share Posted December 6, 2005 Ur welcome :) Rose The strong survive BUT the best succeed !! 1064 - You have an error in your SQL syntax Link to comment Share on other sites More sharing options...
greyhunter Posted December 7, 2005 Share Posted December 7, 2005 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 Link to comment Share on other sites More sharing options...
xXDesertRoseXx Posted December 8, 2005 Share Posted December 8, 2005 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. Rose The strong survive BUT the best succeed !! 1064 - You have an error in your SQL syntax Link to comment Share on other sites More sharing options...
greyhunter Posted December 8, 2005 Share Posted December 8, 2005 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 Link to comment Share on other sites More sharing options...
Guest Posted February 21, 2006 Share Posted February 21, 2006 DeserRose's solution worked for me too. Link to comment Share on other sites More sharing options...
n2advnture Posted March 17, 2006 Share Posted March 17, 2006 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 Link to comment Share on other sites More sharing options...
bw92116 Posted January 26, 2008 Share Posted January 26, 2008 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. Link to comment Share on other sites More sharing options...
sog001 Posted January 29, 2008 Share Posted January 29, 2008 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! Link to comment Share on other sites More sharing options...
kcaile Posted February 6, 2008 Share Posted February 6, 2008 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 Link to comment Share on other sites More sharing options...
bobsi18 Posted December 27, 2008 Share Posted December 27, 2008 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 :) Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.