Error 1064 MySQL

[justdlnow]

$manufacturers_query_raw = "select manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified from " . TABLE_MANUFACTURERS . " order by manufacturers_name";
 $manufacturers_split = new splitPageResults($_GET['page'], MAX_DISPLAY_SEARCH_RESULTS_ADMIN, $manufacturers_query_raw, $manufacturers_query_numrows);
 $manufacturers_query = tep_db_query($manufacturers_query_raw);
 while($manufacturers = tep_db_fetch_array($manufacturers_query)) {
   if((!isset($_GET['mID']) || (isset($_GET['mID']) && ($_GET['mID'] == $manufacturers['manufacturers_id']))) && !isset($mInfo) && (substr($action, 0, 3) != 'new')) {
  $manufacturer_products_query = tep_db_query("select count(*) as products_count from " . TABLE_PRODUCTS . " where manufacturers_id = '" . (int)$manufacturers['manufacturers_id'] . "'");
  $manufacturer_products = tep_db_fetch_array($manufacturer_products_query);
  $mInfo_array = array_merge($manufacturers, $manufacturer_products);
  $mInfo = new objectInfo($mInfo_array);
   }
   if (isset($mInfo) && is_object($mInfo) && ($manufacturers['manufacturers_id'] == $mInfo->manufacturers_id)) {
  echo '			  <tr id="defaultSelected" class="dataTableRowSelected" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_MANUFACTURERS, 'page=' . $_GET['page'] . '&mID=' . $manufacturers['manufacturers_id'] . '&action=edit') . '\'">' . "\n";
   } else {
  echo '			  <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_MANUFACTURERS, 'page=' . $_GET['page'] . '&mID=' . $manufacturers['manufacturers_id']) . '\'">' . "\n";
   }
?>
		    <td class="dataTableContent"><?php echo $manufacturers['manufacturers_name']; ?></td>
		    <td class="dataTableContent" align="right"><?php if (isset($mInfo) && is_object($mInfo) && ($manufacturers['manufacturers_id'] == $mInfo->manufacturers_id)) { echo tep_image(DIR_WS_IMAGES . 'icon_arrow_right.gif'); } else { echo '<a href="' . tep_href_link(FILENAME_MANUFACTURERS, 'page=' . $_GET['page'] . '&mID=' . $manufacturers['manufacturers_id']) . '">' . tep_image(DIR_WS_IMAGES . 'icon_info.gif', IMAGE_ICON_INFO) . '</a>'; } ?> </td>
		  </tr>
<?php
 }

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 manufacturers_id, manufacturers_name, manufacturers_image, date_added, la' at line 1

 

select count(select manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified from manufacturers order by manufacturers_name) as total

 

The PHP code:

[tgely]

@@justdlnow

 

use this:

select count(*) as total from manufacturers

[justdlnow]

@@Gergely I wrote that in place of

select count(*) as products_count from " . TABLE_PRODUCTS . "

and is the same

[justdlnow]

I beg anyone who knows fix this error , please help

[MrPhil]

I don't see how you're going to get that SQL query from that PHP code. Are you sure you're looking in the right place? What add-ons do you have? Is this custom coding? I would think that you'd only have to SELECT one field or * if all you want is a count of selected records:

select count(*) as total from manufacturers

(as @@Gergely said) or something like that. What are you trying to do and where did this query come from? Is your MySQL configured to permit subselections? Even if it is, you're going the long way around the garden to get the "total", which would just be the number of records in the table.

[multimixer]

Could it be that you modified file admin/includes/classes/split_page_results.php at some point? Or that you uploaded the catalog file to admin?

 

Parameters have a different order in each file

 

In admin you have

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

 

