BestSeller Contribution..Anyone used it ??

[johnnykalsi]

I downloaded and installed the following contribution:

 

http://www.oscommerce.com/community/contributions,1944

 

Installed the non STS version, I dunno what STS is anyways...

Followed installed exactly, and now when I clicked on the Bestseller link on the admin panel,

I get the following 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 p.products_id, pd.products_name, s.bestseller_id, s.bestseller_date_added, s.bestseller_last_modified, s.expires_date, s.date_status_change, s.status from products p, bestseller 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 -20, 20

 

Has anyone used this contribution...do you know what this error means..?

Thanks for your help

Johnny Kalsi

[Jack_mcs]

I haven't used the contribution you are talking about but can offer a few suggestions. First off, if you are not using STS on your shop (Simple Template System contribution), you shouldn't be installing code meant specifically for it. Secondly, the error you are getting indicates a problem with the database or the code accessing it. Does the contribution need to have changes made to the database? If so, and you forgot to do that, then you could get this error. Re-read the instructions and make sure you didn't miss anything. Finally, see if there is a support thread for the contribution. It is always better to ask questions about a contribution in the contributions support thread.

 

Jack

[johnnykalsi]

Hi

I am not using the STS version...also, I accessed my database using phpadmin, dropped the table and recreated it using the code as mentioned in the rreadme file

 

create table bestseller (

bestseller_id int(11) not null auto_increment,

products_id int(11) default '0' not null ,

bestseller_date_added datetime ,

bestseller_last_modified datetime ,

expires_date datetime ,

date_status_change datetime ,

status int(1) default '1' ,

PRIMARY KEY (bestseller_id)

);

 

INSERT INTO configuration_group (configuration_group_id, configuration_group_title, configuration_group_description, sort_order, visible) values ('69','bestseller', 'Best Sellers Products Display', '339', '1'

);

 

INSERT into configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added) values ('Display Best Sellers Products', 'BESTSELLER_PRODUCTS_DISPLAY', 'true', 'Set to true or false in order to display best sellers.', '69', '1', now(), now()

);

 

INSERT into configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added) values ('Maximum Display of Best Sellers', 'MAX_DISPLAY_BESTSELLER_PRODUCTS', '6', 'This is the maximum amount of items to display on the best sellers info box.', '69', '2', now(), now()

);

 

 

Table was created successfully...

 

As far as I know, the php file that is being usied when I click on the bestseller list on the admin page is

bestseller.php in the catalog/admin/ section

[johnnykalsi]

The Following is the bestseller.php file

 

<?php

/*

osCommerce, Open Source E-Commerce Solutions

http://www.oscommerce.com

 

Copyright © 2002 osCommerce

 

Released under the GNU General Public License

 

Best Seller Products admin

*/

 

require('includes/application_top.php');

 

function tep_set_bestseller_status($bestseller_id, $status) {

if ($status == '1') {

return tep_db_query("update " . TABLE_BESTSELLER . " set status = '1', expires_date = NULL, date_status_change = NULL where bestseller_id = '" . $bestseller_id . "'");

} elseif ($status == '0') {

return tep_db_query("update " . TABLE_BESTSELLER . " set status = '0', date_status_change = now() where bestseller_id = '" . $bestseller_id . "'");

} else {

return -1;

}

}

 

require(DIR_WS_CLASSES . 'currencies.php');

$currencies = new currencies();

 

