Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Code Help Needed!


MagickWomyn

Recommended Posts

I have All Products installed and working on a local test site. I'm very happy with it.

 

I'm trying to integrate All Prods with Category Enable/Disable. The problem is the category can be disabled, but the products are still enabled. I want to avoid having to manually disable the products in those categories.

 

I'm trying to add a category query.. ie.. category_status = '1' to the All Prods listing sql. Can anyone help me find a way to do this? I'm learning as I go, so please be gentle.

 

This is the listing sql:

$listing_sql = "select p.products_id, p.products_model, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id  $where order by pd.products_name";

Link to comment
Share on other sites

try this, the red part is the part added ... I used categories_status, double check your column name

 

$listing_sql = "select p.products_id, p.products_model, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id

 

, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and c.categories_status = '1'

 

$where order by pd.products_name";

 

check the code in your $where clause, it probably starts with the word "where" ... change that to "and"

:-)

Monika

 

addicted to writing code ... can't get enough of databases either, LOL!

 

my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum

 

Interactive Media Award July 2007 ~ category E-Commerce

my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...

Link to comment
Share on other sites

try this, the red part is the part added ... I used categories_status, double check your column name

check the code in your $where clause, it probably starts with the word "where" ... change that to "and"

 

I get this error using the code below.

 

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 count(p.products_id) as total from products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join products_description pd on p.products_id = pd.products_id and pd.language_id = '1' left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c where p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and c.categories_status = '1' and

 

[TEP STOP]

$listing_sql = "select p.products_id, p.products_model, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and c.categories_status = '1' and order by pd.products_name";

 

If I change the code to :

$listing_sql = "select p.products_id, p.products_model, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and c.categories_status = '1' order by pd.products_name";

 

The page displays, but will not let me select any products by alphabetical letter. The page remains the same no matter which letter I choose.

 

It's progress... at least I see the page now. Any ideas for the latest problem?

I do appreciate the help. Thank You!

Link to comment
Share on other sites

lol ... if you do not give me all code, we will have a problem :-)

 

show me how that where clause is generated....

:-)

Monika

 

addicted to writing code ... can't get enough of databases either, LOL!

 

my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum

 

Interactive Media Award July 2007 ~ category E-Commerce

my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...

Link to comment
Share on other sites

lol ... if you do not give me all code, we will have a problem :-)

 

show me how that where clause is generated....

Sorry about that chief :-" Here's the complete file.

<?php 
/* 
$Id: allprods.php,v 4.4 2006/09/18 20:28:47 Mgx Co. Exp $


All Products v4.3 MS 2.2 with Images [url="http://www.oscommerce.com/community/contributions,1501"]http://www.oscommerce.com/community/contributions,1501[/url]

osCommerce, Open Source E-Commerce Solutions
[url="http://www.oscommerce.com"]http://www.oscommerce.com[/url]

Copyright © 2004 osCommerce

Released under the GNU General Public License

*/ 

require('includes/application_top.php'); 
include(DIR_WS_LANGUAGES . $language . '/' . FILENAME_ALLPRODS); 

$breadcrumb->add(HEADING_TITLE, tep_href_link(FILENAME_ALLPRODS, '', 'NONSSL')); 

$firstletter=$HTTP_GET_VARS['fl'];
if (!$HTTP_GET_VARS['page']){
 $where="where pd.products_name like '$firstletter%' AND p.products_status='1' ";
}else {
 $where="where pd.products_name like '$firstletter%' AND p.products_status='1' ";
} 


?> 
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html <?php echo HTML_PARAMS; ?>> 
<head> 
<?php
// BOF: WebMakers.com Changed: Header Tag Controller v2.55 
// Replaced by header_tags.php 
if ( file_exists(DIR_WS_INCLUDES . 'header_tags.php') ) { 
 require(DIR_WS_INCLUDES . 'header_tags.php'); 
} else { 
?> 
 <title><?php echo TITLE ?></title> 
<?php 
} 
// EOF: WebMakers.com Changed: Header Tag Controller v1.0
?> 
<base href="<?php echo (getenv('HTTPS') == 'on' ? HTTPS_SERVER : HTTP_SERVER) . DIR_WS_CATALOG; ?>"> 
<link rel="stylesheet" type="text/css" href="stylesheet.css">
</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="3" cellpadding="3"> 
<tr> 
  <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2"> 
