355 replies to this topic

[hommer]

Thanks for the great tips. My site is up and running again. However, I used this trick to fix the account_history page but if you look at the page number section, it has -4,-3,-2,-1,0. How do I get rid of this? Please see the screen capture for clearer description of the problem. I am eagerly waiting for your reply. Thank you

[img]http://img127.imageshack.us/img127/1131/picture2cs4.gif[/img]


split_page_results.php file:

<?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 (c) 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 . ' "><u>' . PREVNEXT_BUTTON_PREV . '</u></a>&nbsp;&nbsp;';

// 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 .= '&nbsp;<b>' . $jump_to_page . '</b>&nbsp;';
		} else {
		  $display_links_string .= '&nbsp;<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) . ' "><u>' . $jump_to_page . '</u></a>&nbsp;';
		}
	  }

// 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>&nbsp;';

// next button
	  if (($this->current_page_number < $this->number_of_pages) && ($this->number_of_pages != 1)) $display_links_string .= '&nbsp;<a href="' . tep_href_link(basename($PHP_SELF), $parameters . 'page=' . ($this->current_page_number + 1), $request_type) . '" class="pageResults" title=" ' . PREVNEXT_TITLE_NEXT_PAGE . ' "><u>' . PREVNEXT_BUTTON_NEXT . '</u></a>&nbsp;';

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

[DannyS]

Charles

Thanks for posting it as a tip. I just found it via a Google search and almost 3 years on it is still saving hair-pulling

Best wishes for 2008
Danny

[jagblade]

Hello ive spend the last few hours trying all the fixes shown above but most of them are alredy applyed and i still cant figure out where is the problem the error im getting is

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

select p.products_image, pd.products_name, p.products_weight, 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 = '4' and p2c.categories_id = '61' order by limit 20, 20

any ideas?

[jody00]

anyone know how to fix it in the catalog side www.richardgoodallgallery.com/rggstore/catalog_products_with_images.php

Am using php 4.1.x have latest correct split_page_results in both admin and catalog. Admin side works fine, rest of site works fine. any ideas how to fix it on this side?

jagblade, on Jan 7 2008, 09:06 AM, said:

Hello ive spend the last few hours trying all the fixes shown above but most of them are alredy applyed and i still cant figure out where is the problem the error im getting is

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

select p.products_image, pd.products_name, p.products_weight, 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 = '4' and p2c.categories_id = '61' order by limit 20, 20

any ideas?

[bw92116]

Some of these errors (especially if the error message says "division by zero") are caused by having the Maximum Values settings in the Configuration panel set to zero. In my case, I was getting an error message on the customer's Account History page, and I checked the Maximum Values, and sure enough, the maximum value for Order History was set to "0" - this was resulting in a "division by zero" error on the account_history page. I set that to 10 and that got rid of the error. So one way to try to eliminate these errors, if it involves a specific box or a specifc page, is to check the Maximum Value related to it, and if that is set to 0, change it to something else. This might help eliminate the error, before you try editing code.

[ponury]

how can I fix it:

1054 - Unknown column 'pd.products_shortdescription' in 'field list'

select p.products_id, pd.products_name, pd.products_description, pd.products_shortdescription, p.products_image, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from products p left join specials s on p.products_id = s.products_id left join products_description pd on p.products_id = pd.products_id and pd.language_id = '4' left join featured f on p.products_id = f.products_id where p.products_status = '1' and f.status = '1' order by rand() DESC limit 2

[TEP STOP]


??

sql server 4.1.xx

[geoffreywalton]

First I would head over to phpmyadmin and see if the field does exist.

Have you installed a contribution and not followed the instruction to run some sql that adds this field?

[antkel23]

your a legend... thanx heaps for that!! works a treat.

oh i should note that this worked for me when i went to newsletters in admin i got the problem... used the original post fix and it worked for that area for me... so if anyone is having a newsletter problem... this worked for me.

Edited by antkel23, 28 January 2008, 02:19.

[wayoutofourleague]

Hi! thanks so much for this info. I received this message today after doing an upgrade. Your instructions seemed so easy to me until I read the bottom line and now I am confused. Do I do this in cpanel, admin, ??? How can I edit the text inside the actual document. Sorry I am so very new at this and started with the bare basic computer skills, we are learning hence the screne name. Any help would be so much appreciated. Just speak to me like your teaching a child to walk one foot in front of the other and I promise I will get it.