switch ($HTTP_GET_VARS['action']) {

case 'setflag':

tep_set_bestseller_status($HTTP_GET_VARS['id'], $HTTP_GET_VARS['flag']);

tep_redirect(tep_href_link(FILENAME_BESTSELLER, '', 'NONSSL'));

break;

case 'insert':

$expires_date = '';

if ($HTTP_POST_VARS['day'] && $HTTP_POST_VARS['month'] && $HTTP_POST_VARS['year']) {

$expires_date = $HTTP_POST_VARS['year'];

$expires_date .= (strlen($HTTP_POST_VARS['month']) == 1) ? '0' . $HTTP_POST_VARS['month'] : $HTTP_POST_VARS['month'];

$expires_date .= (strlen($HTTP_POST_VARS['day']) == 1) ? '0' . $HTTP_POST_VARS['day'] : $HTTP_POST_VARS['day'];

}

 

tep_db_query("insert into " . TABLE_BESTSELLER . " (products_id, bestseller_date_added, expires_date, status) values ('" . $HTTP_POST_VARS['products_id'] . "', now(), '" . $expires_date . "', '1')");

tep_redirect(tep_href_link(FILENAME_BESTSELLER, 'page=' . $HTTP_GET_VARS['page']));

break;

case 'update':

$expires_date = '';

if ($HTTP_POST_VARS['day'] && $HTTP_POST_VARS['month'] && $HTTP_POST_VARS['year']) {

$expires_date = $HTTP_POST_VARS['year'];

$expires_date .= (strlen($HTTP_POST_VARS['month']) == 1) ? '0' . $HTTP_POST_VARS['month'] : $HTTP_POST_VARS['month'];

$expires_date .= (strlen($HTTP_POST_VARS['day']) == 1) ? '0' . $HTTP_POST_VARS['day'] : $HTTP_POST_VARS['day'];

}

 

tep_db_query("update " . TABLE_BESTSELLER . " set bestseller_last_modified = now(), expires_date = '" . $expires_date . "' where bestseller_id = '" . $HTTP_POST_VARS['bestseller_id'] . "'");

tep_redirect(tep_href_link(FILENAME_BESTSELLER, 'page=' . $HTTP_GET_VARS['page'] . '&sID=' . $bestseller_id));

break;

case 'deleteconfirm':

$bestseller_id = tep_db_prepare_input($HTTP_GET_VARS['sID']);

 

tep_db_query("delete from " . TABLE_BESTSELLER . " where bestseller_id = '" . tep_db_input($bestseller_id) . "'");

 

tep_redirect(tep_href_link(FILENAME_BESTSELLER, 'page=' . $HTTP_GET_VARS['page']));

break;

}

?>

<!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>

<?php

if ( ($HTTP_GET_VARS['action'] == 'new') || ($HTTP_GET_VARS['action'] == 'edit') ) {

?>

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

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

<?php

}

?>

</head>

<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF" onload="SetFocus();">

<div id="popupcalendar" class="text"></div>

<!-- 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">

<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', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>

</tr>

</table></td>

</tr>

<?php

if ( ($HTTP_GET_VARS['action'] == 'new') || ($HTTP_GET_VARS['action'] == 'edit') ) {

$form_action = 'insert';

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

$form_action = 'update';

 

$product_query = tep_db_query("select p.products_id, pd.products_name, s.expires_date from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_BESTSELLER . " s where p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' and p.products_id = s.products_id and s.bestseller_id = '" . $HTTP_GET_VARS['sID'] . "' order by pd.products_name");

$product = tep_db_fetch_array($product_query);

 

$sInfo = new objectInfo($product);

} else {

$sInfo = new objectInfo(array());

 

// create an array of bestseller products, which will be excluded from the pull down menu of products

// (when creating a new bestseller product)

$bestseller_array = array();

$bestseller_query = tep_db_query("select p.products_id from " . TABLE_PRODUCTS . " p, " . TABLE_BESTSELLER . " s where s.products_id = p.products_id");

while ($bestseller = tep_db_fetch_array($bestseller_query)) {

$bestseller_array[] = $bestseller['products_id'];

}

}

?>

<tr><form name="new_feature" <?php echo 'action="' . tep_href_link(FILENAME_BESTSELLER, tep_get_all_get_params(array('action', 'info', 'sID')) . 'action=' . $form_action, 'NONSSL') . '"'; ?> method="post"><?php if ($form_action == 'update') echo tep_draw_hidden_field('bestseller_id', $HTTP_GET_VARS['sID']); ?>

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

<tr>

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

<td class="main"><?php echo ($sInfo->products_name) ? $sInfo->products_name : tep_draw_products_pull_down('products_id', 'style="font-size:10px"', $bestseller_array); echo tep_draw_hidden_field('products_price', $sInfo->products_price); ?></td>

</tr>

<tr>

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

<td class="main"><?php echo tep_draw_input_field('day', substr($sInfo->expires_date, 8, 2), 'size="2" maxlength="2" class="cal-TextBox"') . tep_draw_input_field('month', substr($sInfo->expires_date, 5, 2), 'size="2" maxlength="2" class="cal-TextBox"') . tep_draw_input_field('year', substr($sInfo->expires_date, 0, 4), 'size="4" maxlength="4" class="cal-TextBox"'); ?><a class="so-BtnLink" href="java script:calClick();return false;" onmouseover="calSwapImg('BTN_date', 'img_Date_OVER',true);" onmouseout="calSwapImg('BTN_date', 'img_Date_UP',true);" onclick="calSwapImg('BTN_date', 'img_Date_DOWN');showCalendar('new_feature','dteWhen','BTN_date');return false;"><?php echo tep_image(DIR_WS_IMAGES . 'cal_date_up.gif', 'Calendar', '22', '17', 'align="absmiddle" name="BTN_date"'); ?></a></td>

</tr>

</table></td>

</tr>

<tr>

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

<tr>

<td class="main" align="right" valign="top"><br><?php echo (($form_action == 'insert') ? tep_image_submit('button_insert.gif', IMAGE_INSERT) : tep_image_submit('button_update.gif', IMAGE_UPDATE)). '   <a href="' . tep_href_link(FILENAME_BESTSELLER, 'page=' . $HTTP_GET_VARS['page'] . '&sID=' . $HTTP_GET_VARS['sID']) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>'; ?></td>

</tr>

</table></td>

</form></tr>

<?php

} else {

?>

<tr>

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

<tr>

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

<tr class="dataTableHeadingRow">

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

<td class="dataTableHeadingContent" align="right"> </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

$bestseller_query_raw = "select p.products_id, pd.products_name, s.bestseller_id, s.bestseller_date_added, s.bestseller_last_modified, s.expires_date, s.date_status_change, s.status from " . TABLE_PRODUCTS . " p, " . TABLE_BESTSELLER . " s, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' and p.products_id = s.products_id order by pd.products_name";

$bestseller_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $bestseller_query_raw, $bestseller_query_numrows);

