Search page V3.12
Printed From: ProductCart E-Commerce Solutions
Category: ProductCart Build To Order
Forum Name: Customizing BTO
Forum Description: Interact with other users, resellers and developers about customizing BTO
URL: https://forum.productcart.com/forum_posts.asp?TID=1812
Printed Date: 27-November-2024 at 1:08am
Software Version: Web Wiz Forums 12.04 - http://www.webwizforums.com
Topic: Search page V3.12
Posted By: cmason
Subject: Search page V3.12
Date 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
|
Replies:
Posted By: cmason
Date 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.
|
|