Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

1064 - You have an error in your SQL syntax


Simplyeasier

Recommended Posts

When I try and go to my special items page all it shows me is:

 

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

 

select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from products p, products_description pd, specials s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and s.status = '1' order by s.specials_date_added DESC limit -9, 9

 

Can anyone help?

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

fix at the end of page

Link to comment
Share on other sites

  • Replies 354
  • Created
  • Last Reply

Just after completed install of Admin access 2.2 a with patches, can not access admin due to SQL error as shown below, browser also points to http://www.domain.com/admin/categories.php I see no problem in browsing catalog.

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 admin_groups_id, admin_cat_access, admin_right_access from TABLE_ADMIN where admin_id=

[TEP STOP]

line 20 of /admin/categories.php shows:

$admin_access_query = tep_db_query("select admin_groups_id, admin_cat_access, admin_right_access from " . TABLE_ADMIN . " where admin_id=" . $login_id);

 

Admin acess 2.2a with patches installed are those for http://www.oscommerce.com/community/contributions,1359 consisting of:

1)Admin Access 2.2a Chinchilla2 24 Jan 2006

2)Categorie Admin (Extension for Administration Access Level Account) with Patches oliswiss 3 Mar 2006

3)Now oliswiss' last Extensions should work schittli 2 Jul 2006

 

webserver is running PHP 4.4.2 and MySql 4.1.19-standard. Based on what I read here and http://www.oscommerce.com/community/bugs,1605/search,1605 , I located /admin/includes/classes/split_page_results.php and land changed starting line 37 the following two lines:

from

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

to:

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

This change had no effect, still getting 1064 error as shown above. Please advise

Link to comment
Share on other sites

  • 2 weeks later...
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;

 

:lol: :D You are awesome. I am a noob to OSC, and you made this fix so easy! Thanks!

Link to comment
Share on other sites

  • 3 weeks later...

My host lost my websites this weekend and the move to a new host hasn't been too fun.

 

When I brought in an oscommerce database I had this error so changed both the file in administration and the file in catalog. It seemed better but now I have this error.

 

Fatal error: Call to undefined function: display_links() in /usr/local/psa/home/vhosts/capricornleather.com/httpdocs/admin/orders.php on line 494

 

I really have no php knowledge and am quite lost when doing these fixes. Your help would be great.

 

Below is the code for the orders.php. Not sure if you need this.

 

Darlene

 

<?php

/*

$Id: orders.php,v 1.112 2003/06/29 22:50:52 hpdl Exp $

 

osCommerce, Open Source E-Commerce Solutions

http://www.oscommerce.com

 

Copyright (c) 2003 osCommerce

 

Released under the GNU General Public License

 

Shoppe Enhancement Controller - Copyright (c) 2003 WebMakers.com

Linda McGrath - [email protected]

*/

 

require('includes/application_top.php');

 

require(DIR_WS_CLASSES . 'currencies.php');

$currencies = new currencies();

 

$orders_statuses = array();

$orders_status_array = array();

$orders_status_query = tep_db_query("select orders_status_id, orders_status_name from " . TABLE_ORDERS_STATUS . " where language_id = '" . (int)$languages_id . "'");

while ($orders_status = tep_db_fetch_array($orders_status_query)) {

$orders_statuses[] = array('id' => $orders_status['orders_status_id'],

'text' => $orders_status['orders_status_name']);

$orders_status_array[$orders_status['orders_status_id']] = $orders_status['orders_status_name'];

}

 

$action = (isset($HTTP_GET_VARS['action']) ? $HTTP_GET_VARS['action'] : '');

 