[wayoutofourleague]

Ok, baby steps....I went to cpaneland then to public html and then to admin and so forth and changed the info by backspacing what was in there and putting what you said to, but I sill get this error:

[ HEADING_TITLE

TABLE_HEADING_PRODUCTS TABLE_HEADING_RATING TABLE_HEADING_DATE_ADDED TABLE_HEADING_ACTION
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 reviews_id, products_id, date_added, last_modified, reviews_rating from r' at line 1

select count(select reviews_id, products_id, date_added, last_modified, reviews_rating from reviews order by date_added DESC) as total

TEP_DB_ERRORR

Is there another place I should have done this? Please help! Im so confused!

[OldBuickParts]

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

select distinct p.products_id, pd.products_name from products p, products_description pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '1' order by p.products_ordered desc, pd.products_name limit

[TEP STOP]

I am getting the this error on the catalog side and it occurs at the top of the right column and is confined to the right column. Never had any errors on the admin side. Am running MS2.2 RC2 with no mods. It was working but now is broken. Have looked at the suggestions but they have already been fixed in this release with (offset,0) in split_page...

I can't figure this out.

Has anyone else seen this error?

Help!

Thanks to all of you who help us. We appreciate it greatly.

Jeff

[carromex]

Mandarin Mark, on May 22 2005, 08:18 PM, said:

My error message is slightly different and reads:

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

delete from products_options where products_id =206'

[TEP STOP]

I tried your fix to no avail. I really need to get this ironed. Please advise. It only happens when I try to delete a product. The product gets deleted, but the error still pops up every time.

I put in contribution : froogle_bizrate_yahoo_feeds_1_12

My error is : SQL query:
INSERT INTO `configuration_group`
VALUES ( 62, 'Feed Settings', 'Info for data feeds', 62, 1 ) ;
MySQL said:
#1062 - Duplicate entry '62' for key 1

What do I do ?

Please help. Most appreciated.

[carromex]

Mandarin Mark, on May 22 2005, 08:18 PM, said:

My error message is slightly different and reads:

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

delete from products_options where products_id =206'

[TEP STOP]

I tried your fix to no avail. I really need to get this ironed. Please advise. It only happens when I try to delete a product. The product gets deleted, but the error still pops up every time.

I put in contribution : froogle_bizrate_yahoo_feeds_1_12

My error is : SQL query:
INSERT INTO `configuration_group`
VALUES ( 62, 'Feed Settings', 'Info for data feeds', 62, 1 ) ;
MySQL said:
#1062 - Duplicate entry '62' for key 1

What do I do ?

Please help. Most appreciated.

[SambaMambo]

Dear everyone

i have a little problem similar of this one
but i'm getting errors when i try to install Total B2B contrib.
it's not on the admin or catalog but when i tryed to upload the .sql files to my sql database

#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 configuration_group_id FROM configuration_group WHERE co 

i already apply the fix for split_page_result but nothing happen (of course) ....


Hoster : 1and1
Mysql : 4.0

if someone have similar problem and have a fix...
thx for all

[SambaMambo]

SambaMambo, on Mar 4 2008, 12:17 PM, said:

Dear everyone

i have a little problem similar of this one
but i'm getting errors when i try to install Total B2B contrib.
it's not on the admin or catalog but when i tryed to upload the .sql files to my sql database

#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 configuration_group_id FROM configuration_group WHERE co 

i already apply the fix for split_page_result but nothing happen (of course) ....
Hoster : 1and1
Mysql : 4.0

if someone have similar problem and have a fix...
thx for all

ok i fixed it !
for those who have the same problem :
just download the first release of Total B2B
and upload the sql file of this package
The fact is that the new package is for Mysql5.xxx++++
so ...
f**k 1and1 ...
and thx SQL

Edited by Jan Zonjee, 28 March 2010, 10:46.

[dodie]

Simplyeasier, on Apr 1 2005, 03:10 AM, said:

Hi

First off - this fix is actually already recorded in the contributions section and many thanks to those who recorded it - but I don't think it is a contribution - rather a tip - Secondly the same problem has arisen for 4 people in a few days and I dare say it will happen to more as hosts migrate their mySQL databases to version 4.1.xx -

Hence I post here

If you get an error whilst creating \ modifying your database such that thro admin you delete all entries on a particular table you will likely get this message

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

followed by something like

select manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified from manufacturers order by manufacturers_name limit -20, 20

or

select banners_id, banners_title, banners_image, banners_group, status, expires_date, expires_impressions, date_status_change, date_scheduled, date_added from banners order by banners_title, banners_group limit -20, 20

depending on which table you have deleted all records from.

To fix this

In admin/includes/classes find split_page_results.php and - BACK IT UP then find the lines

$offset = ($max_rows_per_page * ($current_page_number - 1));
$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

change to

$offset = ($max_rows_per_page * ($current_page_number - 1));
if ($offset < 0)
{
$offset = 0 ;
}
$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;
MySQL 4.1.xx handles negatives correctly (by forcing an error) in the code above unlike earlier versions of MySQL.

NOTE - I have NOT asked you to change catalog/includes/classes/split_page_results.php - If you have a problem on the catalog side you may want to try the same change in that file - I did and it went wrong so the change to my catalog/includes/classes/split_page_results.php file is a little more elaborate and I will share that here if it becomes an issue - but for now it seems people will mostly have a problem with empty files on the admin side.

hth

Charles

I am using sql 5. I unfortunately deleted all of my orders using the admin interface and got 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 '-20, 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 o.orders_id DESC limit -20, 20

I tried the fix modification above and the error message went away and the orders link returned 0 of 0 orders as expected. However, after placing test orders and clicking on the orders link it still says 0 of 0 orders. I can access the test orders by typing the order ID number into the box and I can see the orders in the database table.

Could it be that this fix modification doesn't work with sql5?

[lfan]

Hi I am new here. I am getting errors on Catalog when trying to add Specials.

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

select p.products_id, pd.products_name, p.products_price, s.specials_id, s.specials_new_products_price, s.specials_date_added, s.specials_last_modified, s.expires_date, s.date_status_change, s.status from products p, specials s, products_description pd where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = s.products_id order by pd.products_name limit -30, 30


I have no idea what to do Anyone can help me??
Thanx

[CataM]

I have this error.


An Error Was Encountered
Error Number: 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 classified_id ASC' at line 1

SELECT * FROM classifieds WHERE vehicle_make_id=12 AND vehicle_model LIKE '%%' AND zip IN () ORDER BY classified_id ASC


Can someone help me please?

[yevgeniy]

Hi,

I have a similar problem with 1064 Error on the Admin / edit order. I applied Moneris package and I believe located the source of the query in admin/orders_addon_campg.php. I tried to apply suggested fix from previous postings, but it did not work. Following is an error itself:
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 moneris_order_id, gateway_url, osc_session, ref_num, f4l

select baseTable.moneris_order_id, baseTable.gateway_url, baseTable.osc_session, baseTable.ref_num, baseTable.f4l4, baseTable.resp_code, baseTable.iso_code, baseTable.auth_code, baseTable.trans_date, baseTable.trans_time, baseTable.trans_type, baseTable.message, baseTable.card_type, baseTable.txn_num, baseTable.avs_code, baseTable.cvd_code, baseTable.crypt_type, baseTable.veres, baseTable.pares, txnTable.txn_num orig_txn_num from (select moneris_order_id, gateway_url, osc_session, ref_num, f4l4, iso_code, resp_code, auth_code, trans_date, trans_time, trans_type, message, card_type, txn_num, avs_code, cvd_code, crypt_type, veres, pares from moneris_can_orders where osc_order_id = '11') as baseTable cross join (select moneris_order_id, trans_type, txn_num from moneris_can_orders where trans_type='01' and osc_order_id = '11') as txnTable where (baseTable.trans_type = '01' and baseTable.moneris_order_id = txnTable.moneris_order_id) or (baseTable.trans_type <> txnTable.trans_type) and (baseTable.moneris_order_id = txnTable.moneris_order_id)

Please help!

[Blestg]

Hi, after Extra_images contribution installation...now I got this error in admin panel:

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 pei.products_extra_image, pei.products_extra_images_id, pei.products_id, ' line 1

select count(select pei.products_extra_image, pei.products_extra_images_id, pei.products_id, pd.products_name,p.products_image from products_extra_images pei left join products p ON pei.products_id = p.products_id left join products_description pd on p.products_id = pd.products_id order by pd.products_name) as total 1

[TEP STOP]

Any Help.
thanks