[Development] Products Specifications

[♥kymation]

The SQL in Products Specifications was designed to be as efficient as possible for most stores. Of course that may not apply to your particular store, so feel free to experiment with whatever changes you feel might work. I suggest using the KISS Error Handler to benchmark the different versions. Please post what you find from this experiment.

 

Regards

Jim

[♥bruyndoncx]

OK, I'll post my findings. I have the KISS error handler installed, so I'm all set to go ;)

[♥bruyndoncx]

I think this one officially qualifies as a hack, a piece of code to optimize a particular part of the webshop leaving most of the code untouched ... with a 'WorksForMe' warning ;)

 

I've a working version, not yet totally clean, but it does reduce the number of queries from 249 to 79 on a page with a few filters, and from 298 to 128 on another page (with more filters)

Now the actual gain is more difficult to measure. Overall the number of queries is a lot less, but there is now a more complex query for which I'd hope the result is faster than all the individual small queries.

I'm hitting the same database, thinking about how to test this now, or what kind of monitoring I can put in place to measure this with life visitor data ...

 

Here is the code if you want to try this yourself - note this is a development thread, if these instructions are not clear to you, you should probably not be trying this ...

 

the code change in products_filter box is

 

ADD

    if ($specs_array['products_column_name'] == 'manufacturers_id' and $specs_array['filter_class'] == 'exact') {
	  $filters_select_array = $spec_object->CB_getManufacturerFilterCount ($filter_id, $specs_array['specifications_id']);
    } else {

BEFORE

	    $previous_filter = 0;
    $previous_filter_id = 0;

 

ADD

}

BEFORE

	    $box_text_vars = tep_get_filter_string ($specs_array['filter_display'], $filters_select_array, FILENAME_PRODUCTS_FILTERS, $var, $$var);

 

In the specifications class file, add a new function, a trimmed down, modified getFilterCount function just for manufacturers

   public function CB_getManufacturerFilterCount($specification, $specifications_id) {
  global $filters_select_array;
  global $filter_index;
  $raw_query_from = " from (" . TABLE_PRODUCTS . " p)
					    join (" . TABLE_PRODUCTS_TO_CATEGORIES . " p2c)
						  on (p.products_id = p2c.products_id)
					    left join specials s on (p.products_id = s.products_id)  
				   ";

  $raw_query_where = " where p.products_status = '1' ";

  if ($this->current_category_id != 0) { // Restrict query to the appropriate category/categories
    $subcategories_array = array();
    tep_get_subcategories ($subcategories_array, $this->current_category_id);

    if (SPECIFICATIONS_FILTER_SUBCATEGORIES == 'True' && count ($subcategories_array) > 0) {
	  $category_ids = $this->current_category_id . ',' . implode (',', $subcategories_array);
	  $raw_query_where .= '   ' . "and p2c.categories_id in (" . $category_ids . ") ";    
    } else {
	  $raw_query_where .= " and p2c.categories_id = '" . $this->current_category_id . "' ";
    }
  } // if ($this->current_category_id

  $applied_filters = $this->getAppliedFilters();
  foreach ($applied_filters as $k => $v) {
    if ($k == $specifications_id) {
	  continue;
    }
    $specs_array = $this->getSpecification($k);
    $raw_query_addon_array = tep_get_filter_sql($specs_array['filter_class'], $specs_array['specifications_id'], $v, $specs_array['products_column_name'], $this->languages_id);
    $raw_query_from .= $raw_query_addon_array['from'];
    $raw_query_where .= $raw_query_addon_array['where'];
  } // foreach($applied_filters

  if ($specification == '0') { // show_all
    $raw_query_start = "select 0 as filter, count(p.products_id) as count ";
    $raw_query_group_by = '';
  } else {
    $raw_query_start = "select manufacturers_name as filter, count(p.products_id) as count ";
    $raw_query_from .= "  join (" . TABLE_MANUFACTURERS . " m)
						  on (p.manufacturers_id = m.manufacturers_id)
						  ";
    $raw_query_group_by = ' group by manufacturers_name';
  }

  $raw_query = $raw_query_start . $raw_query_from . $raw_query_where . $raw_query_group_by;
//print 'Raw Query: ' . $raw_query . '<br>';
  $filter_count_query = tep_db_query ($raw_query);
   while ($filter_count_result = tep_db_fetch_array ($filter_count_query)) {
	    $filters_select_array[$filter_index] = array ('id' => $filter_count_result['filter'],
														 'text' => htmlspecialchars($filter_count_result['filter']),
													    'count' => (string) $filter_count_result['count']
													   );
	    $filter_index++;
  }
  return $filters_select_array;      
   }