if (tep_not_null($action)) {

switch ($action) {

case 'update_order':

$oID = tep_db_prepare_input($HTTP_GET_VARS['oID']);

$status = tep_db_prepare_input($HTTP_POST_VARS['status']);

$comments = tep_db_prepare_input($HTTP_POST_VARS['comments']);

 

$order_updated = false;

$check_status_query = tep_db_query("select customers_name, customers_email_address, orders_status, date_purchased from " . TABLE_ORDERS . " where orders_id = '" . (int)$oID . "'");

$check_status = tep_db_fetch_array($check_status_query);

// BOF: WebMakers.com Added: Downloads Controller

// always update date and time on order_status

// MS2 if ( ($check_status['orders_status'] != $status) || tep_not_null($comments)) {

if ($check_status['orders_status'] != $status || tep_not_null($comments) != '' || ($status ==DOWNLOADS_ORDERS_STATUS_UPDATED_VALUE) ) {

tep_db_query("update " . TABLE_ORDERS . " set orders_status = '" . tep_db_input($status) . "', last_modified = now() where orders_id = '" . tep_db_input($oID) . "'");

$check_status_query2 = tep_db_query("select customers_name, customers_email_address, orders_status, date_purchased from " . TABLE_ORDERS . " where orders_id = '" . tep_db_input($oID) . "'");

$check_status2 = tep_db_fetch_array($check_status_query2);

if ( $check_status2['orders_status']==DOWNLOADS_ORDERS_STATUS_UPDATED_VALUE ) {

tep_db_query("update " . TABLE_ORDERS_PRODUCTS_DOWNLOAD . " set download_maxdays = '" . tep_get_configuration_key_value('DOWNLOAD_MAX_DAYS') . "', download_count = '" . tep_get_configuration_key_value('DOWNLOAD_MAX_COUNT') . "' where orders_id = '" . tep_db_input($oID) . "'");

}

// EOF: WebMakers.com Added: Downloads Controller

 

$customer_notified = '0';

if (isset($HTTP_POST_VARS['notify']) && ($HTTP_POST_VARS['notify'] == 'on')) {

$notify_comments = '';

if (isset($HTTP_POST_VARS['notify_comments']) && ($HTTP_POST_VARS['notify_comments'] == 'on')) {

// WebMakers.com Added: Do not include if empty

if (!empty($comments)) {

$notify_comments = sprintf(EMAIL_TEXT_COMMENTS_UPDATE, $comments) . "\n\n";

}

}

 

$email = STORE_NAME . "\n" . EMAIL_SEPARATOR . "\n" . EMAIL_TEXT_ORDER_NUMBER . ' ' . $oID . "\n" . EMAIL_TEXT_INVOICE_URL . ' ' . tep_catalog_href_link(FILENAME_CATALOG_ACCOUNT_HISTORY_INFO, 'order_id=' . $oID, 'SSL') . "\n" . EMAIL_TEXT_DATE_ORDERED . ' ' . tep_date_long($check_status['date_purchased']) . "\n\n" . $notify_comments . sprintf(EMAIL_TEXT_STATUS_UPDATE, $orders_status_array[$status]);

 

tep_mail($check_status['customers_name'], $check_status['customers_email_address'], EMAIL_TEXT_SUBJECT, $email, STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS);

 

$customer_notified = '1';

}

 

tep_db_query("insert into " . TABLE_ORDERS_STATUS_HISTORY . " (orders_id, orders_status_id, date_added, customer_notified, comments) values ('" . (int)$oID . "', '" . tep_db_input($status) . "', now(), '" . tep_db_input($customer_notified) . "', '" . tep_db_input($comments) . "')");

 

$order_updated = true;

}

 

if ($order_updated == true) {

$messageStack->add_session(SUCCESS_ORDER_UPDATED, 'success');

} else {

$messageStack->add_session(WARNING_ORDER_NOT_UPDATED, 'warning');

}

 

tep_redirect(tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('action')) . 'action=edit'));

break;

case 'deleteconfirm':

$oID = tep_db_prepare_input($HTTP_GET_VARS['oID']);

 

tep_remove_order($oID, $HTTP_POST_VARS['restock']);

 

tep_redirect(tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID', 'action'))));

break;

}

}

 

if (($action == 'edit') && isset($HTTP_GET_VARS['oID'])) {

$oID = tep_db_prepare_input($HTTP_GET_VARS['oID']);

 

$orders_query = tep_db_query("select orders_id from " . TABLE_ORDERS . " where orders_id = '" . (int)$oID . "'");

$order_exists = true;

if (!tep_db_num_rows($orders_query)) {

$order_exists = false;

$messageStack->add(sprintf(ERROR_ORDER_DOES_NOT_EXIST, $oID), 'error');

}

}

// BOF: WebMakers.com Added: Additional info for Orders

// Look up things in orders

$the_extra_query= tep_db_query("select * from " . TABLE_ORDERS . " where orders_id = '" . tep_db_input($oID) . "'");

$the_extra= tep_db_fetch_array($the_extra_query);

$the_customers_id= $the_extra['customers_id'];

// Look up things in customers

$the_extra_query= tep_db_query("select * from " . TABLE_CUSTOMERS . " where customers_id = '" . $the_customers_id . "'");

$the_extra= tep_db_fetch_array($the_extra_query);

$the_customers_fax= $the_extra['customers_fax'];

// EOF: WebMakers.com Added: Additional info for Orders

 

include(DIR_WS_CLASSES . 'order.php');

?>

<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">

<html <?php echo HTML_PARAMS; ?>>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">

<title><?php echo TITLE; ?></title>

<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">

<script language="javascript" src="includes/general.js"></script>

</head>

<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">

<!-- header //-->

<?php

require(DIR_WS_INCLUDES . 'header.php');

?>

<!-- header_eof //-->

 

<!-- body //-->

<table border="0" width="100%" cellspacing="2" cellpadding="2">

<tr>

<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">

<!-- left_navigation //-->

<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>

<!-- left_navigation_eof //-->

</table></td>

<!-- body_text //-->

<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">

<?php

