Admin/Customers/Orders 1064 SQL Syntax Error

[remixstore]

at shop/admin/orders.php I am getting this error... :(

 

 

Orders

 

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

 

select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by limit 0, 20

 

[TEP STOP]

 

Help, please...

 

i am kinda thinking this may have something to do w/ the Download Controller

[Guest]

Its the 1605, go here:

http://www.oscommerce.com/community/bugs,1605

 

there is a fix at the end of the page.

[remixstore]

Its the 1605, go here:

http://www.oscommerce.com/community/bugs,1605

 

there is a fix at the end of the page.

 

BAD ASS, MAN!!! THANK YOU. I'll give it a shot now

[remixstore]

dang, didn't work

 

I am using PHP Version 4.4.1 & MySQL 4.0.25-standard-log

 

 

 

29 Sep 2005 19:21:49	  hpdl (AT) oscommerce (DOT) com

Thanks for the report, here is the official fix:

Line 67 in catalog/includes/classes/split_page_results.php must be changed from:

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

to:

$this->sql_query .= " limit " . max($offset, 0) . ", " . $this->number_of_rows_per_page;

Line 38 in catalog/admin/includes/classes/split_page_results.php must be changed from:

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

to:

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

[remixstore]

http://www.oscommerce.com/community?bugs=&...h=limit+0%2C+20

[remixstore]

http://www.oscommerce.com/community/bugs,2...h,limit+0%2C+20

[remixstore]

My current modified code:

	  $num_pages = ceil($query_num_rows / $max_rows_per_page);


// BEGIN Modified 2005-09-21
if ($num_pages == 0) {
$num_pages=1;
}
// END Modified 2005-09-21

  if ($current_page_number > $num_pages) {
   $current_page_number = $num_pages;
  }
  $offset = ($max_rows_per_page * ($current_page_number - 1));
  $sql_query .= " limit " . $offset . ", " . $max_rows_per_page;
  }

 

gives me this error:

Parse error: parse error, unexpected ';', expecting T_OLD_FUNCTION or T_FUNCTION or T_VAR or '}' in /homepages/36/d106168665/htdocs/mastershot/shop/admin/includes/classes/split_page_results.php on line 111

 

Line 111 is

?>

 

bollox...

 

updated

 

My current modified code for admin/includes/classes/split_page_results.php:

	  $num_pages = ceil($query_num_rows / $max_rows_per_page);


// BEGIN Modified 2005-09-21
if ($num_pages == 0) {
$num_pages=1;
}
// END Modified 2005-09-21

  if ($current_page_number > $num_pages) {
   $current_page_number = $num_pages;
  }
  $offset = ($max_rows_per_page * ($current_page_number - 1));
  $sql_query .= " limit " . $offset . ", " . $max_rows_per_page;
  }

 

 

still gives me this:

 

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

 

select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by limit 0, 20

 

[TEP STOP]

 

my code @ includes/classes/split_page_results.php:

 

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

[Jan Zonjee]

Actually, the error is caused by order missing fieldname by limit and I remember it being the fault of a certain contribution you probably added. I can't remember the name of it, but you might :)

[remixstore]

Actually, the error is caused by order missing fieldname by limit and I remember it being the fault of a certain contribution you probably added. I can't remember the name of it, but you might :)

 

I have added the Downloads Controller, Easy Populate and Category Fields... that's about it

 

 

http://mastershot.tv/shop/index.php

 

Currently I have no error on the user side but I can't release a download or whatever since I can't get to Customer/ Totals :(

[remixstore]

Actually, the error is caused by order missing fieldname by limit and I remember it being the fault of a certain contribution you probably added. I can't remember the name of it, but you might :)

 

 

i've been tearing thru this all day and can't figure it out... any further insight/ help is greatly appreciated. :huh:

[Guest]

can you post the entire split_page_results.php file you modified with the official fix? The one you mentioned didnt work.

 

catalog\admin\includes\classes\split_page_results.php

 

just in case there other mods there.

[Jan Zonjee]

i've been tearing thru this all day and can't figure it out... any further insight/ help is greatly appreciated.

Seems to be download controller indeed. Perhaps this post or this shorter fix in a post will help you out.

[remixstore]

this is my current code for that page. i'll check those other 2 links. thanks!

 

 

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

 

 

// BEGIN Modified 2005-09-21

if ($num_pages == 0) {

$num_pages=1;

}

// END Modified 2005-09-21

 

if ($current_page_number > $num_pages) {

$current_page_number = $num_pages;

}

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

}

}

?>

 

[code]

[remixstore]

ok, so this thing is almost working...

 

i got rid of the sql error but now the top nav header part of the admin orders.php isn't working

 

[remixstore]

so close...

 

just the thing above now and I am getting an error in categories.php on the user side...

 

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]

[remixstore]

dang thing...

[remixstore]

fixed. it was my index.php

 

thanks JanZ for the heads up