Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Best way to find duplicate products by model number and display the top level category name they are in?


PupStar

Recommended Posts

Hi Guys,

 

As the title of the thread suggests I am trying to find the best way to find duplicate products by model number and display the top level category name they are in.

 

As the store I am working on will sell spare parts the same spare part may also be present in more than 1 category so my ultimate goal is to find all the duplicates and list them on the product listing page along side the product.

 

I have found various queries that find duplicates but only by product_id which is fine but the product_model is not present in a table such as products_to_categories.

 

Can any of the guru's suggest a query that I can use for this?

 

Thanks

 

Mark

Link to comment
Share on other sites

this maybe way off the mark (I am totally pants with queries lol)

 

   $product_to_category_query = tep_db_query("select p.products_id, products_model from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'");

 

close or miles away?

Link to comment
Share on other sites

ok looked at this again on semi-clear head urgh

 

I now have come up with this

 

   $product_to_category_query = tep_db_query("SELECT products_id, products_model FROM " . TABLE_PRODUCTS . " LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " ON products_model = categories_id'");

 

However this results in this error:

 

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 products_id, products_model FROM products LEFT JOIN products_to_categories ON products_model = categories_id'

 

[TEP STOP]

 

Please help!

 

Thanks

 

Mark

Link to comment
Share on other sites

Mark

 

Why is there a single quote at the end of the line, also left joins need brackets,

 

SELECT products_id, products_model FROM (products) LEFT JOIN products_to_categories ON products_model = categories_id

 

HTH

 

G

Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

Link to comment
Share on other sites

Mark

 

Why is there a single quote at the end of the line, also left joins need brackets,

 

SELECT products_id, products_model FROM (products) LEFT JOIN products_to_categories ON products_model = categories_id

 

HTH

 

G

 

Hi Geoffrey,

 

Thanks for the reply. I have taken what you said and applied it then I got the 'ambiguous' error so obviously it was asking me it has found a products_id in both the products and products_to_categories tables and I need to specify which one.

 

So I changed the code to

 

$product_to_category_query = tep_db_query("SELECT products_id FROM (" . TABLE_PRODUCTS . ") p LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_id = p2c.products_id");

 

and now I am well stuck with

 

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 'where p.products_id = p2c.products_id' at line 1

 

SELECT p.products_id FROM (products) LEFT JOIN products_to_categories p2c where p.products_id = p2c.products_id

 

[TEP STOP]

 

not expecting the query written for me just a hand to wipe the tears lol

 

Thanks

 

Mark

Link to comment
Share on other sites

SELECT p.products_id, p.products_model, p2c.categories_id  FROM products AS p
LEFT JOIN
products_to_categories AS p2c
ON p2c.products_id = p.products_id

 

Didn't check the database for actual column names so those maybe off, but that should put you on the right track.

Link to comment
Share on other sites

youmight want to add an order by on the end.

 

Check out w3schools.com for more info on mysql

 

HTH

 

G

Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

Link to comment
Share on other sites

SELECT p.products_id, p.products_model, p2c.categories_id  FROM products AS p
LEFT JOIN
products_to_categories AS p2c
ON p2c.products_id = p.products_id

 

Didn't check the database for actual column names so those maybe off, but that should put you on the right track.

 

Thanks guys that did the trick :)

 

Now the real trouble starts as I need a way of drilling down to show the name of the 3rd category in the tree in all top level categories (Sub Category3) i.e.

 

Top Level Category 1

|---------->Sub Category 1

|---------->Sub Category 2

|---------->Sub Category 3

 

Top Level Category 2

|---------->Sub Category 1

|---------->Sub Category 2

|---------->Sub Category 3

 

etc etc etc.....

 

Thanks

 

Mark

Link to comment
Share on other sites

I have been trying to add in the categories but all it does now is display the highest category_id against all products and not the correct product_id

 

   $product_to_category_query = tep_db_query("SELECT distinct p.products_id, p.products_model, cd.categories_name, p2c.categories_id FROM products AS p LEFT JOIN products_to_categories AS p2c ON p2c.products_id = p.products_id LEFT JOIN categories_description AS cd ON cd.categories_id = p2c.categories_id");


 