if (($action == 'edit') && ($order_exists == true)) {

$order = new order($oID);

?>

<tr>

<td width="100%"><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td class="pageHeading"><?php echo HEADING_TITLE; ?></td>

<td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', 1, HEADING_IMAGE_HEIGHT); ?></td>

<td class="pageHeading" align="right"><?php echo '<a href="' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('action'))) . '">' . tep_image_button('button_back.gif', IMAGE_BACK) . '</a>'; ?></td>

</tr>

</table></td>

</tr>

<tr>

<td><table width="100%" border="0" cellspacing="0" cellpadding="2">

<tr>

<td colspan="3"><?php echo tep_draw_separator(); ?></td>

</tr>

<tr>

<td valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="2">

<tr>

<td class="main" valign="top"><b><?php echo ENTRY_CUSTOMER; ?></b></td>

<td class="main"><?php echo tep_address_format($order->customer['format_id'], $order->customer, 1, '', '<br>'); ?></td>

</tr>

<tr>

<td colspan="2"><?php echo tep_draw_separator('pixel_trans.gif', '1', '5'); ?></td>

</tr>

<tr>

<td class="main"><b><?php echo ENTRY_TELEPHONE_NUMBER; ?></b></td>

<td class="main"><?php echo $order->customer['telephone']; ?></td>

</tr>

<?php

// BOF: WebMakers.com Added: Downloads Controller - Extra order info

if ($the_customers_fax) {

?>

<tr>

<td class="main"><b><?php echo 'FAX #:'; ?></b></td>

<td class="main"><?php echo $the_customers_fax; ?></td>

</tr>

<?php

}

// EOF: WebMakers.com Added: Downloads Controller

?>

<tr>

<td class="main"><b><?php echo ENTRY_EMAIL_ADDRESS; ?></b></td>

<td class="main"><?php echo '<a href="mailto:' . $order->customer['email_address'] . '"><u>' . $order->customer['email_address'] . '</u></a>'; ?></td>

</tr>

</table></td>

<td valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="2">

<tr>

<td class="main" valign="top"><b><?php echo ENTRY_SHIPPING_ADDRESS; ?></b></td>

<td class="main"><?php echo tep_address_format($order->delivery['format_id'], $order->delivery, 1, '', '<br>'); ?></td>

</tr>

</table></td>

<td valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="2">

<tr>

<td class="main" valign="top"><b><?php echo ENTRY_BILLING_ADDRESS; ?></b></td>

<td class="main"><?php echo tep_address_format($order->billing['format_id'], $order->billing, 1, '', '<br>'); ?></td>

</tr>

</table></td>

</tr>

</table></td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>

</tr>

<tr>

<td><table border="0" cellspacing="0" cellpadding="2">

<?php

// BOF: WebMakers.com Added: show additional order info

?>

<!-- Customers ID# // -->

<tr>

<td class="main"><b>Customers # </b></td>

<td class="main"><?php echo $the_customers_id; ?></td>

</tr>

<!-- add Order # // -->

<tr>

<td class="main"><b>Order # </b></td>

<td class="main"><?php echo tep_db_input($oID); ?></td>

</tr>

<!-- add date/time // -->

<tr>

<td class="main"><b>Date & Time</b></td>

<td class="main"><?php echo tep_datetime_short($order->info['date_purchased']); ?></td>

</tr>

<tr>

<td class="main"><b>Shipping Method:</b></td>

<td class="main"><?php echo tep_get_orders_shipping_method($oID); ?></td>

</tr>

<?php

// EOF: WebMakers.com Added: show additional order info

?>

<tr>

<td class="main"><b><?php echo ENTRY_PAYMENT_METHOD; ?></b></td>

<td class="main"><?php echo $order->info['payment_method']; ?></td>

</tr>

<?php

if (tep_not_null($order->info['cc_type']) || tep_not_null($order->info['cc_owner']) || tep_not_null($order->info['cc_number'])) {

?>

<tr>

<td colspan="2"><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>

</tr>

<tr>

<td class="main"><?php echo ENTRY_CREDIT_CARD_TYPE; ?></td>

<td class="main"><?php echo $order->info['cc_type']; ?></td>

</tr>

<tr>

<td class="main"><?php echo ENTRY_CREDIT_CARD_OWNER; ?></td>

<td class="main"><?php echo $order->info['cc_owner']; ?></td>

</tr>

<tr>

<td class="main"><?php echo ENTRY_CREDIT_CARD_NUMBER; ?></td>

<td class="main"><?php echo $order->info['cc_number']; ?></td>

</tr>

<tr>

<td class="main"><?php echo ENTRY_CREDIT_CARD_EXPIRES; ?></td>

<td class="main"><?php echo $order->info['cc_expires']; ?></td>

</tr>

<?php

}

?>

</table></td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>

</tr>

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="2">

<tr class="dataTableHeadingRow">

<td class="dataTableHeadingContent" colspan="2"><?php echo TABLE_HEADING_PRODUCTS; ?></td>