[♥bruyndoncx]

I've added caching of the products_filter box, and everything is looking to run quite smoothly now.

The caching is done only on the initial selection of a category.

Now I just need to add some admin code to refresh the cache.

[emcmoda]

Hi Jim how to make the fiter with size (ROZMIAR) and/or color (KOLOR) the same as

http://answear.com/jeansy-213-k.html

 

BR

Michal

Edited July 23, 2012 by emcmoda

[♥kymation]

Set up Size and Color specifications for your products. Add the size and color to each product. Set up filters for those specifications that match the size and color information.

 

All of this is covered in the User's Manual. Read all of it.

 

Regards

Jim

[JSR]

Hi kymation,

 

I have a question about values as checkboxes. I added onClick for the checkboxes, but it only passes one value as link data at a time.

I know I probably need to edit the tep_draw_pull_down_menu funtcion in html_output.php but I get stuck trying.

How do I keep the selected value in the link data? I Hope you understand what I'm trying to do here...

[♥kymation]

The only way to handle checkboxes is to enclose them in a form and add a submit button. I suppose you could also use AJAX to submit each one in the background, but that would be a lot of extra work for very little benefit.

 

Regards

Jim

[JSR]

Thanks for the fast reply and insight!

I thought it would be possible to either store the previous selected value in a session or redirecting the page and saving the "get" variables after submission.

By searching the web I found several fora addressing this issue (saving selected checkboxes in php forms after submit).

But my knowledge of php is pretty limited, so guess I'll have to stick with the submit button for now. :wacko:

[vampirehunter]

is it possible to set up checkbox filters like on this website?

 

http://www.style369.com/webapp/wcs/stores/servlet/CatalogNavigationSearchResultCmd?catalogId=33555&storeId=13054&langId=-1&viewAllFlag=false&sort_field=Relevance&categoryId=270484&beginIndex=1&pageSize=20#catalogId=33555&storeId=13054&langId=-1&viewAllFlag=false&sort_field=Relevance&categoryId=270484&beginIndex=1&pageSize=20&refinements=Colour{1}~[green]&noOfRefinements=1

 

so if you click on muliple specifications to keep narrowing down the products?

[♥bruyndoncx]

is it possible to set up checkbox filters like on this website?

 

http://www.style369.com/webapp/wcs/stores/servlet/CatalogNavigationSearchResultCmd?catalogId=33555&storeId=13054&langId=-1&viewAllFlag=false&sort_field=Relevance&categoryId=270484&beginIndex=1&pageSize=20#catalogId=33555&storeId=13054&langId=-1&viewAllFlag=false&sort_field=Relevance&categoryId=270484&beginIndex=1&pageSize=20&refinements=Colour{1}~[green]&noOfRefinements=1

 

so if you click on muliple specifications to keep narrowing down the products?

 

yes you can

[ken0306]

Hi, under the current version, I only can see the category images under the product_filters.php right hand side uper corner, how can I change to category name?

thank you

[♥kymation]

The category images are part of stock osCommerce. They have nothing to do with the Products Specifications Addon.

 

Regards

Jim

[ken0306]

Yes, Jim, I understand that. I want to know if there is way to insert it. Because when people click on the filter, it will direct them to the products_filter.php page, not on the index.php page anymore. So I want to add the category title on the products_filter.php page, and it may looks consistent on the site.

Thank you

Ken

[♥kymation]