Mark

Link to comment
Share on other sites

Hey Mark,

 

So it looks like this maybe your issue....

You have the same product in multiple categories right?

So you can't use SELECT DISTINCT because you now have eliminated all the duplicates.

 

Your results indicate that the SELECT is getting an array and moving to the first / last level of it to give you results. Try getting the data you want straight through SQL then move to the processing of it in PHP. Without knowing what kind of can of worms you opened I'm not sure I can be much more help.

 

But I do hope it helps,

Don

Link to comment
Share on other sites

Hi Don,

 

Hey Mark,

 

So it looks like this maybe your issue....

You have the same product in multiple categories right?

So you can't use SELECT DISTINCT because you now have eliminated all the duplicates.

 

well sort of, same products_model different product_id but essentially yes same product in multiple categories.

 

Your results indicate that the SELECT is getting an array and moving to the first / last level of it to give you results. Try getting the data you want straight through SQL then move to the processing of it in PHP. Without knowing what kind of can of worms you opened I'm not sure I can be much more help.

 

But I do hope it helps,

Don

 

ok this is the code that I have in the modified product_listing file maybe you can make good of the query better than me urgh

 

<form name="cart_multi" method="post" action="<?php echo tep_href_link(FILENAME_SHOPPING_CART, tep_get_all_get_params(array('action')) . 'action=add_multi', 'NONSSL'); ?>">
 <div class="contentText">
<?php
 $prod_list_contents = '<div style="clear: both; padding:5px;"></div>' .
				    '<div class="contentContainer">' .
				    '  <div class="ui-widget-header ui-corner-top">' .
				    '    <table border="0" width="100%" cellspacing="0" cellpadding="0">' .
				    '	  <tr>';
 for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
   $lc_align = '';
   switch ($column_list[$col]) {
  case 'PRODUCT_LIST_MEDIACAT_PART_NO':
    $lc_text = TABLE_HEADING_PARTS_ID;
    $lc_align = 'right';
    break;
  case 'PRODUCT_LIST_MODEL':
    $lc_text = TABLE_HEADING_MODEL;
    $lc_align = 'center';
    break;
  case 'PRODUCT_LIST_NAME':
    $lc_text = TABLE_HEADING_PRODUCTS_DESCRIPTION;
    $lc_align = 'center';
    break;
  case 'PRODUCT_LIST_MEDIACAT_QUANTITY':
    $lc_text = TABLE_HEADING_MEDIACAT_QUANTITY;
    $lc_align = 'left';
    break;
  case 'PRODUCT_LIST_QUANTITY':
    $lc_text = TABLE_HEADING_QUANTITY;
    $lc_align = 'right';
    break;
  case 'PRODUCT_LIST_PRICE':
    $lc_text = '';
    $lc_align = 'right';
    break;
   }
   $prod_list_contents .= '	    <td class="partsfindercolumn" ' . (tep_not_null($lc_align) ? ' align="' . $lc_align . '"' : '') . '>' . $lc_text . '</td>';
 }
 $prod_list_contents .= '	  <td width="19%"> </td></tr>' .
					 '    </table>' .
					 '  </div></div>';