<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_PRODUCTS_MODEL; ?></td>

<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TAX; ?></td>

<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_PRICE_EXCLUDING_TAX; ?></td>

<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_PRICE_INCLUDING_TAX; ?></td>

<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL_EXCLUDING_TAX; ?></td>

<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL_INCLUDING_TAX; ?></td>

</tr>

<?php

// WebMakers.com Added: calculate total one time charges

$total_one_time_charges=0;

 

for ($i=0, $n=sizeof($order->products); $i<$n; $i++) {

echo ' <tr class="dataTableRow">' . "\n" .

' <td class="dataTableContent" valign="top" align="right">' . $order->products[$i]['qty'] . ' x</td>' . "\n" .

' <td class="dataTableContent" valign="top">' . $order->products[$i]['name'];

 

if (isset($order->products[$i]['attributes']) && (sizeof($order->products[$i]['attributes']) > 0)) {

for ($j = 0, $k = sizeof($order->products[$i]['attributes']); $j < $k; $j++) {

// WebMakers.com Added: show if attribute is FREE

if ($order->products[$i]['attributes'][$j]['attributes_price_onetime'] != 0) {

$new_attributes_price_onetime= '<br>' . '    ' . ATTRIBUTES_UNITS_ONETIME_TEXT . ' ' . $currencies->display_price($order->products[$i]['attributes'][$j]['attributes_price_onetime_ext'], $order->products[$i]['tax'], 1);

$total_one_time_charges += $order->products[$i]['attributes'][$j]['attributes_price_onetime_ext'];

} else {

$new_attributes_price_onetime='';

}

if (!empty($order->products[$i]['attributes'][$j]['attributes_qty_prices_onetime'])) {

// $new_attributes_qty_prices_onetime= '<br>' . '    ' . ATTRIBUTES_QTY_PRICE_ONETIME_TEXT . ' ' . $currencies->display_price(tep_get_attributes_qty_prices_onetime($order->products[$i]['attributes'][$j]['attributes_qty_prices_onetime'], $order->products[$i]['qty']), $order->products[$i]['tax'], 1);

$new_attributes_qty_prices_onetime= '<br>' . '    ' . ATTRIBUTES_QTY_PRICE_ONETIME_TEXT . ' ' . $currencies->display_price($order->products[$i]['attributes'][$j]['attributes_qty_prices_onetime_ext'], $order->products[$i]['tax'], 1);

$total_one_time_charges += $order->products[$i]['attributes'][$j]['attributes_qty_prices_onetime_ext'];

} else {

$new_attributes_qty_prices_onetime='';

}

// WebMakers.com Added: do not add colon if it exists on end of option name

if (strrpos($order->products[$i]['attributes'][$j]['option'],':') == strlen($order->products[$i]['attributes'][$j]['option'])-1) {

// echo '<br><nobr> <i> - ' . $order->products[$i]['attributes'][$j]['option'] . ': ' . $order->products[$i]['attributes'][$j]['value'];

echo '<br><nobr> <i> - ' . $order->products[$i]['attributes'][$j]['option'] . ' ' . $order->products[$i]['attributes'][$j]['value'] . '</i></nobr>';

} else {

echo '<br><nobr> <i> - ' . $order->products[$i]['attributes'][$j]['option'] . ': ' . $order->products[$i]['attributes'][$j]['value'] . '</i></nobr>';

}

 

echo '</i></nobr>';

if ($order->products[$i]['attributes'][$j]['price'] != '0') {

echo ' (' . $order->products[$i]['attributes'][$j]['prefix'] . $currencies->format($order->products[$i]['attributes'][$j]['price'] * $order->products[$i]['qty'], true, $order->info['currency'], $order->info['currency_value']) . ')' . ($order->products[$i]['attributes'][$j]['product_attribute_is_free']=='1' ? ' - FREE' : '');

}

echo $new_attributes_price_onetime

. $new_attributes_qty_prices_onetime;

}

}

 

echo ' </td>' . "\n" .

' <td class="dataTableContent" valign="top">' . $order->products[$i]['model'] . '</td>' . "\n" .

' <td class="dataTableContent" align="right" valign="top">' . tep_display_tax_value($order->products[$i]['tax']) . '%</td>' . "\n" .

' <td class="dataTableContent" align="right" valign="top"><b>' . $currencies->format($order->products[$i]['final_price'], true, $order->info['currency'], $order->info['currency_value']) . '</b></td>' . "\n" .

' <td class="dataTableContent" align="right" valign="top"><b>' . $currencies->format(tep_add_tax($order->products[$i]['final_price'], $order->products[$i]['tax']), true, $order->info['currency'], $order->info['currency_value']) . '</b></td>' . "\n" .

' <td class="dataTableContent" align="right" valign="top"><b>' . $currencies->format($order->products[$i]['final_price'] * $order->products[$i]['qty'], true, $order->info['currency'], $order->info['currency_value']) . '</b></td>' . "\n" .

' <td class="dataTableContent" align="right" valign="top"><b>' . $currencies->format(tep_add_tax($order->products[$i]['final_price'], $order->products[$i]['tax']) * $order->products[$i]['qty'], true, $order->info['currency'], $order->info['currency_value']) . '</b></td>' . "\n";

echo ' </tr>' . "\n";

}