$bestseller_query = tep_db_query($bestseller_query_raw);

while ($bestseller = tep_db_fetch_array($bestseller_query)) {

if ( ((!$HTTP_GET_VARS['sID']) || ($HTTP_GET_VARS['sID'] == $bestseller['bestseller_id'])) && (!$sInfo) ) {

 

$products_query = tep_db_query("select products_image from " . TABLE_PRODUCTS . " where products_id = '" . $bestseller['products_id'] . "'");

$products = tep_db_fetch_array($products_query);

$sInfo_array = array_merge($bestseller, $products);

$sInfo = new objectInfo($sInfo_array);

}

 

if ( (is_object($sInfo)) && ($bestseller['bestseller_id'] == $sInfo->bestseller_id) ) {

echo ' <tr class="dataTableRowSelected" onmouseover="this.style.cursor=\'hand\'" onclick="document.location.href=\'' . tep_href_link(FILENAME_BESTSELLER, 'page=' . $HTTP_GET_VARS['page'] . '&sID=' . $sInfo->bestseller_id . '&action=edit') . '\'">' . "\n";

} else {

echo ' <tr class="dataTableRow" onmouseover="this.className=\'dataTableRowOver\';this.style.cursor=\'hand\'" onmouseout="this.className=\'dataTableRow\'" onclick="document.location.href=\'' . tep_href_link(FILENAME_BESTSELLER, 'page=' . $HTTP_GET_VARS['page'] . '&sID=' . $bestseller['bestseller_id']) . '\'">' . "\n";

}

?>

<td class="dataTableContent"><?php echo $bestseller['products_name']; ?></td>

<td class="dataTableContent" align="right"> </td>

<td class="dataTableContent" align="right">

<?php

if ($bestseller['status'] == '1') {

echo tep_image(DIR_WS_IMAGES . 'icon_status_green.gif', IMAGE_ICON_STATUS_GREEN, 10, 10) . '  <a href="' . tep_href_link(FILENAME_BESTSELLER, 'action=setflag&flag=0&id=' . $bestseller['bestseller_id'], 'NONSSL') . '">' . tep_image(DIR_WS_IMAGES . 'icon_status_red_light.gif', IMAGE_ICON_STATUS_RED_LIGHT, 10, 10) . '</a>';

} else {

echo '<a href="' . tep_href_link(FILENAME_BESTSELLER, 'action=setflag&flag=1&id=' . $bestseller['bestseller_id'], 'NONSSL') . '">' . tep_image(DIR_WS_IMAGES . 'icon_status_green_light.gif', IMAGE_ICON_STATUS_GREEN_LIGHT, 10, 10) . '</a>  ' . tep_image(DIR_WS_IMAGES . 'icon_status_red.gif', IMAGE_ICON_STATUS_RED, 10, 10);

}

?></td>

<td class="dataTableContent" align="right"><?php if ( (is_object($sInfo)) && ($bestseller['bestseller_id'] == $sInfo->bestseller_id) ) { echo tep_image(DIR_WS_IMAGES . 'icon_arrow_right.gif', ''); } else { echo '<a href="' . tep_href_link(FILENAME_BESTSELLER, 'page=' . $HTTP_GET_VARS['page'] . '&sID=' . $bestseller['bestseller_id']) . '">' . tep_image(DIR_WS_IMAGES . 'icon_info.gif', IMAGE_ICON_INFO) . '</a>'; } ?> </td>

</tr>

<?php

}

?>

<tr>

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

<tr>