if ($listing_split->number_of_rows > 0) {

   $product_to_category_query = tep_db_query("SELECT p.products_id, p.products_model, cd.categories_name, p2c.categories_id FROM products AS p LEFT JOIN products_to_categories AS p2c ON p2c.products_id = p.products_id LEFT JOIN categories_description AS cd ON cd.categories_id = p2c.categories_id");
   $product_to_category = tep_db_fetch_array($product_to_category_query);
   $rows = 0;
   $listing_query = tep_db_query($listing_split->sql_query);
   $prod_list_contents .= '  <div style="padding-bottom:16px;" class="ui-widget ui-widget-content-parts-finder ui-corner-bottom">' .
					   '    <table border="0" width="94%" cellspacing="0" cellpadding="2" align="center" class="striped">';

   while ($listing = tep_db_fetch_array($listing_query)) {
  $rows++;
  $prod_list_contents .= '<tr>';
  for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
    switch ($column_list[$col]) {
	  case 'PRODUCT_LIST_MEDIACAT_PART_NO':
	    $prod_list_contents .= '	    <td> </td><td width="15px" align="center" style="font-weight:bold; background-color: #c9c9c9;">' . $listing['mediacat_part_no'] . '</td>';
	    break;
	  case 'PRODUCT_LIST_MEDIACAT_QUANTITY':
	    $prod_list_contents .= '	    <td width="6%" align="center">' . $listing['products_mediacat_quantity'] . '</td>';
	    break;
	  case 'PRODUCT_LIST_MODEL':
	    $prod_list_contents .= '	    <td width="20%" align="center">' . $listing['products_model'] . '</td>';
	    break;
	  case 'PRODUCT_LIST_NAME':
	    if (isset($HTTP_GET_VARS['manufacturers_id']) && tep_not_null($HTTP_GET_VARS['manufacturers_id'])) {
		  $prod_list_contents .= '	    <td width="40%" style="padding-left: 2px">' . $listing['products_name'] . '  <a class=info href="#" onclick="return false">info<span><b>' . $product_to_category['categories_name'] . '</b></span></a></td>';
	    } else {
		  $prod_list_contents .= '	    <td width="40%" style="padding-left: 2px">' . $listing['products_name'] . '  <a class=info href="#" onclick="return false">info<span><b>' . $product_to_category['categories_name'] . '</b></span></a></td>';
	    }
	    break;
	  case 'PRODUCT_LIST_PRICE':
	   if (tep_not_null($listing['specials_new_products_price'] > '0')) {
		  $prod_list_contents .= '<td width="20%" align="center"><del>' .  $currencies->display_price($listing['products_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . '</del>  <span class="productSpecialPrice">' . $currencies->display_price($listing['specials_new_products_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . '</span></td>';
	   }
	   else if ($listing['products_price'] == 0){
		  $prod_list_contents .= '<td width="50%" align="center" colspan="2" class="smallText"><a href="' . tep_href_link(FILENAME_CONTACT_US) . '">' . TEXT_CALL_FOR_PRICE . '</a></td>';
	    } else {
		  $prod_list_contents .= '<td width="14%" align="center">' . $currencies->display_price($listing['products_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . '</td>';
	   }
	    break;
	  case 'PRODUCT_LIST_BUY_NOW':
	    if (tep_not_null($listing['products_price'] > '0')) {
	    $qty_array=array();
	    $qty_array[] = array('id' => '00', 'text' => 'Qty');
		 for($i=1; $i<=20; $i++){
		  if(strlen($i)!= 1){
		   $j = '' . $i;
		  } else {
		   $j = $i;
		  }
	    $qty_array[] = array('id' => $j, 'text' => $j); }
	    $prod_list_contents .= '<td width="20%" align="center">' . TEXT_QTY . ' ' . tep_draw_pull_down_menu('add_id['.$number_of_products.']', $qty_array, $i) . tep_draw_hidden_field('products_id['.$number_of_products.']', $listing['products_id']) . '</td>';
	    }else{
	    $prod_list_contents .= ' ';
	   }
	   break;
    }
  }

	    $prod_list_contents .= '	  </tr>';
   }
	    $prod_list_contents .= '    </table>' .
 '</div>';
   echo $prod_list_contents;
 } else {
?>
   <p><?php echo TEXT_NO_PRODUCTS; ?></p>
<?php
 }
?>
<div class="contentContainer">
<div class="ui-widget buttonSet" style="padding-top:10px;">
   <span class="buttonAction"><?php echo '<b>' . TEXT_ENTER_QUANTITY . TEXT_YOU_REQUIRE . '</b>  ' . tep_draw_button(IMAGE_BUTTON_IN_CART, 'cart', null, 'primary'); ?></span>
</div>
</div>
</form>
<div style="clear: both; padding:8px;"></div>
 <div class="contentContainer">
   <div class="ui-widget ui-widget-content ui-corner-all contentText" style="padding: 10px; background: #f4f4f4 url(images/main-bg.png); background-repeat: repeat-xy; font-style: italic;">
  <div class="contentText"><?php echo TEXT_PARTS_FINDER_TIP;?>
  </div>
   </div>
 </div>
</div>

Link to comment
Share on other sites

Hey Mark, my PHP skills are not expert. They way I'd tackle it is by trying a kathousand things till I got it to work. Which I won't subject you to.

 

Hope somebody else can point you in the right direction.

Link to comment
Share on other sites

Hi Guys,

 

I am still having no joy in getting this sorted.

 

One last attempt at asking for help before I dump this out of my product listing for good as I am jiggered if I can figure it out urgh

 

Thanks

 

Mark

Link to comment
Share on other sites

I'm not sure I understand the problem but there are in-built functions to get category paths and names. Once you have the category ID from the path, you can just call the function to get the name for it. By the way, you can't rely on the order of the fields in the database. They may not always be what you expect. You'll need to find all of the categories with the model number you want and then check the paths with the in-built function and compare them to each other. The shortest path is the uppermost category. The longest is the last category. This assumes they are all in the same path. If they are in two different categories that are on the same level , there's no way to tell which is the main one, without adding extra code, of course.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

ok I have managed to find some code that does what I want

 

	// Return a category's name
	 function tep_get_category_name($categories_id,
	 $language = '') {
	 global $languages_id;
	  if (empty($language)) $language = $languages_id;
	  $categories_query =
	  tep_db_query("select categories_name from " .
	  TABLE_CATEGORIES_DESCRIPTION . " where categories_id = '" .
	  (int)$categories_id . "' and language_id = '" .
	  (int)$language . "'");
	  $categories = tep_db_fetch_array($categories_query);
	  return $categories['categories_name'];
	 }
	// Construct a category path to the product in names
	 function tep_get_product_path_names($products_id) {
	  $cat_namePath = '';
	  $category_query =
	  tep_db_query("select p2c.categories_id from " .
	  TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . "
	  p2c where p.products_id = '" . (int)$products_id . "' and
	  p.products_status = '1' and
	  p.products_id = p2c.products_id limit 1");
	  if (tep_db_num_rows($category_query)) {
	   $category = tep_db_fetch_array($category_query);
	   $categories = array();
	   tep_get_parent_categories($categories,
	   $category['categories_id']);
	   $categories = array_reverse($categories);
	   $cat_namePath =
	   tep_get_category_name(implode('_', $categories));
	 if (tep_not_null($cat_namePath)) $cat_namePath .= '->';
	  $cat_namePath .=
	  tep_get_category_name($category['categories_id']);
	  }
	  return $cat_namePath;
	 }

 

However this returns the top level category name and the category that the product is in for example:

 

spare parts -> muffler

 

The actual full category path is:

 

spare parts -> chainsaws -> ms170 -> muffler

 

Ideally I would like it to just return the second subcategory name in this example being 'ms170'

 

Is anyone willing to give this a shot for me?

 

Thanks

 

Mark

Link to comment
Share on other sites

tep_db_query="select pc.categories_name, p.products_id from products p join products_to_categories p2c using(products_id) join categories c using(categories_id) join categories_description pc on ( c.parent_categories_id = pc.categories_id and pc.language_id = '" .  (int)$language . "' )
where products_model = '" . $listing['products_model'] ."' and p.products_id <> '" . $listing['products_id'] . "' ";

 

untested code, so syntax needs to be checked, but the logic of this is, for a given product being listed, it would show the parent category name, and products_id with the same model, (except for the current product being listed)

 

HTH

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...