?>

<tr>

<td align="right" colspan="8"><table border="0" cellspacing="0" cellpadding="2">

<?php

// WebMakers.com Added: Show one time charges totals

echo ' <tr>' . "\n" .

' <td align="right" class="smallText" valign="top">' . ATTRIBUTES_TOTAL_ONETIME_CHARGES . '</td>' . "\n" .

' <td align="right" class="smallText" valign="top">' . $currencies->format($total_one_time_charges) . '<br><br>' . '</td>' . "\n" .

' </tr>' . "\n";

 

for ($i = 0, $n = sizeof($order->totals); $i < $n; $i++) {

echo ' <tr>' . "\n" .

' <td align="right" class="smallText">' . $order->totals[$i]['title'] . '</td>' . "\n" .

' <td align="right" class="smallText">' . $order->totals[$i]['text'] . '</td>' . "\n" .

' </tr>' . "\n";

}

?>

</table></td>

</tr>

</table></td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>

</tr>

<tr>

<td class="main"><table border="1" cellspacing="0" cellpadding="5">

<tr>

<td class="smallText" align="center"><b><?php echo TABLE_HEADING_DATE_ADDED; ?></b></td>

<td class="smallText" align="center"><b><?php echo TABLE_HEADING_CUSTOMER_NOTIFIED; ?></b></td>

<td class="smallText" align="center"><b><?php echo TABLE_HEADING_STATUS; ?></b></td>

<td class="smallText" align="center"><b><?php echo TABLE_HEADING_COMMENTS; ?></b></td>

</tr>

<?php

$orders_history_query = tep_db_query("select orders_status_id, date_added, customer_notified, comments from " . TABLE_ORDERS_STATUS_HISTORY . " where orders_id = '" . tep_db_input($oID) . "' order by date_added");

if (tep_db_num_rows($orders_history_query)) {

while ($orders_history = tep_db_fetch_array($orders_history_query)) {

echo ' <tr>' . "\n" .

' <td class="smallText" align="center">' . tep_datetime_short($orders_history['date_added']) . '</td>' . "\n" .

' <td class="smallText" align="center">';

if ($orders_history['customer_notified'] == '1') {

echo tep_image(DIR_WS_ICONS . 'tick.gif', ICON_TICK) . "</td>\n";

} else {

echo tep_image(DIR_WS_ICONS . 'cross.gif', ICON_CROSS) . "</td>\n";

}

echo ' <td class="smallText">' . $orders_status_array[$orders_history['orders_status_id']] . '</td>' . "\n" .

' <td class="smallText">' . nl2br(tep_db_output($orders_history['comments'])) . ' </td>' . "\n" .

' </tr>' . "\n";

}

} else {

echo ' <tr>' . "\n" .

' <td class="smallText" colspan="5">' . TEXT_NO_ORDER_HISTORY . '</td>' . "\n" .

' </tr>' . "\n";

}

?>

</table></td>

</tr>

<tr>

<td class="main"><br><b><?php echo TABLE_HEADING_COMMENTS; ?></b></td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '1', '5'); ?></td>

</tr>

<tr><?php echo tep_draw_form('status', FILENAME_ORDERS, tep_get_all_get_params(array('action')) . 'action=update_order'); ?>

<td class="main"><?php echo tep_draw_textarea_field('comments', 'soft', '60', '5'); ?></td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>

</tr>

<tr>

<td><table border="0" cellspacing="0" cellpadding="2">

<tr>

<td><table border="0" cellspacing="0" cellpadding="2">

<tr>

<td class="main"><b><?php echo ENTRY_STATUS; ?></b> <?php echo tep_draw_pull_down_menu('status', $orders_statuses, $order->info['orders_status']); ?></td>

</tr>

<tr>

<td class="main"><b><?php echo ENTRY_NOTIFY_CUSTOMER; ?></b> <?php echo tep_draw_checkbox_field('notify', '', true); ?></td>

<td class="main"><b><?php echo ENTRY_NOTIFY_COMMENTS; ?></b> <?php echo tep_draw_checkbox_field('notify_comments', '', true); ?></td>

</tr>

</table></td>

<td valign="top"><?php echo tep_image_submit('button_update.gif', IMAGE_UPDATE); ?></td>

</tr>

</table></td>

</form></tr>

<tr>

