19 replies to this topic

[PupStar]

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

[NodsDorf]

Can't you just select the products_id & products_model from products and left / right join on products_id in products_to_category?

[PupStar]

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?

[PupStar]

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:

Quote

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

[geoffreywalton]

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

[PupStar]

geoffreywalton, on 11 January 2012, 22:21, said:

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

Quote

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

[NodsDorf]

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.

[geoffreywalton]

youmight want to add an order by on the end.

Check out w3schools.com for more info on mysql

HTH

G

[PupStar]

NodsDorf, on 12 January 2012, 01:18, said:

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

[PupStar]

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

Edited by PupStar, 12 January 2012, 23:23.

[NodsDorf]

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

[PupStar]

Hi Don,

NodsDorf, on 13 January 2012, 00:31, said:

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.

Quote

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%">&nbsp;</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>&nbsp;</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'] . '&nbsp;&nbsp;<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'] . '&nbsp;&nbsp;<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>&nbsp;&nbsp;<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 . '&nbsp;' . 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 .= '&nbsp;';
		   }
		   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>&nbsp;&nbsp;' . 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>

[PupStar]

still have issues with this and can not seem to move forward.

[NodsDorf]

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.

[PupStar]

I know what you ean mate because I work the same way

[PupStar]

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

[Jack_mcs]

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.

[patrickluursema]

I wouldn't touch the SQL, there are some other ways to get what you want. Just search on previous postings.
Regards,

Patrick Luursema

[PupStar]

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:

Quote

spare parts -> muffler

The actual full category path is:

Quote

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

Edited by PupStar, 28 January 2012, 15:25.

[bruyndoncx]

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