Guest Posted December 10, 2002 Posted December 10, 2002 Hi All! I have been working on a new printable catalog contribution and have hit a brick wall.......I got the query working to pull all of the products and arrange them by categories (YEAH!!!) and I'm now attempting to split the results into shorter pages. Using the featured products section as a model I used the query that was working previously and changed all instances of $featured_products to $catalog_printable and now it won't work. Please point me in the right direction....... 1064 - You have an error in your SQL syntax near 'SELECT p.products_id, p.products_image, pd.products_description, p.products_mode' at line 1 select count(*) as total SELECT p.products_id, p.products_image, pd.products_description, p.products_model, p.products_quantity, pd.products_name, p.manufacturers_id, p.products_price, c.parent_id, cd.categories_name, 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, categories c, categories_description cd, 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 p2c.categories_id=c.categories_id AND c.categories_id=cd.categories_id AND pd.language_id = 1 ORDER BY c.parent_id ASC, cd.categories_name, pd.products_name
burt Posted December 10, 2002 Posted December 10, 2002 select count(*) as total SELECT p.products_id, 2 selects is not good ;) HTH
Guest Posted December 10, 2002 Posted December 10, 2002 Hi Gary.... I know I'm having a real stupid attack...Would you mind pointing me to where the second SELECT is being added? I have looked at the split_page_results.php.....I tried removing the SELECT from my query and still get an error. <?php $catalog_printable_query_raw = "SELECT p.products_id, p.products_image, pd.products_description, p.products_model, p.products_quantity, pd.products_name, p.manufacturers_id, p.products_price, c.parent_id, cd.categories_name, 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 " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_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 p2c.categories_id=c.categories_id AND c.categories_id=cd.categories_id AND pd.language_id = 1 ORDER BY c.parent_id ASC, cd.categories_name, pd.products_name"; $catalog_printable_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_CATALOG_PRINTABLE_LISTING, $catalog_printable_query_raw, $catalog_printable_numrows); $catalog_printable_query = tep_db_query($catalog_printable_query_raw); while ($catalog_printable = tep_db_fetch_array($catalog_printable_query)) { $catalog_printable_array[] = array('id' => $catalog_printable['products_id'], 'image' => $catalog_printable['products_image'], 'description' => $catalog_printable['products_description'], 'model' => $catalog_printable['products_model'], 'quantity' => $catalog_printable['products_quantity'], 'name' => $catalog_printable['products_name'], 'manufacturers_id' => $catalog_printable['manufacturers_id'], 'price' => $catalog_printable['products_price'], 'parent_id' => $catalog_printable['parent_id'], 'categories' => $catalog_printable['catagories_name'], 'specials_price' => $catalog_printable['specials_new_products_price']); } require(DIR_WS_MODULES . FILENAME_PRINTABLE_CATALOG); ?> </td> </tr> 1064 - You have an error in your SQL syntax near 'p.products_id, p.products_image, pd.products_description, p.products_model, p.pr' at line 1 select count(*) as total p.products_id, p.products_image, pd.products_description, p.products_model, p.products_quantity, pd.products_name, p.manufacturers_id, p.products_price, c.parent_id, cd.categories_name, 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, categories c, categories_description cd, 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 p2c.categories_id=c.categories_id AND c.categories_id=cd.categories_id AND pd.language_id = 1 ORDER BY c.parent_id ASC, cd.categories_name, pd.products_name
remixstore Posted November 8, 2005 Posted November 8, 2005 What about this error ?? I get this when navigating my categories on the user side... it seems to be in the product info box 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 'order by pd.products_name' at line 1 select count(p.products_id) as total order by pd.products_name [TEP STOP] code shown below <?php /* $Id: product_info.php,v 1.97 2003/07/01 14:34:54 hpdl Exp $ osCommerce, Open Source E-Commerce Solutions http://www.oscommerce.com Copyright (c) 2003 osCommerce Released under the GNU General Public License */ require('includes/application_top.php'); require(DIR_WS_LANGUAGES . $language . '/' . FILENAME_PRODUCT_INFO); $product_check_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'"); $product_check = tep_db_fetch_array($product_check_query); ?> <!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> <base href="<?php echo (($request_type == 'SSL') ? HTTPS_SERVER : HTTP_SERVER) . DIR_WS_CATALOG; ?>"> <link rel="stylesheet" type="text/css" href="stylesheet.css"> <script language="javascript"><!-- function popupWindow(url) { window.open(url,'popupWindow','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=no,res izable=yes,copyhistory=no,width=100,height=100,screenX=150,screenY=150,top=150,le ft=150') } //--></script> </head> <body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0"> <!-- header //--> <?php require(DIR_WS_INCLUDES . 'header.php'); ?> <!-- header_eof //--> <!-- body //--> <table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <!-- body_text //--> <td width="100%" valign="top"><?php echo tep_draw_form('cart_quantity', tep_href_link(FILENAME_PRODUCT_INFO, tep_get_all_get_params(array('action')) . 'action=add_product')); ?> <table cellspacing=0 cellpadding=0> <tr><td background=images/m27.gif width=503 height=36> <table cellspacing=0 cellpadding=0> <tr><td colspan=2 height=10></td></tr> <tr><td width=52></td><td class=tx3>PRODUCT</td></tr> <tr><td colspan=2 height=8></td></tr> </table> </td></tr> </table> <table border="0" width="100%" cellspacing="0" cellpadding="0"> <?php if ($product_check['total'] < 1) { ?> <tr> <td><?php new infoBox(array(array('text' => TEXT_PRODUCT_NOT_FOUND))); ?></td> </tr> <tr> <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td> </tr> <tr> <td><table border="0" width="100%" cellspacing="1" cellpadding="2" class="infoBox"> <tr class="infoBoxContents"> <td><table border="0" width="100%" cellspacing="0" cellpadding="2"> <tr> <td width="10"><?php echo tep_draw_separator('pixel_trans.gif', '10', '1'); ?></td> <td align="right"><?php echo '<a href="' . tep_href_link(FILENAME_DEFAULT) . '">' . tep_image_button('button_continue.gif', IMAGE_BUTTON_CONTINUE) . '</a>'; ?></td> <td width="10"><?php echo tep_draw_separator('pixel_trans.gif', '10', '1'); ?></td> </tr> </table></td> </tr> </table></td> </tr> <?php } else { $product_info_query = tep_db_query("select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, pd.products_url, p.products_price, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'"); $product_info = tep_db_fetch_array($product_info_query); tep_db_query("update " . TABLE_PRODUCTS_DESCRIPTION . " set products_viewed = products_viewed+1 where products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and language_id = '" . (int)$languages_id . "'"); if ($new_price = tep_get_products_special_price($product_info['products_id'])) { $products_price = '<s style="color:#777777; font-size:12px">' . $currencies->display_price($product_info['products_price'], tep_get_tax_rate($product_info['products_tax_class_id'])) . '</s> <span>' . $currencies->display_price($new_price, tep_get_tax_rate($product_info['products_tax_class_id'])) . '</span>'; } else { $products_price = $currencies->display_price($product_info['products_price'], tep_get_tax_rate($product_info['products_tax_class_id'])); } if (tep_not_null($product_info['products_model'])) { $products_name = $product_info['products_name'] . '<br><span class="smallText">[' . $product_info['products_model'] . ']</span>'; } else { $products_name = $product_info['products_name']; } ?> <tr><td class=bg2> <table cellspacing=0 cellpadding=0 width=460 align=center> <tr><td width=236 align=center valign=center> <?php if (tep_not_null($product_info['products_image'])) { ?> <table cellspacing="0" cellpadding="2" align="center"> <tr> <td align="center" class="smallText"> <script language="javascript"><!-- document.write('<?php echo '<a href="javascript:popupWindow(\\\'' . tep_href_link(FILENAME_POPUP_IMAGE, 'pID=' . $product_info['products_id']) . '\\\')">' . tep_image(DIR_WS_IMAGES . $product_info['products_image'], addslashes($product_info['products_name']), SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT, 'class=br hspace="5" vspace="5"') . '<br>' . TEXT_CLICK_TO_ENLARGE . '</a>'; ?>'); //--></script> <noscript> <?php echo '<a href="' . tep_href_link(DIR_WS_IMAGES . $product_info['products_image']) . '" target="_blank">' . tep_image(DIR_WS_IMAGES . $product_info['products_image'], $product_info['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT, ' class=br hspace="5" vspace="5"') . '<br>' . TEXT_CLICK_TO_ENLARGE . '</a>'; ?> </noscript> </td> </tr> </table> <!-- Category Fields //--> <!-- Category Fields EOF //--> </td> <td width=197 valign=top> <table cellspacing=0 cellpadding=0> <tr><td height=15></td></tr> <tr><td class=tx1><?=$products_name?></td></tr> <tr><td height=5></td></tr> <tr><td align=center><img src=images/m56.gif width=192 height=1></td></tr> <tr><td height=5></td></tr> <tr><td class=tx><b>Item #:</b> <?= $products_id; ?></td></tr> <tr><td height=5></td></tr> <tr><td class=tx> <? if ($product_info['products_date_available'] > date('Y-m-d H:i:s')) echo sprintf(TEXT_DATE_AVAILABLE, tep_date_long($product_info['products_date_available'])); else echo sprintf(TEXT_DATE_ADDED, tep_date_long($product_info['products_date_added'])); ?> </td></tr> </table> </td> </tr> </table> <table cellspacing=0 cellpadding=0 width=460 align=center> <tr> <td height=17></td> </tr> <tr> <td height=17><b>Item Description</b></td> </tr> <tr> <td height=17><?php echo stripslashes($product_info['products_description']); ?></td> </tr> <tr> <td height=17></td> </tr> <tr> <td height=17><table border="0" cellspacing="0" cellpadding="2"> <?php } $extra_fields_query = tep_db_query("SELECT pef.products_extra_fields_name as name, ptf.products_extra_fields_value as value FROM ". TABLE_PRODUCTS_EXTRA_FIELDS ." pef LEFT JOIN ". TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS ." ptf ON ptf.products_extra_fields_id=pef.products_extra_fields_id WHERE pef.products_extra_fields_status = 1 and ptf.products_id=". (int)$products_id ." ORDER BY products_extra_fields_order"); while ($extra_fields = tep_db_fetch_array($extra_fields_query)) { if ($extra_fields['value'] != '') { ?> <tr> <td class="main" valign="top"><b><?php echo $extra_fields['name']; ?>:</b></td> <td class="main"><?php echo $extra_fields['value']; ?><br/></td> </tr> <? } } ?> </table></td> </tr> <tr><td height=10></td></tr> <tr><td> </td></tr> <tr><td height=10></td></tr> </table> <table cellspacing=0 cellpadding=0> <tr><td><img src=images/m58.gif width=503 height=1></td></tr> <tr><td height=10></td></tr> </table> <table cellspacing=0 cellpadding=0 width=460 align=center> <tr><td width=380 align=right class=tx2><?php echo $products_price; ?></td><td align=right><?php echo tep_draw_hidden_field('products_id', $product_info['products_id']) . tep_image_submit('button_in_cart.gif', IMAGE_BUTTON_BUY_NOW); ?></td></tr> <tr><td height=10></td></tr> </table> <table cellspacing=0 cellpadding=0> <tr><td><img src=images/m31.gif width=503 height=6></td></tr> </table> </td></tr> <tr><td> <?php $products_attributes_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_ATTRIBUTES . " patrib where patrib.products_id='" . (int)$HTTP_GET_VARS['products_id'] . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . (int)$languages_id . "'"); $products_attributes = tep_db_fetch_array($products_attributes_query); if ($products_attributes['total'] > 0) { ?> <table border="0" cellspacing="0" cellpadding="2"> <tr> <td class="main" colspan="2"><?php echo TEXT_PRODUCT_OPTIONS; ?></td> </tr> <?php $products_options_name_query = tep_db_query("select distinct popt.products_options_id, popt.products_options_name from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_ATTRIBUTES . " patrib where patrib.products_id='" . (int)$HTTP_GET_VARS['products_id'] . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . (int)$languages_id . "' order by popt.products_options_name"); while ($products_options_name = tep_db_fetch_array($products_options_name_query)) { $products_options_array = array(); $products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'"); while ($products_options = tep_db_fetch_array($products_options_query)) { $products_options_array[] = array('id' => $products_options['products_options_values_id'], 'text' => $products_options['products_options_values_name']); if ($products_options['options_values_price'] != '0') { $products_options_array[sizeof($products_options_array)-1]['text'] .= ' (' . $products_options['price_prefix'] . $currencies->display_price($products_options['options_values_price'], tep_get_tax_rate($product_info['products_tax_class_id'])) .') '; } } if (isset($cart->contents[$HTTP_GET_VARS['products_id']]['attributes'][$products_options_name['products_options_id']])) { $selected_attribute = $cart->contents[$HTTP_GET_VARS['products_id']]['attributes'][$products_options_name['products_options_id']]; } else { $selected_attribute = false; } ?> <tr> <td class="main"><?php echo $products_options_name['products_options_name'] . ':'; ?></td> <td class="main"><?php echo tep_draw_pull_down_menu('id[' . $products_options_name['products_options_id'] . ']', $products_options_array, $selected_attribute); ?></td> </tr> <?php } ?> </table> <?php } ?> </td> </tr> <tr> <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td> </tr> <?php $reviews_query = tep_db_query("select count(*) as count from " . TABLE_REVIEWS . " where products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "'"); $reviews = tep_db_fetch_array($reviews_query); if ($reviews['count'] > 0) { ?> <tr> <td class="main"><?php echo TEXT_CURRENT_REVIEWS . ' ' . $reviews['count']; ?></td> </tr> <tr> <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td> </tr> <?php } if (tep_not_null($product_info['products_url'])) { ?> <tr> <td class="main"><?php echo sprintf(TEXT_MORE_INFORMATION, tep_href_link(FILENAME_REDIRECT, 'action=url&goto=' . urlencode($product_info['products_url']), 'NONSSL', true, false)); ?></td> </tr> <tr> <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td> </tr> <?php } ?> <!-- <tr> <td><table border="0" width="100%" cellspacing="1" cellpadding="2" class="infoBox"> <tr class="infoBoxContents"> <td><table border="0" width="100%" cellspacing="0" cellpadding="2"> <tr> <td width="10"><?php echo tep_draw_separator('pixel_trans.gif', '10', '1'); ?></td> <td class="main"><?php echo '<a href="' . tep_href_link(FILENAME_PRODUCT_REVIEWS, tep_get_all_get_params()) . '">' . tep_image_button('button_reviews.gif', IMAGE_BUTTON_REVIEWS) . '</a>'; ?></td> <td class="main" align="right"><?php echo tep_draw_hidden_field('products_id', $product_info['products_id']) . tep_image_submit('button_in_cart.gif', IMAGE_BUTTON_IN_CART); ?></td> <td width="10"><?php echo tep_draw_separator('pixel_trans.gif', '10', '1'); ?></td> </tr> </table></td> </tr> </table></td> </tr> <tr> <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td> </tr> --> <tr> <td> <?php /* if ((USE_CACHE == 'true') && empty($SID)) { echo tep_cache_also_purchased(3600); } else { include(DIR_WS_MODULES . FILENAME_ALSO_PURCHASED_PRODUCTS); } */ } ?> </td></tr> </table> </form></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'); ?>
postma Posted November 9, 2005 Posted November 9, 2005 What about this error ?? I get this when navigating my categories on the user side... it seems to be in the product info box code shown below It seems like you need the PHP5 and MYSQL5 contribution.
remixstore Posted November 10, 2005 Posted November 10, 2005 http://mastershot.tv/shop/index.php i'll look that up... I guess it can't get worse. JanZ thinkss it's in catalog/includes/classes/split_page_results.php. http://www.oscommerce.com/forums/index.php?showtopic=179198 http://www.oscommerce.com/forums/index.php?showtopic=179885 http://www.oscommerce.com/forums/index.php?showtopic=179633
New 
Recommended Posts
Archived
This topic is now archived and is closed to further replies.