<td colspan="2" align="right"><?php echo '<a href="' . tep_href_link(FILENAME_ORDERS_INVOICE, 'oID=' . $HTTP_GET_VARS['oID']) . '" TARGET="_blank">' . tep_image_button('button_invoice.gif', IMAGE_ORDERS_INVOICE) . '</a> <a href="' . tep_href_link(FILENAME_ORDERS_PACKINGSLIP, 'oID=' . $HTTP_GET_VARS['oID']) . '" TARGET="_blank">' . tep_image_button('button_packingslip.gif', IMAGE_ORDERS_PACKINGSLIP) . '</a> <a href="' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('action'))) . '">' . tep_image_button('button_back.gif', IMAGE_BACK) . '</a>'; ?></td>

</tr>

<?php

} else {

?>

<tr>

<td width="100%"><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td class="pageHeading"><?php echo HEADING_TITLE; ?></td>

<td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', 1, HEADING_IMAGE_HEIGHT); ?></td>

<td align="right"><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr><?php echo tep_draw_form('orders', FILENAME_ORDERS, '', 'get'); ?>

<td class="smallText" align="right"><?php echo HEADING_TITLE_SEARCH . ' ' . tep_draw_input_field('oID', '', 'size="12"') . tep_draw_hidden_field('action', 'edit'); ?></td>

</form></tr>

<tr><?php echo tep_draw_form('status', FILENAME_ORDERS, '', 'get'); ?>

<td class="smallText" align="right"><?php echo HEADING_TITLE_STATUS . ' ' . tep_draw_pull_down_menu('status', array_merge(array(array('id' => '', 'text' => TEXT_ALL_ORDERS)), $orders_statuses), '', 'onChange="this.form.submit();"'); ?></td>

</form></tr>

</table></td>

</tr>

</table></td>

</tr>

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">

<?php

// WebMakers.com Added: Display ID# in listing

?>

<tr class="dataTableHeadingRow">

<td class="dataTableHeadingContent" width='30'><?php echo 'ID #'; ?></td>

<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_CUSTOMERS; ?></td>

<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ORDER_TOTAL; ?></td>

<td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_DATE_PURCHASED; ?></td>

<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_STATUS; ?></td>

<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACTION; ?> </td>

</tr>

<?php

if (isset($HTTP_GET_VARS['cID'])) {

$cID = tep_db_prepare_input($HTTP_GET_VARS['cID']);

$orders_query_raw = "select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by orders_id DESC";

} elseif (isset($HTTP_GET_VARS['status'])) {

$status = tep_db_prepare_input($HTTP_GET_VARS['status']);

$orders_query_raw = "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 " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and s.orders_status_id = '" . (int)$status . "' and ot.class = 'ot_total' order by o.orders_id DESC";

} else {

$orders_query_raw = "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 " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";

}

// WebMakers.com Added: Shoppe Enhancement Controller

// Give own MAX_DISPLAY

$orders_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS_ORDERS, $orders_query_raw, $orders_query_numrows);

$orders_query = tep_db_query($orders_query_raw);

while ($orders = tep_db_fetch_array($orders_query)) {

if ((!isset($HTTP_GET_VARS['oID']) || (isset($HTTP_GET_VARS['oID']) && ($HTTP_GET_VARS['oID'] == $orders['orders_id']))) && !isset($oInfo)) {

$oInfo = new objectInfo($orders);

}

 

if (isset($oInfo) && is_object($oInfo) && ($orders['orders_id'] == $oInfo->orders_id)) {

echo ' <tr id="defaultSelected" class="dataTableRowSelected" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID', 'action')) . 'oID=' . $oInfo->orders_id . '&action=edit') . '\'">' . "\n";

$current_row='dataTableContentSelectedList';

} else {

echo ' <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID')) . 'oID=' . $orders['orders_id']) . '\'">' . "\n";

$current_row='dataTableContent';

}

// WebMakers.com Added: Display ID# in listing

?>

<td class="<?php echo $current_row; ?>"><?php echo '#' . $

Link to comment
Share on other sites

  • 2 weeks later...

Just another little potential tip for those with a persistent, albeit intractable 1064 error.

 

I'm using MySQL 3.23.58 (yeah, I should update, you're right, you sure are smart!) but I NEVER had a problem with osCommerce M2.2. Phenomenal. Love it. That is until I started letting other people do the input.

 

Now this may be a little noobish for some, but occasionally we forget.. also a note for my employer..

 

ADMINS PLEASE REMEMBER: DO NOT PUT QUOTATION MARKS OR ANY OTHER ERRONEOUS CHARACTERS IN YOUR CATALOG PRODUCTS NAME ENTRY FIELD.

 

Our problem was created when the DivX dowloads we sold were named - "SuperSomething Movie" starring Actress Z - Killing the quotation marks made all the difference.

 

And if you are using MySQL 3.xx.x like me, remember to restore those backup versions of split_page_results.php that you so intelligently made (if you changed the originals). They work just fine.

 

Apologies if this has already been posted. Probably bears repeating anyways.

Link to comment
Share on other sites