In catalog you have

   function splitPageResults($query, $max_rows, $count_key = '*', $page_holder = 'page') {

 

If you uploaded the catalog file to admin, you are feeding the function with the complete query, where it expect the $count_key, so it creates this subquery

[svos]

Hi everyone, I have been having some trouble with this error message when I'm trying to reprogram the tax zones for Canadian provinces. In Canada, I have to charge each province who pays a Harmonized Sales Tax (HST), their specific percentage of HST, while those that pay separate provincial and federal tax need to be charged only the federal portion. That means, I have to have all the different rates set up according to provinces and I found a guide I am trying to follow that says how to do it. Only problem is, when I get to the point where I have to make the new HST tax zone, and then click on it to make sub zones, I get this error message:

Tax Zones

 

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 a.association_id, a.zone_country_id, c.countries_name, a.zone_id, a.geo_zone_id, a.last_modified, a.date_added, z.zone_name from zones_to_geo_zones a left join countries c on a.zone_country_id = c.countries_id left join zones z on a.zone_id = z.zone_id where a.geo_zone_id = 24 order by association_id limit -20, 20

[TEP STOP]

 

I am just wondering why this is occurring? I am doing exactly as the guide says and I can't get past this message. I need to figure out the tax things soon because when my customers order, they are always getting charged the wrong rates on the order forms. Anyone have any ideas on this? Thanks In Advance!!

[svos]

Oops, forgot to say that I am using OS Commerce 2.2 - MS2. I know its old!

[Dan Cole]

@@svos

 

I am just wondering why this is occurring? I am doing exactly as the guide says and I can't get past this message. I need to figure out the tax things soon because when my customers order, they are always getting charged the wrong rates on the order forms. Anyone have any ideas on this? Thanks In Advance!!

 

I had a similar problem a few weeks ago and it had to do with the splitPageResults function that was used a little further along on the page...I can't recall what I did to resolve it off the top of my head but.if that function is being called a few lines further along, comment it out for a second to see if that is indeed the problem....your query might well be okay.

 

Dan

[svos]

Thanks Dan, I appreciate the response. I am really bad with all this code stuff so I think I will ask the lady that usually works on it for me. I only attempt the really easy stuff and in this case, I felt I could handle it but apparently it's not as straight forward as it appears. Thank you again. I will tell her what you said and hopefully she will get to the bottom of it! - Sylvia

[Jan Zonjee]

Indeed it is probably this change in includes/functions/split_page_results.php around line 67 that would deal with the "-20" that MySQL stopped tolerating (it can't be negative anymore):

 

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

 

I think it should be changed in both the catalog part and the admin part. Probably not the last error you will encounter I'm afraid...

[rpdesign]

@@svos

 

MySQL doesn't like negative numbers in the limit field so change -20,20 to 0,20 or 10,20 this says limit the search results beginning at record 0 and ending at with 20 results or begin at record 10 and limit to 20 results or put more simply limit [begining record, resulting rows] Hope this answers your question :D

 

 

so MySQL errors out because there can not be a negative record :- your database always starts out with a positive number for indexing the records (w00t)

 

so this sql query wants to limit the results to 20 rows at a time you can also change this value to 0,10 for ten results 0,50 for fifty results etc.... starting at record 1

 

When you do this you will notice that the resulting record set will only contain 10 or 20 or 50 results

[rpdesign]

@@svos

 

I think that this website describes it better.

http://php.about.com/od/mysqlcommands/g/Limit_sql.htm

[♥mommaroodles]

You could try this too - it worked for me those years back

 

in your split_page_results.php page

 

look for this code:

 

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

 

and just above that add the following so it looks like this

 

// add this
if($current_page_number == 0) { $current_page_number = 1; }
// end of
$offset = ($max_rows_per_page * ($current_page_number - 1));
$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;
}

[burt]

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

 

Yes.

 

// add this
if($current_page_number == 0) { $current_page_number = 1; }
// end of
$offset = ($max_rows_per_page * ($current_page_number - 1));
$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;
}

 

No.

 

Using max(X, Y) selects the maximum number of the two.

[MrPhil]

What's the lowest valid $current_page_number? If it's 0, the max($offset, 0) fix is correct. If it's 1, either fix will work (although it would be good to determine just how $current_page_number got to be less than 1).

[tgely]

I am just wondering why this is occurring?

 

The TAX addon is not well coded. :)

[♥mommaroodles]

You could try this too - it worked for me those years back

 

in your split_page_results.php page

 

look for this code:

 

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

 

and just above that add the following so it looks like this

 

// add this
if($current_page_number == 0) { $current_page_number = 1; }
// end of
$offset = ($max_rows_per_page * ($current_page_number - 1));
$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;
}

 

My post above is indeed incorrect - i've just been going through all my old notes again and the above code related to the "Cannot re-assign $this" bug reported.

 

limit -20, 20

 

This limit -20, 20 which was documented here http://www.oscommerce.com/community/bugs,1605 gives the following instructions.

 

Problem:

 

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

 

 

Solution:

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;

 

In fact these corrections are included in the download of oscommerce-2.2ms2-060817