Jump to content
dev osCommerce
Forum
  • Checkout
  • Login
  • Contact Us
  • Get in touch

osCommerce

The e-commerce.

New Demo
Our Partners

Code Help Needed!

MagickWomyn

By MagickWomyn
in General Support

Recommended Posts

MagickWomyn

MagickWomyn

Posted
Posted

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

Monika in Germany

♥Monika in Germany

Posted
Posted

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

MagickWomyn

MagickWomyn

Posted
  • Author
Posted
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!

Monika in Germany

♥Monika in Germany

Posted
Posted

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

MagickWomyn

MagickWomyn

Posted
  • Author
Posted
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.

Monika in Germany

♥Monika in Germany

Posted
Posted

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

Monika in Germany

♥Monika in Germany

Posted
Posted

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

MagickWomyn

MagickWomyn

Posted
  • Author
Posted
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.

Monika in Germany

♥Monika in Germany

Posted
Posted

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

MagickWomyn

MagickWomyn

Posted
  • Author
Posted
lol ... you lost me. What did you change???

I replaced the $where in the last line that I had so brilliantly removed. :-"

And I thought I was lost! lol

Monika in Germany

♥Monika in Germany

Posted
Posted

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

Archived

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

Go to topic listing
×
×
  • Create New...