I was getting 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

 

 

But for some reason, none of the solutions here worked for me. This thread did point me to the right direction though,

 

This is what fixed my problem...

 

Edit catalog/admin/includes/classes/split_page_results.php

 

Find this line

$offset = ($max_rows_per_page * ($current_page_number - 1));

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

 

 

And replace with this one.

$offset = ($max_rows_per_page * ($current_page_number - 1));

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

 

Try all the solutions above first. If none of them works, you can try mine. Thats what I did. :P

Link to comment
Share on other sites

  • 1 month later...

Database error in vBulletin 3.6.2:

 

 

UPDATE post SET

parentid = CASE WHEN parentid = 15862 THEN WHEN parentid = 15872 THEN WHEN parentid = 15894 THEN WHEN parentid = 15904 THEN WHEN parentid = 15905 THEN WHEN parentid = 15945 THEN WHEN parentid = 15994 THEN WHEN parentid = 16196 THEN WHEN parentid = 16199 THEN WHEN parentid = 16200 THEN WHEN parentid = 16201 THEN WHEN parentid = 16202 THEN WHEN parentid = 16203 THEN WHEN parentid = 16205 THEN WHEN parentid = 16656 THEN WHEN parentid = 16748 THEN WHEN parentid = 16754 THEN WHEN parentid = 25029 THEN ELSE parentid END

WHERE threadid = 3102 AND parentid IN (0,15862,15872,15894,15904,15905,15945,15994,16196,16199,16200,16201,16202,16203

,16205,16656,16748,16754,25029);

 

MySQL Error : 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 'WHEN parentid = 15872 THEN WHEN parentid = 15894 THEN WHEN parentid = 15904 TH' at line 2

Error Number : 1064

Date : Monday, October 16th 2006 @ 09:11:37 PM

Script : http://www.XXXX.net/vb/postings.php?do=dom...read&t=1197

Referrer : http://www.XXXX.net/vb/postings.php?t=1197&pollid=

IP Address : xxx.xxx.xxx.xxx

Username : admin

Classname : bbfrom_database

 

 

 

 

Please help.

I talked this problem When used copies of the Thread

Link to comment
Share on other sites

  • 2 weeks later...

Hi There All.

 

I'm new when it comes to my My SQL ,and I don't have a clue what cause this error. :blink:

 

I just start to learn PHP few months back, :'( so if someone can help me I would greatly appreciate it!

 

Please could you point me, which files do I need to Open to fix this error?

 

 

 

MySQL version 4.1.21-standard

 

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

 

select r.reviews_id, r.reviews_rating, p.products_id, p.products_image, pd.products_name from reviews r, reviews_description rd, products p, products_description pd where p.products_status = '1' and p.products_id = r.products_id and r.reviews_id = rd.reviews_id and rd.languages_id = '1' and p.products_id = pd.products_id and pd.language_id = '1' order by r.reviews_id desc limit

 

[TEP STOP]

 

 

SORRY FOR MY BAD ENLGISH

 

Kind Regards

Link to comment
Share on other sites

  • 3 weeks later...

Hi All,

 

I hope someone can help me resolve the following error I am getting each time I attempt to add either a new category or a new sub-category. This error only shows in admin section.

 

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 'ALL,324)) and c.categories_id = cd.categories_id and cd.language_id = '1' order ' at line 1

 

select c.categories_id, cd.categories_name, c.categories_image, c.parent_id, c.sort_order, c.date_added, c.last_modified from categories c, categories_description cd where c.parent_id = '44' and (c.parent_id or c.categories_id in (ALL,324)) and c.categories_id = cd.categories_id and cd.language_id = '1' order by c.sort_order, cd.categories_name

 

However the category or sub-category I would have entered shows in the catalog section. So the insertion does take place ...its just that I cannot access

 

*****/catalog/admin/categories.php?cPath=44&cID=324

 

as it gives the above error.

 

This problem is limited to only categories. I can add products with no problems.

 

I have a number of contributions which include Admin_Access2.1 with categories

 

 

Any help would be appreciated

Thank you

Link to comment
Share on other sites

Hi Guys

 

Iam also had problems ever since my host upgraded to php 4.1

 

ive done the fixes like you mentioned and those errors are now gone, but i can see there are also problems with.

 

When going to catalog > then click on the go to drop down box to quick select a category oscommerce just logs out. Now i havent made any changnes to oscommerce, and even did a backup to when i knew it was working fine, so its defeinetly a php 4.1 problem.

 

Anyone else having this issue

 

its also doing similar issues with

 

easypopulate contribution & quick updates contribution.

Link to comment
Share on other sites

I have now fixed the problem below. This is how I did it just in case it helps someone.

I restored a known working /admin/categories.php (without the contribution for Admin_access 2.1 with Categories)

I then used Beyond Compare to compare the code. I made changes as required till I got it working.

 

 

 

Hi All,

 