<td class="smallText" valign="top"><?php echo $bestseller_split->display_count($bestseller_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, $HTTP_GET_VARS['page'], TEXT_DISPLAY_NUMBER_OF_BESTSELLER); ?></td>

<td class="smallText" align="right"><?php echo $bestseller_split->display_links($bestseller_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, MAX_DISPLAY_PAGE_LINKS, $HTTP_GET_VARS['page']); ?></td>

</tr>

<?php

if (!$HTTP_GET_VARS['action']) {

?>

<tr>

<td colspan="2" align="right"><?php echo '<a href="' . tep_href_link(FILENAME_BESTSELLER, 'page=' . $HTTP_GET_VARS['page'] . '&action=new') . '">' . tep_image_button('button_new_product.gif', IMAGE_NEW_PRODUCT) . '</a>'; ?></td>

</tr>

<?php

}

?>

</table></td>

</tr>

</table></td>

<?php

$heading = array();

$contents = array();

switch ($HTTP_GET_VARS['action']) {

case 'delete':

$heading[] = array('text' => '<b>' . TEXT_INFO_HEADING_DELETE_BESTSELLER . '</b>');

 

$contents = array('form' => tep_draw_form('bestseller', FILENAME_BESTSELLER, 'page=' . $HTTP_GET_VARS['page'] . '&sID=' . $sInfo->bestseller_id . '&action=deleteconfirm'));

$contents[] = array('text' => TEXT_INFO_DELETE_INTRO);

$contents[] = array('text' => '<br><b>' . $sInfo->products_name . '</b>');

$contents[] = array('align' => 'center', 'text' => '<br>' . tep_image_submit('button_delete.gif', IMAGE_DELETE) . ' <a href="' . tep_href_link(FILENAME_BESTSELLER, 'page=' . $HTTP_GET_VARS['page'] . '&sID=' . $sInfo->bestseller_id) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>');

break;

default:

if (is_object($sInfo)) {

$heading[] = array('text' => '<b>' . $sInfo->products_name . '</b>');

 

$contents[] = array('align' => 'center', 'text' => '<a href="' . tep_href_link(FILENAME_BESTSELLER, 'page=' . $HTTP_GET_VARS['page'] . '&sID=' . $sInfo->bestseller_id . '&action=edit') . '">' . tep_image_button('button_edit.gif', IMAGE_EDIT) . '</a> <a href="' . tep_href_link(FILENAME_BESTSELLER, 'page=' . $HTTP_GET_VARS['page'] . '&sID=' . $sInfo->bestseller_id . '&action=delete') . '">' . tep_image_button('button_delete.gif', IMAGE_DELETE) . '</a>');

$contents[] = array('text' => '<br>' . TEXT_INFO_DATE_ADDED . ' ' . tep_date_short($sInfo->bestseller_date_added));

$contents[] = array('text' => '' . TEXT_INFO_LAST_MODIFIED . ' ' . tep_date_short($sInfo->bestseller_last_modified));

$contents[] = array('align' => 'center', 'text' => '<br>' . tep_info_image($sInfo->products_image, $sInfo->products_name, SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT));

 

$contents[] = array('text' => '<br>' . TEXT_INFO_EXPIRES_DATE . ' <b>' . tep_date_short($sInfo->expires_date) . '</b>');

$contents[] = array('text' => '' . TEXT_INFO_STATUS_CHANGE . ' ' . tep_date_short($sInfo->date_status_change));

}

break;

}

if ( (tep_not_null($heading)) && (tep_not_null($contents)) ) {

echo ' <td width="25%" valign="top">' . "\n";

 

$box = new box;

echo $box->infoBox($heading, $contents);

 

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

}

}

?>

</tr>

</table></td>

</tr>

</table></td>

<!-- body_text_eof //-->

</tr>

</table>

<!-- body_eof //-->

 

<!-- footer //-->

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

<!-- footer_eof //-->

</body>

</html>

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

[johnnykalsi]

The error I'm 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 '-20, 20' at line 1

 

select p.products_id, pd.products_name, s.bestseller_id, s.bestseller_date_added, s.bestseller_last_modified, s.expires_date, s.date_status_change, s.status from products p, bestseller 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 -20, 20

 

 

I checked the tables reference above...all of them match...the syntax of SQl looks good

only think I do not understand is what the meaning of the last page

 

limit -20, 20

 

what is that ?

[johnnykalsi]

Found the Answer

 

Look at the Bug Report

 

http://www.oscommerce.com/community/bugs,1...abase+(General)

 

Also look at this

 

http://www.oscommerce.com/forums/index.php?sho...1062+SQL+syntax