Now I see what you want. Find in products_filter.php:

 

    $image = tep_db_query ("select categories_image
					    from " . TABLE_CATEGORIES . "
					    where categories_id = '" . (int) $current_category_id . "'
   $image = tep_db_fetch_array ($image);
   $image = $image['categories_image'];

 

and change it to

 

    $image_query_raw = tep_db_query ("select c.categories_image,
						  cd.categories_name
					    from " . TABLE_CATEGORIES . " c
						  join " . TABLE_CATEGORIES_DESCRIPTION . " cd
						    on (cd.categories_id = c.categories_id)
					    where c.categories_id = '" . (int) $current_category_id . "'
   $image_query = tep_db_fetch_array ($image_query_raw);
   $image = $image_query['categories_image'];

 

Then find

 

<h1><?php echo HEADING_TITLE; ?></h1>

 

and replace it with

 

<h1><?php echo $image_query['categories_name']; ?></h1>

 

Regards

Jim

[Elwood]

Hello, great Add-on for start :)

 

But is there any progress with this known bug with specials chars like & / and others as well as some specific polish chars?

 

Could you give maybe some temporary solution in that case which wouldn't involve much changes in core code?

 

Thanks in advance and keep up good work :)

Edited November 23, 2012 by Elwood

[♥kymation]

No this has not been fixed. Feel free to do it yourself and contribute the improvements to the project.

 

Regards

Jim

[♥bruyndoncx]

I'm trying to backtrack the changes I've made looking for & changes i found reference to this function, where I have what looks like a manual change for special chars ...

 function tep_clean_get__recursive ($get_var) {

   if (!is_array($get_var)) {

//    return preg_replace("/[^ {}a-zA-Z0-9_.-]/i", "", urldecode($get_var));

   return preg_replace("/[^ {}öüéè&çà#a-zA-Z0-9_.-]/i", "", urldecode($get_var));

   }



   // Add the preg_replace to every element.

   return array_map ('tep_clean_get__recursive', $get_var);

 } // function tep_clean_get__recursive

 

Same file a bit further, this chanage

 function tep_decode_recursive ($variable) {

   if (!is_array ($variable)) {

  return  ($variable);

//	  return rawurldecode ($variable);

 

in my tep_draw_links_menu function I have this default section, links is what I use on the site for practically all filters and also for the brands where I had the issue with ampersands

    default:

	  $field .= '  ';

	  if ($default == $link_data['id']) {

	    $field .= '<b>';

	  }

	  $field .= '<a href="' . tep_href_link ($target, tep_get_array_get_params (array ( $name, 'page') ) . ($link_data['id'] == '0' ? '' : $name . '=' . urlencode(tep_output_string($link_data['id'])))) . '" rel="nofollow">';

	  $field .= tep_output_string ($link_data['text'] );

	  $field .= '</a>';

As I don't know for sure what I changed, this might give you some hints as to where the changes might be needed

 

HTH

[farmohadradi]

Dear Kymation & Friends,

 

I have osCommerce V.2.3.1 running very nice and I have downloaded Product Specifications add-on today.

Uploaded the files, imported the SQL with phpmyadmin.

 

My admin area is up and I can see the configuration of this add-on but my front-end is down with this error:

Fatal error: Call to undefined function tep_draw_button_header_top() in /home/XXXX/public_html/includes/modules/boxes/cm_user_menu.php on line 84

 

Any suggestion guys? Please guide :)

Cheers.

 

Farhad Moradi

[♥kymation]

The file cm_user_menu.php is not a part of this addon. This is part of some other addon that you have installed.

 

Regards

Jim

[farmohadradi]

@@kymation

 

Dear Kymation,

 

Sorry.

 

I have purchased this script with template and just installed it from the package.

How to track this error and to solve this issue?

Is there any user menu compatible with this add-on? I really need to use Product Specifications.

 

Please guide.

Thank you.

 

Farhad Moradi

[♥kymation]

Did you overwrite any existing files with the Products Specifications files? You can't do that if you have a template or other addons installed that also modify those files. You need to merge your modified files with the ones from Products Specifications. I would use a good comparison program (Winmerge, Meld, etc.) to compare the files and move the changes to your new files.

 

Regards

Jim

[farmohadradi]

@@kymation

 

Dear Jim,

 

So you mean that:

 

1) I replace my website from backup.

2) I replace my SQL from my backup. (Have my website up again)

3) Upload the files (No Overwrite) and edit the existing files?

 

Best Regards,

Farhad Moradi

[♥kymation]

The first two steps are unnecessary. Make a copy of your backup from before and edit those files to include the Products Specifications changes, then upload the changed files. That will overwrite the broken files with correct ones.

 

Regards

Jim

[farmohadradi]

@@kymation

 

Dear Jim,

 

Thank you for your efforts and help.

I will go now and do as you said. Will update you on process :)

 

Thank you again.

 

Best Regards,

Farhad Moradi