I hope someone can help me resolve the following error I am getting each time I attempt to add either a new category or a new sub-category. This error only shows in admin section.

However the category or sub-category I would have entered shows in the catalog section. So the insertion does take place ...its just that I cannot access

 

*****/catalog/admin/categories.php?cPath=44&cID=324

 

as it gives the above error.

 

This problem is limited to only categories. I can add products with no problems.

 

I have a number of contributions which include Admin_Access2.1 with categories

Any help would be appreciated

Thank you

Link to comment
Share on other sites

  • 4 weeks later...

Hello everyone,

 

I have also had an issue with this but mine is slightly different and occurs at time of checkout and processing the credit card with moneris

 

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 'orderids (moneris_orderid, orders_id, response_variables) values ('mdp2684660017' at line 1

 

insert into moneris orderids (moneris_orderid, orders_id, response_variables) values ('mdp26846600178', '9', 'mdp26846600178;2007-01-10;16:53:22;660021730016889900;6.00;002104;027;01;APPROVED * =;purchase;;4242***4242;V ;0701;1;3df037d782ecbefdf41eea22f2f11e12')

 

[TEP STOP]

 

 

Any help would be greatly appreciated!

 

Jeff

Link to comment
Share on other sites

  • 2 weeks later...

hello someone can help me?

 

when i'm goign to conclude the order ( /checkout_confirmation.php) ....i 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 '' at line 1

 

select g.geo_zone_name from geo_zones g , zones_to_geo_zones z where g.geo_zone_id = z.geo_zone_id AND z.zone_country_id=

 

[TEP STOP]

 

MySQL 4.0.18-standard

 

 

how i can solve it ?

 

thanks

 

Maury

Link to comment
Share on other sites

Another kudos for Charles from this corner of the world! Thank you so much for posting the fixes to both split_page_results.php. I'd been pulling out my hair trying to figure this out, and you have saved me from permanent baldness. My DH thanks you for that, too, since he has to look at me.

Link to comment
Share on other sites

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 :D

 

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.

 

:o 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

YOUR ****ING AWSOME!!!! Thanks so much man, I was on the phone with tech support, caller 5, by the time I was caller number 3 it was fixed! :)

-Eric Schrader

Link to comment
Share on other sites

Hi, I've tried all the tips from this thread but i'm still getting error.

PLS HELP! :( Thks a million in advance!!!

 

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 p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, p.products_sort_order, 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, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '21' order by limit 0, 20

 

[TEP STOP]

 

This happens when I clicked the price to sort my products. I've no idea what table/s I've deleted.

Link to comment
Share on other sites

I applied this and it fixed the newsletter but now I have the following error in specials.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 '-6, 6' at line 1

 

select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from products p, products_description pd, specials s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and s.status = '1' order by s.specials_date_added DESC limit -6, 6

 

[TEP STOP]

 

 

Any idea on this one?

 

Thanks!

Link to comment
Share on other sites

Thank you SimplyEasy you fixed my problem! thanks must also go to Kawei95 for linking to this topic 'cos I would have spent a few years finding the anser. Now all I gotta do is work out how to replace the jpg image with a gif for the banner that's on ever page.... year later.....

Link to comment
Share on other sites

Hi,

 

Can anyone help me?

 

My error is slightly different and am wondering if it is at all related. I tried the fixes previously mentioned but no luck.

 

When trying to add a new product category to the my website I get the following 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 '))' at line 1

insert into categories (sort_order, parent_id, date_added) values ('', '0', date_add(now(), INTERVAL +15HOUR))

 

 

and when trying to add a new product to my website I get the following 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 '))' at line 1

insert into products (products_quantity, products_model, products_price, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id, products_date_added) values ('1', '', '12', null, '12', '1', '1', '', date_add(now(), INTERVAL +15HOUR))

 

 

Thanks,

 

Darren

Link to comment
Share on other sites

Hi -

 

I am having a similar problem as everyone listed here but haven't find any that's just like mine. I get the 1064 error, but it only occurs once in a while (only 2 times in the past 3 years). What happens is every now and then an order gets through to our payment gateway and shows up there but does not show up as an order in osCommerce. If I look up the customer in osCommerce then click on 'orders', I 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 '-9, 9' at line 1

 

select o.orders_id, o.customers_name, o.customers_id, 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.customers_id = '1669' and o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by orders_id DESC limit -9, 9

 

I have had other orders come through on the same day that are fine. Any idea as to what is happening and how to fix?

 

Many thanks in advance!

Link to comment
Share on other sites

  • 2 weeks later...

hi there.. i have tried looking for the source code fromther very first page though i do not SEE the code in the split_page_results at all..

 

i am getting to error below after i deleted all my customer.. can anyone please assist with some info???

 

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

 

select c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, a.entry_country_id from customers c left join address_book a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id order by c.customers_lastname, c.customers_firstname limit -6, 6

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...