https://forum.productcart.com/ Copyright (c) 2006-2013 Web Wiz Forums - All Rights Reserved. Fri, 10 Apr 2026 17:13:37 +0000 Fri, 11 Jul 2008 20:27:27 +0000 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 12.04 360 https://forum.productcart.com/RSS_post_feed.asp?TID=1812 https://forum.productcart.com/forum_images/pc_logo_50.png https://forum.productcart.com/ https://forum.productcart.com/search-page-v3-12_topic1812_post6005.html#6005 Author: cmason
Subject: 1812
Posted: 11-July-2008 at 8:27pm

Gave up on changing the code, solution that we implemented was to make other tier 1 categories wholesale categories so that they're not viewed if the customer is retail/public and, so far, all customers are retail/public.]]> Fri, 11 Jul 2008 20:27:27 +0000 https://forum.productcart.com/search-page-v3-12_topic1812_post6005.html#6005 https://forum.productcart.com/search-page-v3-12_topic1812_post5978.html#5978 Author: cmason
Subject: 1812
Posted: 09-July-2008 at 8:08pm

I need to customize the search page so that it only returns products from a certain tier 1 category - 74. I've done queries based on concatating strings before but never on this scale.

If I make no changes and search for sku like '%FE%'" resulting strSql looks like this (I have no idea where the "like '%d%' is being generated or why but results seem to accurate)
"
SELECT TOP 200 COUNT(categories.idcategory) AS ProductCount, categories.idcategory, categories.categoryDesc FROM (categories_products INNER JOIN categories ON categories_products.idcategory=categories.idcategory) LEFT OUTER JOIN products as A ON A.idProduct=categories_products.idProduct WHERE ((A.serviceSpec<>0 AND A.pcProd_BTODefaultPrice>=0 And A.pcProd_BTODefaultPrice<=999999999) OR (A.serviceSpec=0 AND A.price>=0 And A.price<=999999999)) AND ((content1 LIKE '%d%' OR content2 LIKE '%d%' OR content3 LIKE '%d%') OR (A.details LIKE '%d%' OR A.description LIKE '%d%' OR A.sDesc LIKE '%d%')) AND A.removed=0 AND A.sku like '%fe%' AND categories.idCategory=categories_products.idCategory AND categories.iBTOhide=0 AND categories.pccats_RetailHide=0 AND A.active=-1
"

if I put that into Sql Express's Query Designer it then looks like this
"
SELECT     TOP (200) COUNT(categories.idCategory) AS ProductCount, categories.idCategory, categories.categoryDesc
FROM        categories_products INNER JOIN
                      categories ON categories_products.idCategory = categories.idCategory AND categories_products.idCategory = categories.idCategory LEFT OUTER JOIN
                      products AS A ON A.idProduct = categories_products.idProduct
WHERE     (A.serviceSpec <> 0 AND A.pcProd_BTODefaultPrice >= 0 AND A.pcProd_BTODefaultPrice <= 999999999 OR
                      A.serviceSpec = 0 AND A.price >= 0 AND A.price <= 999999999) AND (A.content1 LIKE '%d%' OR
                      A.content2 LIKE '%d%' OR
                      A.content3 LIKE '%d%' OR
                      A.details LIKE '%d%' OR
                      A.description LIKE '%d%' OR
                      A.sDesc LIKE '%d%') AND (A.removed = 0) AND (A.sku LIKE '%fe%') AND (categories.iBTOhide = 0) AND (categories.pccats_RetailHide = 0) AND
                      (A.active = - 1)
"
If I then add the link to check for the tier one idParentCategory=74 it looks like this
"
SELECT     TOP (200) COUNT(categories.idCategory) AS ProductCount, categories.idCategory, categories.categoryDesc
FROM        categories_products INNER JOIN
                      categories ON categories_products.idCategory = categories.idCategory AND categories_products.idCategory = categories.idCategory INNER JOIN
                      categories AS categories_1 ON categories.idParentCategory = categories_1.idCategory LEFT OUTER JOIN
                      products AS A ON A.idProduct = categories_products.idProduct
WHERE     (A.serviceSpec <> 0 AND A.pcProd_BTODefaultPrice >= 0 AND A.pcProd_BTODefaultPrice <= 999999999 OR
                      A.serviceSpec = 0 AND A.price >= 0 AND A.price <= 999999999) AND (A.content1 LIKE '%d%' OR
                      A.content2 LIKE '%d%' OR
                      A.content3 LIKE '%d%' OR
                      A.details LIKE '%d%' OR
                      A.description LIKE '%d%' OR
                      A.sDesc LIKE '%d%') AND (A.removed = 0) AND (A.sku LIKE '%fe%') AND (categories.iBTOhide = 0) AND (categories.pccats_RetailHide = 0) AND
                      (A.active = - 1) AND (categories_1.idParentCategory = 74)
"

Has anyone successfully modified the search page to do something like this or have an idea on which area I should be looking at to inject the "INNER JOIN categories AS categories_1 ON categories.idParentCategory = categories_1.idCategory" or "From categories as categories_1 where categories.idParentCategory = categories_1.idCategory"? I think I know where to put "categories_1.idParentCategory = 74" but first categories_1 needs to be defined.

I also know this won't work if there's a category, not product search, but that's a problem for another day.

Christie Mason


]]> Wed, 09 Jul 2008 20:08:56 +0000 https://forum.productcart.com/search-page-v3-12_topic1812_post5978.html#5978