Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

1064 SQL Syntax Error


mugitty

Recommended Posts

I am encountering a syntax error in the 2 files mentioned above when trying to view Products Purchased or Customer Orders-Total Reports in the Admin area.

 

The error in stats_customers.php 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 '*) as total from customers c, orders_products op, orders o whe

 

select count(distinct *) as total from customers c, orders_products op, orders o where c.customers_id = o.customers_id and o.orders_id = op.orders_id

Note: There is no string that says "select count(distinct *)" in that file

 

The query area is lines 58-74

<?php
 if (isset($HTTP_GET_VARS['page']) && ($HTTP_GET_VARS['page'] > 1)) $rows = $HTTP_GET_VARS['page'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS;
 $customers_query_raw = "select c.customers_firstname, c.customers_lastname, sum(op.products_quantity * op.final_price) as ordersum from " . TABLE_CUSTOMERS . " c, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_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";
 $customers_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $customers_query_raw, $customers_query_numrows);
// fix counted customers
 $customers_query_numrows = tep_db_query("select customers_id from " . TABLE_ORDERS . " group by customers_id");
 $customers_query_numrows = tep_db_num_rows($customers_query_numrows);

 $rows = 0;
 $customers_query = tep_db_query($customers_query_raw);
 while ($customers = tep_db_fetch_array($customers_query)) {
$rows++;

if (strlen($rows) < 2) {
  $rows = '0' . $rows;
}
?>

 

The error in stats_products_purchased.php 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 '*) as total from products p, products_description pd where pd.

 

select count(distinct *) as total from products p, products_description pd where pd.products_id = p.products_id and pd.language_id = '1' and p.products_ordered > 0

Note: There is no string that says "select count(distinct *)" in that file

 

The query area is lines 55-68

<?php
 if (isset($HTTP_GET_VARS['page']) && ($HTTP_GET_VARS['page'] > 1)) $rows = $HTTP_GET_VARS['page'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS;
 $products_query_raw = "select p.products_id, p.products_ordered, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where pd.products_id = p.products_id and pd.language_id = '" . $languages_id. "' and p.products_ordered > 0 group by pd.products_id order by p.products_ordered DESC, pd.products_name";
 $products_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $products_query_raw, $products_query_numrows);

 $rows = 0;
 $products_query = tep_db_query($products_query_raw);
 while ($products = tep_db_fetch_array($products_query)) {
$rows++;

if (strlen($rows) < 2) {
  $rows = '0' . $rows;
}
?>

 

Both files are unaltered files from the 2.2ms2-060817 version of osC

Does anyone have any ideas what might be causing these errors?

... if you want to REALLY see something that doesn't set up right out of the box without some tweaking,

try being a Foster Parent!

Link to comment
Share on other sites

Have you installed any contributions?

 

Vger

Hi, Rhea...

 

Yes, I have a number of contributions already installed (but none of them directly made any changes to these 2 files) - see if any of the contribs listed below ring a bell as something that might cause these 2 files to behave differently...

Abstract Zones, Admin Comment Toolbar, Admin Horizontal Navigation, Big Images, Country-State selector, Easy Populate, Free Shipping per Product, Order Editor, Package Tracking with Email Mods, Popup Estimated Shipping, Product Availability, UPSXML + Packaging Data, US and Canada Geo Zones, USPS Methods, Visible Countries

 

Thanks :'(

... if you want to REALLY see something that doesn't set up right out of the box without some tweaking,

try being a Foster Parent!

Link to comment
Share on other sites

Any contribution which interacts with those files could cause the error - and you have a LOT of contribs installed!

 

The only hope of tracking down where the error is originating from is to download a complete set of folders/files to your computer and put them in a folder on your C drive. Then use the 'Find In Files' feature of Text Pad (free download) to search for "select count(distinct *)" - without the quotes - in that folder.

 

Vger

Link to comment
Share on other sites

Any contribution which interacts with those files could cause the error - and you have a LOT of contribs installed!

 

The only hope of tracking down where the error is originating from is to download a complete set of folders/files to your computer and put them in a folder on your C drive. Then use the 'Find In Files' feature of Text Pad (free download) to search for "select count(distinct *)" - without the quotes - in that folder.

 

Vger

This really has me baffled...

I just did a GREP on every file that is on the site and found NO instance of "select count(distinct *)"

There are a number of instances where select count(*) appears, both in osC native files and in contributions and there are a couple where select count(" . $count_string . ") appears.

 

I also noticed that the error in stats_customers.php says

select count(distinct *) as total from customers c, orders_products op, orders o where c.customers_id = o.customers_id and o.orders_id = op.orders_id
but the code reads as ordersum rather than as total

... if you want to REALLY see something that doesn't set up right out of the box without some tweaking,

try being a Foster Parent!

Link to comment
Share on other sites

Resolved the error (but not corrected)

The issue seems to have been with the contribution Abstract Zones

While I did not try to fix the issue (I just uninstalled the contribution and the SQL error is gone and the missing reports display now), I suspect the issue may be somewhere in the changes that were made to admin\includes\split_page_results.php as shown below (this is where I finally found code relating to the term "distinct"

ORIGINAL CODE

	function splitPageResults(&$current_page_number, $max_rows_per_page, &$sql_query, &$query_num_rows) {

REPLACED BY (from contribution)

//-MS- Calculate pages correctly on distinct
function splitPageResults(&$current_page_number, $max_rows_per_page, &$sql_query, &$query_num_rows, $count_key='*') {
//-MS- Calculate pages correctly on distinct EOM

and

ORIGINAL CODE

$reviews_count_query = tep_db_query("select count(*) as total " . substr($sql_query, $pos_from, ($pos_to - $pos_from)));

REPLACED BY (from contribution)

//-MS- Calculate pages correctly on distinct
  if (strpos($sql_query, 'distinct') || strpos($sql_query, 'group by')) {
	$count_string = 'distinct ' . tep_db_input($count_key);
  } else {
	$count_string = tep_db_input($count_key);;
  }

  $reviews_count_query = tep_db_query("select count(" . $count_string . ") as total " . substr($sql_query, $pos_from, ($pos_to - $pos_from)));
//-MS- Calculate pages correctly on distinct

If anyone decides to take the time to see if this problem that occurs with the install of this contribution can be resolved, please let us all know. :thumbsup:

... if you want to REALLY see something that doesn't set up right out of the box without some tweaking,

try being a Foster Parent!

Link to comment
Share on other sites

HEY HEY, I had this error.

 

It's due to your host updating their mysql verisons to 4.1 something

 

It's when you empty out the database of something and it cant interpret the number 0. (or something like that, im dumb. )

 

I used this tip and boom, no problems.

 

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

 

Let me know guys if this works!

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...