SQL Syntax Error In Newletter Module

wehttam37 · February 7, 2009

Hello. I am encountering the following error on the Newletter manager page of admin, which started showed up after my webhost upgraded the SQL version on their server. The error is:

Newsletter Manager

Newsletters Size Module Sent Status 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 '-20, 20' at line 1

select newsletters_id, title, length(content) as content_length, module, date_added, date_sent, status, locked from newsletters order by date_added desc limit -20, 20

[TEP STOP]

I attempted dropping the newletter table in MySQL and running a query to create another newletter table from a backup of the newest version of Oscommerce. This worked for my Tax Zones, where I was having the same problem, but unfortunately, not for the newsletter module.

The SQL query I attempted to run without any positive results, and actual code from my admin/newsletters.php file are both pasted below. If anyone can direct me what to do, with my code or what SQL query to run to correct the syntax error in my database, I greatly appreciate it. Need more info, email me at "mclm @ praiseyah . com" (no spaces of course)

Thankyou so much.

Matthew

--

-- Table structure for table `newsletters`

--

DROP TABLE IF EXISTS `newsletters`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `newsletters` (

`newsletters_id` int(11) NOT NULL auto_increment,

`title` varchar(255) NOT NULL,

`content` text NOT NULL,

`module` varchar(255) NOT NULL,

`date_added` datetime NOT NULL,

`date_sent` datetime default NULL,

`status` int(1) default NULL,

`locked` int(1) default '0',

PRIMARY KEY (`newsletters_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET character_set_client = @saved_cs_client;

--

-- Dumping data for table `newsletters`

--

LOCK TABLES `newsletters` WRITE;

/*!40000 ALTER TABLE `newsletters` DISABLE KEYS */;

/*!40000 ALTER TABLE `newsletters` ENABLE KEYS */;

UNLOCK TABLES;

PHP CODE FOR ADMIN/NEWSLETTERS.PHP FILE IS BELOW, although I don't believe that is where the problem lies. I already replaced this with the code from the newest version of OSC as well.

<?php

/*

$Id: newsletters.php 1751 2007-12-21 05:26:09Z hpdl $

osCommerce, Open Source E-Commerce Solutions

http://www.oscommerce.com

Released under the GNU General Public License

*/

require('includes/application_top.php');

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

if (tep_not_null($action)) {

switch ($action) {

case 'lock':

case 'unlock':

$newsletter_id = tep_db_prepare_input($HTTP_GET_VARS['nID']);

$status = (($action == 'lock') ? '1' : '0');

tep_db_query("update " . TABLE_NEWSLETTERS . " set locked = '" . $status . "' where newsletters_id = '" . (int)$newsletter_id . "'");

tep_redirect(tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $HTTP_GET_VARS['nID']));

break;

case 'insert':

case 'update':

if (isset($HTTP_POST_VARS['newsletter_id'])) $newsletter_id = tep_db_prepare_input($HTTP_POST_VARS['newsletter_id']);

$newsletter_module = tep_db_prepare_input($HTTP_POST_VARS['module']);

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

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

$newsletter_error = false;

if (empty($title)) {

$messageStack->add(ERROR_NEWSLETTER_TITLE, 'error');

$newsletter_error = true;

}

if (empty($newsletter_module)) {

$messageStack->add(ERROR_NEWSLETTER_MODULE, 'error');

$newsletter_error = true;

}

if ($newsletter_error == false) {

$sql_data_array = array('title' => $title,

'content' => $content,

'module' => $newsletter_module);

if ($action == 'insert') {

$sql_data_array['date_added'] = 'now()';

$sql_data_array['status'] = '0';

$sql_data_array['locked'] = '0';

tep_db_perform(TABLE_NEWSLETTERS, $sql_data_array);

$newsletter_id = tep_db_insert_id();

} elseif ($action == 'update') {

tep_db_perform(TABLE_NEWSLETTERS, $sql_data_array, 'update', "newsletters_id = '" . (int)$newsletter_id . "'");

}

tep_redirect(tep_href_link(FILENAME_NEWSLETTERS, (isset($HTTP_GET_VARS['page']) ? 'page=' . $HTTP_GET_VARS['page'] . '&' : '') . 'nID=' . $newsletter_id));

} else {

$action = 'new';

}

break;

case 'deleteconfirm':

$newsletter_id = tep_db_prepare_input($HTTP_GET_VARS['nID']);

tep_db_query("delete from " . TABLE_NEWSLETTERS . " where newsletters_id = '" . (int)$newsletter_id . "'");

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

break;

case 'delete':

case 'new': if (!isset($HTTP_GET_VARS['nID'])) break;

case 'send':

case 'confirm_send':

$newsletter_id = tep_db_prepare_input($HTTP_GET_VARS['nID']);

$check_query = tep_db_query("select locked from " . TABLE_NEWSLETTERS . " where newsletters_id = '" . (int)$newsletter_id . "'");

$check = tep_db_fetch_array($check_query);

if ($check['locked'] < 1) {

switch ($action) {

case 'delete': $error = ERROR_REMOVE_UNLOCKED_NEWSLETTER; break;

case 'new': $error = ERROR_EDIT_UNLOCKED_NEWSLETTER; break;

case 'send': $error = ERROR_SEND_UNLOCKED_NEWSLETTER; break;

case 'confirm_send': $error = ERROR_SEND_UNLOCKED_NEWSLETTER; break;

}

$messageStack->add_session($error, 'error');

tep_redirect(tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $HTTP_GET_VARS['nID']));

}

break;

}

?>

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

<head>

</head>

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

<tr>

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

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

</table></td>

<tr>

<tr>

</tr>

</table></td>

</tr>

<?php

if ($action == 'new') {

$form_action = 'insert';

$parameters = array('title' => '',

'content' => '',

'module' => '');

$nInfo = new objectInfo($parameters);

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

$form_action = 'update';

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

$newsletter_query = tep_db_query("select title, content, module from " . TABLE_NEWSLETTERS . " where newsletters_id = '" . (int)$nID . "'");

$newsletter = tep_db_fetch_array($newsletter_query);

$nInfo->objectInfo($newsletter);

} elseif ($HTTP_POST_VARS) {

$nInfo->objectInfo($HTTP_POST_VARS);

}

$file_extension = substr($PHP_SELF, strrpos($PHP_SELF, '.'));

$directory_array = array();

if ($dir = dir(DIR_WS_MODULES . 'newsletters/')) {

while ($file = $dir->read()) {

if (!is_dir(DIR_WS_MODULES . 'newsletters/' . $file)) {

if (substr($file, strrpos($file, '.')) == $file_extension) {

$directory_array[] = $file;

}

sort($directory_array);

$dir->close();

}

for ($i=0, $n=sizeof($directory_array); $i<$n; $i++) {

$modules_array[] = array('id' => substr($directory_array[$i], 0, strrpos($directory_array[$i], '.')), 'text' => substr($directory_array[$i], 0, strrpos($directory_array[$i], '.')));

}

?>

<tr>

</tr>

<tr>

<td class="main"><?php echo tep_draw_pull_down_menu('module', $modules_array, $nInfo->module); ?></td>

</tr>

<tr>

</tr>

<tr>

<td class="main"><?php echo tep_draw_input_field('title', $nInfo->title, '', true); ?></td>

</tr>

<tr>

</tr>

<tr>

<td class="main"><?php echo tep_draw_textarea_field('content', 'soft', '100%', '20', $nInfo->content); ?></td>

</tr>

</table></td>

</tr>

<tr>

</tr>

<tr>

<tr>

<td class="main" align="right"><?php echo (($form_action == 'insert') ? tep_image_submit('button_save.gif', IMAGE_SAVE) : tep_image_submit('button_update.gif', IMAGE_UPDATE)). ' <a href="' . tep_href_link(FILENAME_NEWSLETTERS, (isset($HTTP_GET_VARS['page']) ? 'page=' . $HTTP_GET_VARS['page'] . '&' : '') . (isset($HTTP_GET_VARS['nID']) ? 'nID=' . $HTTP_GET_VARS['nID'] : '')) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>'; ?></td>

</tr>

</table></td>

</form></tr>

<?php

} elseif ($action == 'preview') {

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

$newsletter_query = tep_db_query("select title, content, module from " . TABLE_NEWSLETTERS . " where newsletters_id = '" . (int)$nID . "'");

$newsletter = tep_db_fetch_array($newsletter_query);

$nInfo = new objectInfo($newsletter);

?>

<tr>

<td align="right"><?php echo '<a href="' . tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $HTTP_GET_VARS['nID']) . '">' . tep_image_button('button_back.gif', IMAGE_BACK) . '</a>'; ?></td>

</tr>

<tr>

<td><tt><?php echo nl2br($nInfo->content); ?></tt></td>

</tr>

<tr>

<td align="right"><?php echo '<a href="' . tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $HTTP_GET_VARS['nID']) . '">' . tep_image_button('button_back.gif', IMAGE_BACK) . '</a>'; ?></td>

</tr>

<?php

} elseif ($action == 'send') {

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

$newsletter_query = tep_db_query("select title, content, module from " . TABLE_NEWSLETTERS . " where newsletters_id = '" . (int)$nID . "'");

$newsletter = tep_db_fetch_array($newsletter_query);

$nInfo = new objectInfo($newsletter);

include(DIR_WS_LANGUAGES . $language . '/modules/newsletters/' . $nInfo->module . substr($PHP_SELF, strrpos($PHP_SELF, '.')));

include(DIR_WS_MODULES . 'newsletters/' . $nInfo->module . substr($PHP_SELF, strrpos($PHP_SELF, '.')));

$module_name = $nInfo->module;

$module = new $module_name($nInfo->title, $nInfo->content);

?>

<tr>

<td><?php if ($module->show_choose_audience) { echo $module->choose_audience(); } else { echo $module->confirm(); } ?></td>

</tr>

<?php

} elseif ($action == 'confirm') {

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

$newsletter_query = tep_db_query("select title, content, module from " . TABLE_NEWSLETTERS . " where newsletters_id = '" . (int)$nID . "'");

$newsletter = tep_db_fetch_array($newsletter_query);

$nInfo = new objectInfo($newsletter);

include(DIR_WS_LANGUAGES . $language . '/modules/newsletters/' . $nInfo->module . substr($PHP_SELF, strrpos($PHP_SELF, '.')));

include(DIR_WS_MODULES . 'newsletters/' . $nInfo->module . substr($PHP_SELF, strrpos($PHP_SELF, '.')));

$module_name = $nInfo->module;

$module = new $module_name($nInfo->title, $nInfo->content);

?>

<tr>

<td><?php echo $module->confirm(); ?></td>

</tr>

<?php

} elseif ($action == 'confirm_send') {

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

$newsletter_query = tep_db_query("select newsletters_id, title, content, module from " . TABLE_NEWSLETTERS . " where newsletters_id = '" . (int)$nID . "'");

$newsletter = tep_db_fetch_array($newsletter_query);

$nInfo = new objectInfo($newsletter);

include(DIR_WS_LANGUAGES . $language . '/modules/newsletters/' . $nInfo->module . substr($PHP_SELF, strrpos($PHP_SELF, '.')));

include(DIR_WS_MODULES . 'newsletters/' . $nInfo->module . substr($PHP_SELF, strrpos($PHP_SELF, '.')));

$module_name = $nInfo->module;

$module = new $module_name($nInfo->title, $nInfo->content);

?>

<tr>

<tr>

</tr>

</table></td>

</tr>

<?php

tep_set_time_limit(0);

flush();

$module->send($nInfo->newsletters_id);

?>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

<td><?php echo '<a href="' . tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $HTTP_GET_VARS['nID']) . '">' . tep_image_button('button_back.gif', IMAGE_BACK) . '</a>'; ?></td>

</tr>

<?php

} else {

?>

<tr>

<tr>

</tr>

<?php

$newsletters_query_raw = "select newsletters_id, title, length(content) as content_length, module, date_added, date_sent, status, locked from " . TABLE_NEWSLETTERS . " order by date_added desc";

$newsletters_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $newsletters_query_raw, $newsletters_query_numrows);

$newsletters_query = tep_db_query($newsletters_query_raw);

while ($newsletters = tep_db_fetch_array($newsletters_query)) {

if ((!isset($HTTP_GET_VARS['nID']) || (isset($HTTP_GET_VARS['nID']) && ($HTTP_GET_VARS['nID'] == $newsletters['newsletters_id']))) && !isset($nInfo) && (substr($action, 0, 3) != 'new')) {

$nInfo = new objectInfo($newsletters);

}

if (isset($nInfo) && is_object($nInfo) && ($newsletters['newsletters_id'] == $nInfo->newsletters_id) ) {

echo ' <tr id="defaultSelected" class="dataTableRowSelected" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $nInfo->newsletters_id . '&action=preview') . '\'">' . "\n";

} else {

echo ' <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $newsletters['newsletters_id']) . '\'">' . "\n";

}

?>

<td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $newsletters['newsletters_id'] . '&action=preview') . '">' . tep_image(DIR_WS_ICONS . 'preview.gif', ICON_PREVIEW) . '</a> ' . $newsletters['title']; ?></td>

<td class="dataTableContent" align="center"><?php if ($newsletters['locked'] > 0) { echo tep_image(DIR_WS_ICONS . 'locked.gif', ICON_LOCKED); } else { echo tep_image(DIR_WS_ICONS . 'unlocked.gif', ICON_UNLOCKED); } ?></td>

<td class="dataTableContent" align="right"><?php if (isset($nInfo) && is_object($nInfo) && ($newsletters['newsletters_id'] == $nInfo->newsletters_id) ) { echo tep_image(DIR_WS_IMAGES . 'icon_arrow_right.gif', ''); } else { echo '<a href="' . tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $newsletters['newsletters_id']) . '">' . tep_image(DIR_WS_IMAGES . 'icon_info.gif', IMAGE_ICON_INFO) . '</a>'; } ?> </td>

</tr>

<?php

}

?>

<tr>

<tr>

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

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

</tr>

<tr>

<td align="right" colspan="2"><?php echo '<a href="' . tep_href_link(FILENAME_NEWSLETTERS, 'action=new') . '">' . tep_image_button('button_new_newsletter.gif', IMAGE_NEW_NEWSLETTER) . '</a>'; ?></td>

</tr>

</table></td>

</tr>

</table></td>

<?php

$heading = array();

$contents = array();

switch ($action) {

case 'delete':

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

$contents = array('form' => tep_draw_form('newsletters', FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $nInfo->newsletters_id . '&action=deleteconfirm'));

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

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

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

break;

default:

if (is_object($nInfo)) {

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

if ($nInfo->locked > 0) {

$contents[] = array('align' => 'center', 'text' => '<a href="' . tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $nInfo->newsletters_id . '&action=new') . '">' . tep_image_button('button_edit.gif', IMAGE_EDIT) . '</a> <a href="' . tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $nInfo->newsletters_id . '&action=delete') . '">' . tep_image_button('button_delete.gif', IMAGE_DELETE) . '</a> <a href="' . tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $nInfo->newsletters_id . '&action=preview') . '">' . tep_image_button('button_preview.gif', IMAGE_PREVIEW) . '</a> <a href="' . tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $nInfo->newsletters_id . '&action=send') . '">' . tep_image_button('button_send.gif', IMAGE_SEND) . '</a> <a href="' . tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $nInfo->newsletters_id . '&action=unlock') . '">' . tep_image_button('button_unlock.gif', IMAGE_UNLOCK) . '</a>');

} else {

$contents[] = array('align' => 'center', 'text' => '<a href="' . tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $nInfo->newsletters_id . '&action=preview') . '">' . tep_image_button('button_preview.gif', IMAGE_PREVIEW) . '</a> <a href="' . tep_href_link(FILENAME_NEWSLETTERS, 'page=' . $HTTP_GET_VARS['page'] . '&nID=' . $nInfo->newsletters_id . '&action=lock') . '">' . tep_image_button('button_lock.gif', IMAGE_LOCK) . '</a>');

}

$contents[] = array('text' => '<br>' . TEXT_NEWSLETTER_DATE_ADDED . ' ' . tep_date_short($nInfo->date_added));

if ($nInfo->status == '1') $contents[] = array('text' => TEXT_NEWSLETTER_DATE_SENT . ' ' . tep_date_short($nInfo->date_sent));

}

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>

<?php

}

?>

</table></td>

</tr>

</table>

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

<br>

</body>

</html>

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

February 7, 2009

This has been answered so many times before - try to search the forum before posting -nobody has the time to look through pages of code you've randomly posted without even spending the time to try helping yourself.

Hint:use google and type site:www.oscommerce.com/forums 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'

SQL Syntax Error In Newletter Module

Recommended Posts

wehttam37

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Archived

Browse

Activity