<!-- 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="0">
    <tr>
      <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
        <tr>
         <?php if ( file_exists(DIR_WS_INCLUDES . 'header_tags.php') ) {?> 
          <td><h1><?php echo HEADING_TITLE; ?></h1></td>
         <?php } else { ?>           
          <td class="pageHeading"><?php echo HEADING_TITLE; ?></td>
         <?php } ?>
          <td class="pageHeading" align="right"><?php echo tep_image(DIR_WS_IMAGES . 'table_background_products_new.gif', HEADING_TITLE, HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
        </tr>
        <tr>
          <td class="main"><?php echo HEADING_SUB_TEXT; ?></td>
        </tr>           
        <tr>
         <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
        </tr>
      </table></td>
    </tr>
    <tr>
      <td align="center" class="smallText"><?php $firstletter_nav=
       '<a href="' . tep_href_link("allprods.php",  'fl=A', 'NONSSL') . '"> A |</A>' . 
       '<a href="' . tep_href_link("allprods.php",  'fl=B', 'NONSSL') . '"> B |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=C', 'NONSSL') . '"> C |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=D', 'NONSSL') . '"> D |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=E', 'NONSSL') . '"> E |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=F', 'NONSSL') . '"> F |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=G', 'NONSSL') . '"> G |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=H', 'NONSSL') . '"> H |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=I', 'NONSSL') . '"> I |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=J', 'NONSSL') . '"> J |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=K', 'NONSSL') . '"> K |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=L', 'NONSSL') . '"> L |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=M', 'NONSSL') . '"> M |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=N', 'NONSSL') . '"> N |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=O', 'NONSSL') . '"> O |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=P', 'NONSSL') . '"> P |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=Q', 'NONSSL') . '"> Q |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=R', 'NONSSL') . '"> R |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=S', 'NONSSL') . '"> S |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=T', 'NONSSL') . '"> T |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=U', 'NONSSL') . '"> U |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=V', 'NONSSL') . '"> V |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=W', 'NONSSL') . '"> W |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=X', 'NONSSL') . '"> X |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=Y', 'NONSSL') . '"> Y |</A>' .
       '<a href="' . tep_href_link("allprods.php",  'fl=Z', 'NONSSL') . '"> Z</A>  '   .
       '<a href="' . tep_href_link("allprods.php",  '',     'NONSSL') . '"> FULL</A>';

       echo $firstletter_nav; ?></td>
    </tr>
   <tr>
   <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
   </tr>
    <tr>
      <td>
<?php
// create column list
$define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,
                     'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,
                     'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER, 
                     'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,
                     'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,
                     'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,
                     'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE,
                     'PRODUCT_LIST_BUY_NOW' => PRODUCT_LIST_BUY_NOW);
asort($define_list);

$column_list = array();
reset($define_list);
while (list($column, $value) = each($define_list)) {
  if ($value) $column_list[] = $column; 
}

$select_column_list = '';

for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
  if ( ($column_list[$col] == 'PRODUCT_LIST_BUY_NOW') || ($column_list[$col] == 'PRODUCT_LIST_NAME') || ($column_list[$col] == 'PRODUCT_LIST_PRICE') ) {
    continue;
  }
}

// listing all products
//Original code below this line
//$listing_sql = "select p.products_id, p.products_model, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id  $where order by pd.products_name";
//test for enable disable categories//
$listing_sql = "select p.products_id, p.products_model, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and c.categories_status = '1' order by pd.products_name";

if (ALL_PRODUCTS_DISPLAY_MODE == 'true')
include(DIR_WS_MODULES . 'product_listing.php'); //display in standard format
else
include(DIR_WS_MODULES . 'allprods.php');
?>
      </td>
    </tr>
  </table></td>
<!-- body_text_eof //-->
  <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2">
<!-- right_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_right.php'); ?>
<!-- right_navigation_eof //-->
  </table></td>
</tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
<br>
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

 

Thanks again for taking the time to help.

Link to comment
Share on other sites

ugh .... can you please repost. Either use no formatting or the quote formatting

:-)

Monika

 

addicted to writing code ... can't get enough of databases either, LOL!

 

my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum

 

Interactive Media Award July 2007 ~ category E-Commerce

my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...

Link to comment
Share on other sites

I just saw that in my forum email formatting was fine, so I can work from that.

 

lease teh listing_sql the way I have it up. I think you messed up the $where edit right at the very top, it should be

 

 

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

 

$where=" and pd.products_name like '$firstletter%' AND p.products_status='1' ";

 

}else {

 

$where=" and pd.products_name like '$firstletter%' AND p.products_status='1' ";

 

}

:-)

Monika

 

addicted to writing code ... can't get enough of databases either, LOL!

 

my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum

 

Interactive Media Award July 2007 ~ category E-Commerce

my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...

Link to comment
Share on other sites

I just saw that in my forum email formatting was fine, so I can work from that.

 

lease teh listing_sql the way I have it up. I think you messed up the $where edit right at the very top, it should be

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

 

$where=" and pd.products_name like '$firstletter%' AND p.products_status='1' ";

 

}else {

 

$where=" and pd.products_name like '$firstletter%' AND p.products_status='1' ";

 

}

I changed the top lines as indicated and used your original code. I got an error message.

When I changed the listing query to:

 

$listing_sql = "select p.products_id, p.products_model, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and c.categories_status = '1' $where order by pd.products_name";

 

Everything worked fine. Products in the disabled categories no longer display. Products listed in alphabetical display just fine. :thumbsup:

 

I'll post the code changes in the All Products Contrib support area so others may benefit from your expertise. Thank you very much for your help.

Link to comment
Share on other sites

lol ... you lost me. What did you change???

:-)

Monika

 

addicted to writing code ... can't get enough of databases either, LOL!

 

my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum

 

Interactive Media Award July 2007 ~ category E-Commerce

my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...

Link to comment
Share on other sites

hilarious ... I had compared my code to yours and they were identical ;-)

:-)

Monika

 

addicted to writing code ... can't get enough of databases either, LOL!

 

my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum

 

Interactive Media Award July 2007 ~ category E-Commerce

my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...