Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

x-sell maximum products shown


Guest

Recommended Posts

at present in my admin panel, i have it set to 12, but only 3 show on any given product, even if there's 1000 products attatched.

 

code:

<?php
/* 
$Id: xsell_products.php, v1  2002/09/11

osCommerce, Open Source E-Commerce Solutions 
<http://www.oscommerce.com> 

Copyright (c) 2002 osCommerce 

Released under the GNU General Public License 
*/ 

/* if product given */

if ($HTTP_GET_VARS['products_id']) { 

/* obtain the xsell product */

$xsell_query = tep_db_query("select distinct p.products_id, 
										 p.products_image, 
										 pd.products_name 
						 from " . TABLE_PRODUCTS_XSELL . " xp,
							  " . TABLE_PRODUCTS . " p,
							  " . TABLE_PRODUCTS_DESCRIPTION . " pd 
						 where xp.products_id = '" . $HTTP_GET_VARS['products_id'] . "' and 
							   xp.xsell_id = p.products_id and 
							   p.products_id = pd.products_id and 
							   pd.language_id = '" . $languages_id . "' and 
							   p.products_status = '1' 
						 order by xp.products_id asc 
						 limit " . MAX_DISPLAY_ALSO_PURCHASED); 

$num_products_xsell = tep_db_num_rows($xsell_query); 

/* if we equal or exceed the minimal amount of products */

if ($num_products_xsell >= MIN_DISPLAY_ALSO_PURCHASED) { 

  /* put them in the box */

  $info_box_contents = array();
  $info_box_contents[] = array('align' => 'left', 'text' => TEXT_XSELL_PRODUCTS);
 // new contentBoxHeading($info_box_contents);

  $row = 0;
  $col = 0;
  $info_box_contents = array();
  while ($xsell = tep_db_fetch_array($xsell_query)) {
	$xsell['products_name'] = tep_get_products_name($xsell['products_id']);
	$info_box_contents[$row][$col] = array('align' => 'center',
										   'params' => 'class="smallText" width="33%" valign="top"',
										   'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $xsell['products_id']) . '">' . tep_image(DIR_WS_PRODUCT_IMAGES . $xsell['products_image'], $xsell['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $xsell['products_id']) . '">' . $xsell['products_name'] . '</a>');
	$col ++;
	if ($col > 2) {
	  $col = 0;
	  $row ++;
	}
  }

  /* if we have not enough products to fill the box */

  if ($num_products_xsell < MAX_DISPLAY_ALSO_PURCHASED) {

	/* add some random products from the same category to fill the box */

	$mtm= rand();

	$xsell_cat_query = tep_db_query("select categories_id
							   from " . TABLE_PRODUCTS_TO_CATEGORIES . "
							   where products_id = '" . $HTTP_GET_VARS['products_id'] . "'");
	$xsell_cat_array = tep_db_fetch_array($xsell_cat_query);
	$xsell_category = $xsell_cat_array['categories_id'];
	$new_limit = MAX_DISPLAY_ALSO_PURCHASED - $num_products_xsell;
	$xsell_prod_query = tep_db_query("select distinct p.products_id, 
										 p.products_image, 
										 pd.products_name 
						 from " . TABLE_PRODUCTS . " p,
							  " . TABLE_PRODUCTS_TO_CATEGORIES . " pc,
							  " . TABLE_PRODUCTS_DESCRIPTION . " pd 
						 where pc.categories_id = '" . $xsell_category . "' and
							   p.products_id != '" . $HTTP_GET_VARS['products_id'] . "' and 
							   pc.products_id = p.products_id and 
							   p.products_id = pd.products_id and 
							   pd.language_id = '" . $languages_id . "' and 
							   p.products_status = '1' 
						 order by rand($mtm) desc 
						 limit " . $new_limit);

   while ($xsell = tep_db_fetch_array($xsell_prod_query)) {
	 $xsell['products_name'] = tep_get_products_name($xsell['products_id']);
	 $info_box_contents[$row][$col] = array('align' => 'center',
											'params' => 'class="smallText" width="33%" valign="top"',
											'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $xsell['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $xsell['products_image'], $xsell['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $xsell['products_id']) . '">' . $xsell['products_name'] . '</a>');
	 $col ++;
	 if ($col > 2) {
	   $col = 0;
  //	 $row ++;
	 }
   }
  }
  new contentBox($info_box_contents);
}
else {

  /* there are no xsell products registered at all for this product */

  $info_box_contents = array();
  $info_box_contents[] = array('align' => 'left', 'text' => TEXT_XSELL_PRODUCTS);
//  new contentBoxHeading($info_box_contents);

  $row = 0;
  $col = 0;
  $info_box_contents = array();


  /* fill the box with all random products from the same category */

	$mtm= rand();
	$xsell_cat_query = tep_db_query("select categories_id
									 from " . TABLE_PRODUCTS_TO_CATEGORIES . "
									 where products_id = '" . $HTTP_GET_VARS['products_id'] . "'");

	$xsell_cat_array = tep_db_fetch_array($xsell_cat_query);
	$xsell_category = $xsell_cat_array['categories_id'];
	$new_limit = MAX_DISPLAY_ALSO_PURCHASED - $num_products_xsell;
	$xsell_prod_query = tep_db_query("select distinct p.products_id, 
													  p.products_image, 
													  pd.products_name 
									  from " . TABLE_PRODUCTS . " p,
										   " . TABLE_PRODUCTS_TO_CATEGORIES . " pc,
										   " . TABLE_PRODUCTS_DESCRIPTION . " pd 
									  where pc.categories_id = '" . $xsell_category . "' and 
											pc.products_id = p.products_id and 
											p.products_id != '" . $HTTP_GET_VARS['products_id'] . "' and 
											p.products_id = pd.products_id and 
											pd.language_id = '" . $languages_id . "' and 
											p.products_status = '1' 
									  order by rand($mtm) desc 
									  limit " . MAX_DISPLAY_ALSO_PURCHASED);

   while ($xsell = tep_db_fetch_array($xsell_prod_query)) {
	 $xsell['products_name'] = tep_get_products_name($xsell['products_id']);
	 $info_box_contents[$row][$col] = array('align' => 'center',
											'params' => 'class="smallText" width="33%" valign="top"',
											'text' => '<a class="tiny" href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $xsell['products_id']) . '">' . $xsell['products_name'] . '</a>');
	 $col ++;
	 if ($col > 2) {
	   $col = 0;
   //	$row ++;
	 }
   }
  new contentBox($info_box_contents);
}
}
?>

Link to comment
Share on other sites

under maximum values -> Also Purchased is set to 12

 

you have 1000 xsell products attached to a product ?

 

put after this :

 

$num_products_xsell = tep_db_num_rows($xsell_query);

 

this:

 

echo 'xsell results = '.$num_products_xsell.'<br>';

 

and see what it gives.

Treasurer MFC

Link to comment
Share on other sites

result: xsell results = 0

 

i cleared the entire xsell database because i prefer a more automated approach to the contribution, and your variant does just that, i just can't seem to get more products shown. i don't have any where near 1000 products in the same category, but at most i have maybe 10-20 (i make use of subcategories like crazy)

Link to comment
Share on other sites

result: xsell results = 0

 

i cleared the entire xsell database because i prefer a more automated approach to the contribution, and your variant does just that, i just can't seem to get more products shown. i don't have any where near 1000 products in the same category, but at most i have maybe 10-20 (i make use of subcategories like crazy)

 

maybe uncomment this ?

 

// $row ++;

Treasurer MFC

Link to comment
Share on other sites

all of them?

 

the only one i have uncommented is:

$col ++;

if ($col > 2) {

$col = 0;

$row ++;

}

}

 

but i edited:

$col ++;

if ($col > 2) {

$col = 0;

// $row ++;

}

}

 

no dice.

 

the other instances were already // $row ++;

Link to comment
Share on other sites

all of them?

 

the only one i have uncommented is:

$col ++;

if ($col > 2) {

$col = 0;

$row ++;

}

}

 

but i edited:

$col ++;

if ($col > 2) {

$col = 0;

// $row ++;

}

}

 

no dice.

 

the other instances were already // $row ++;

 

I mean un-comment as in remove the //

 

 

if you have no xsell ocurrences, the last logic is executed.

that will put things in rows and columns.

if the column gets > 2 then it is reset to 0

if you then not increase the row, the next ocurrence will overwrite the first in row 0 etc.

Treasurer MFC

Link to comment
Share on other sites

excellent, that did the trick :D

is it possible to sort in two colums instead of 3? i have relatively long product titles and it's a bit unsightly as 3

Link to comment
Share on other sites

nevermind i'd figured it out :)

 

is it possible to rework this to display any product(s) from the manufacturer (instead of the category) or do you think this would slow page load?

Link to comment
Share on other sites

nevermind i'd figured it out :)

 

is it possible to rework this to display any product(s) from the manufacturer (instead of the category) or do you think this would slow page load?

 

sure, just change the queries for the category id to manufacturer_id and then do the products query with that id instead of the categories id. Should not be any slower I guess.

Treasurer MFC

Link to comment
Share on other sites

eventually i will have 500+ manufacturers and thousands of products (with the only intentions to continue expanding), do you think in this instance replacing your variant with a manufacturer edit would cause any slower of page loads? for the size my site is now i'd probably never notice a difference.

 

 

also, considering i emptied my xsell database, is it safe to remove everything before

/* if we have not enough products to fill the box */

in your version of xsell and delete the sql tables ?

Link to comment
Share on other sites

eventually i will have 500+ manufacturers and thousands of products (with the only intentions to continue expanding), do you think in this instance replacing your variant with a manufacturer edit would cause any slower of page loads? for the size my site is now i'd probably never notice a difference.

also, considering i emptied my xsell database, is it safe to remove everything before

/* if we have not enough products to fill the box */

in your version of xsell and delete the sql tables ?

 

no, then you would need to remove everything above

 

/* there are no xsell products registered at all for this product */

 

 

from the version but then it is no longer an xsell box but a "same manufacturers" box.

Treasurer MFC

Link to comment
Share on other sites

boxtel, i'm not quite sure how to group the manufacturers to fix this contribution to display random items from the manufacturer page instead of random product from inside the category

 

my manufacturers are presently called via $manufacturers['manufacturers_id'] .. how exactly do i replace the categories call? i suspect i will have to join manufacturers and products but i'm a bit sketchy exactly how to do so.

Link to comment
Share on other sites

  • 3 weeks later...

boxtel, i've made another attempt at this and although i'm not getting any errors; i'm not getting any products showing either (even though they exist)

 

any suggestions?

here's my code:

	  $info_box_contents = array();
  $info_box_contents[] = array('align' => 'left', 'text' => TEXT_XSELL_PRODUCTS);
  new contentBoxHeading($info_box_contents);

  $row = 0;
  $col = 0;
  $info_box_contents = array();


  /* fill the box with all random products from the same category */

	$mtm= rand();
	$xsell_cat_query = tep_db_query("select manufacturers_id
									 from " . TABLE_MANUFACTURERS . "
									 where manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_id'] . "'");

	$xsell_cat_array = tep_db_fetch_array($xsell_cat_query);
	$xsell_category = $xsell_cat_array['manufacturers_id'];
	$new_limit = MAX_DISPLAY_ALSO_PURCHASED - $num_products_xsell;
	$xsell_prod_query = tep_db_query("select distinct p.products_id, 
													  p.products_image, 
													  pd.products_name
									  from " . TABLE_PRODUCTS . " p,
										   " . TABLE_MANUFACTURERS . " pc,
										   " . TABLE_PRODUCTS_DESCRIPTION . " pd 
									  where pc.manufacturers_id = '" . $xsell_category . "' and 
											pc.manufacturers_id = p.manufacturers_id and 
											p.products_id != '" . $HTTP_GET_VARS['products_id'] . "' and 
											p.products_id = pd.products_id and 
											pd.language_id = '" . $languages_id . "' and 
											p.products_status = '1' 
									  order by pd.products_name asc 
									  limit " . MAX_DISPLAY_ALSO_PURCHASED);

Link to comment
Share on other sites

  • 1 year later...
at present in my admin panel, i have it set to 12, but only 3 show on any given product, even if there's 1000 products attatched.

 

code:

<?php
/* 
$Id: xsell_products.php, v1  2002/09/11

osCommerce, Open Source E-Commerce Solutions 
<http://www.oscommerce.com> 

Copyright (c) 2002 osCommerce 

Released under the GNU General Public License 
*/ 

/* if product given */

if ($HTTP_GET_VARS['products_id']) { 

/* obtain the xsell product */

$xsell_query = tep_db_query("select distinct p.products_id, 
										 p.products_image, 
										 pd.products_name 
						 from " . TABLE_PRODUCTS_XSELL . " xp,
							  " . TABLE_PRODUCTS . " p,
							  " . TABLE_PRODUCTS_DESCRIPTION . " pd 
						 where xp.products_id = '" . $HTTP_GET_VARS['products_id'] . "' and 
							   xp.xsell_id = p.products_id and 
							   p.products_id = pd.products_id and 
							   pd.language_id = '" . $languages_id . "' and 
							   p.products_status = '1' 
						 order by xp.products_id asc 
						 limit " . MAX_DISPLAY_ALSO_PURCHASED); 

$num_products_xsell = tep_db_num_rows($xsell_query); 

/* if we equal or exceed the minimal amount of products */

if ($num_products_xsell >= MIN_DISPLAY_ALSO_PURCHASED) { 

  /* put them in the box */

  $info_box_contents = array();
  $info_box_contents[] = array('align' => 'left', 'text' => TEXT_XSELL_PRODUCTS);
 // new contentBoxHeading($info_box_contents);

  $row = 0;
  $col = 0;
  $info_box_contents = array();
  while ($xsell = tep_db_fetch_array($xsell_query)) {
	$xsell['products_name'] = tep_get_products_name($xsell['products_id']);
	$info_box_contents[$row][$col] = array('align' => 'center',
										   'params' => 'class="smallText" width="33%" valign="top"',
										   'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $xsell['products_id']) . '">' . tep_image(DIR_WS_PRODUCT_IMAGES . $xsell['products_image'], $xsell['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $xsell['products_id']) . '">' . $xsell['products_name'] . '</a>');
	$col ++;
	if ($col > 2) {
	  $col = 0;
	  $row ++;
	}
  }

  /* if we have not enough products to fill the box */

  if ($num_products_xsell < MAX_DISPLAY_ALSO_PURCHASED) {

	/* add some random products from the same category to fill the box */

	$mtm= rand();

	$xsell_cat_query = tep_db_query("select categories_id
							   from " . TABLE_PRODUCTS_TO_CATEGORIES . "
							   where products_id = '" . $HTTP_GET_VARS['products_id'] . "'");
	$xsell_cat_array = tep_db_fetch_array($xsell_cat_query);
	$xsell_category = $xsell_cat_array['categories_id'];
	$new_limit = MAX_DISPLAY_ALSO_PURCHASED - $num_products_xsell;
	$xsell_prod_query = tep_db_query("select distinct p.products_id, 
										 p.products_image, 
										 pd.products_name 
						 from " . TABLE_PRODUCTS . " p,
							  " . TABLE_PRODUCTS_TO_CATEGORIES . " pc,
							  " . TABLE_PRODUCTS_DESCRIPTION . " pd 
						 where pc.categories_id = '" . $xsell_category . "' and
							   p.products_id != '" . $HTTP_GET_VARS['products_id'] . "' and 
							   pc.products_id = p.products_id and 
							   p.products_id = pd.products_id and 
							   pd.language_id = '" . $languages_id . "' and 
							   p.products_status = '1' 
						 order by rand($mtm) desc 
						 limit " . $new_limit);

   while ($xsell = tep_db_fetch_array($xsell_prod_query)) {
	 $xsell['products_name'] = tep_get_products_name($xsell['products_id']);
	 $info_box_contents[$row][$col] = array('align' => 'center',
											'params' => 'class="smallText" width="33%" valign="top"',
											'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $xsell['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $xsell['products_image'], $xsell['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $xsell['products_id']) . '">' . $xsell['products_name'] . '</a>');
	 $col ++;
	 if ($col > 2) {
	   $col = 0;
  //	 $row ++;
	 }
   }
  }
  new contentBox($info_box_contents);
}
else {

  /* there are no xsell products registered at all for this product */

  $info_box_contents = array();
  $info_box_contents[] = array('align' => 'left', 'text' => TEXT_XSELL_PRODUCTS);
//  new contentBoxHeading($info_box_contents);

  $row = 0;
  $col = 0;
  $info_box_contents = array();
  /* fill the box with all random products from the same category */

	$mtm= rand();
	$xsell_cat_query = tep_db_query("select categories_id
									 from " . TABLE_PRODUCTS_TO_CATEGORIES . "
									 where products_id = '" . $HTTP_GET_VARS['products_id'] . "'");

	$xsell_cat_array = tep_db_fetch_array($xsell_cat_query);
	$xsell_category = $xsell_cat_array['categories_id'];
	$new_limit = MAX_DISPLAY_ALSO_PURCHASED - $num_products_xsell;
	$xsell_prod_query = tep_db_query("select distinct p.products_id, 
													  p.products_image, 
													  pd.products_name 
									  from " . TABLE_PRODUCTS . " p,
										   " . TABLE_PRODUCTS_TO_CATEGORIES . " pc,
										   " . TABLE_PRODUCTS_DESCRIPTION . " pd 
									  where pc.categories_id = '" . $xsell_category . "' and 
											pc.products_id = p.products_id and 
											p.products_id != '" . $HTTP_GET_VARS['products_id'] . "' and 
											p.products_id = pd.products_id and 
											pd.language_id = '" . $languages_id . "' and 
											p.products_status = '1' 
									  order by rand($mtm) desc 
									  limit " . MAX_DISPLAY_ALSO_PURCHASED);

   while ($xsell = tep_db_fetch_array($xsell_prod_query)) {
	 $xsell['products_name'] = tep_get_products_name($xsell['products_id']);
	 $info_box_contents[$row][$col] = array('align' => 'center',
											'params' => 'class="smallText" width="33%" valign="top"',
											'text' => '<a class="tiny" href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $xsell['products_id']) . '">' . $xsell['products_name'] . '</a>');
	 $col ++;
	 if ($col > 2) {
	   $col = 0;
   //	$row ++;
	 }
   }
  new contentBox($info_box_contents);
}
}
?>

Hi,

Can I get the above code to work only when no Xsell products listed, but when xsell products have been defined then the code below to work

<?php

$xsell_array = array();

 

$xsell_query_raw = "SELECT p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, xp.xsell_id, m.manufacturers_name, m.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_XSELL . " xp, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' AND p.manufacturers_id = m.manufacturers_id AND p.products_id = xp.xsell_id AND xp.products_id = '" . $HTTP_GET_VARS['products_id'] . "' AND p.products_id = pd.products_id ORDER BY pd.products_name";

 

$xsell_split = new splitPageResults($xsell_query_raw, MAX_DISPLAY_PRODUCTS_NEW);

if ($xsell_split->number_of_rows > 0) { ;?>

<table class="productListing" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td width="85" align="center" class="productListing-heading">Add

</td>

<td class="productListing-heading">

<?php echo tep_draw_separator('pixel_trans.gif', '150', '10'); ?>

</td>

<td width="100%" class="productListing-heading">

Recommended with this Product

</td>

</tr>

<?php

$xsell_query = tep_db_query($xsell_split->sql_query);

while ($xsell = tep_db_fetch_array($xsell_query)) {

if ($xsell_price = tep_get_products_special_price($xsell['products_id'])) {

$products_price = '<s>' . $currencies->display_price($xsell['products_price'], tep_get_tax_rate($xsell['products_tax_class_id'])) . '</s> <span class="productSpecialPrice">' . $currencies->display_price($xsell_price, tep_get_tax_rate($xsell['products_tax_class_id'])) . '</span>';

} else {

$products_price = $currencies->display_price($xsell['products_price'], tep_get_tax_rate($xsell['products_tax_class_id']));

}

?>

<tr>

<td class="main" align="center">

<?php

echo tep_draw_checkbox_field('add_recommended[]',$xsell['products_id']);

?>

</td>

<td width="<?php echo SMALL_IMAGE_WIDTH + 10; ?>" align="center" valign="top" class="main"><?php echo '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $xsell['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $xsell['products_image'], $xsell['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a>'; ?></td>

<td valign="middle" class="smallText"><?php echo '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $xsell['products_id']) . '"><b><u>' . $xsell['products_name'] . '</u></b></a><br>

Manufacturer: <a href="' . tep_href_link(FILENAME_DEFAULT, 'manufacturers_id=' . $xsell['manufacturers_id']) . '" target="_blank">' . $xsell['manufacturers_name'] . '</a><br>Price: ' . $products_price; ?></td>

</tr>

 

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

</tr>

<?php

}

} else {

?>

<table>

<tr>

<td>

<?php echo tep_draw_separator('pixel_trans.gif', '100%', '10');?>

</td>

</tr>

<?php

}

?>

</tr>

</table>

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...