Search Products and Dispaly based on CategoryID
Hi,
Looking for some help with a little problem with PowerStore 2.
I am trying to populate certain pages with certain products, each page displays only products relating to that pages contents.
What I have been able to do is populate the pages with products however all of the products in the database are displayed and I need to limit what products are displayed based on the products category or categoryID (which ever is easiest and most effective).
Currently I am using code from the Products_Results.php to search the DB and populate the page as needed. But have not been able to get the results to be limited to CategoryID or ProductsCategoryID.
Any suggestions or help is much appriciated!
Code provided below is prior to html tag, code used to populate pages is same as code in Products_Results.php with minor CSS adjustments:
<?php require_once('WA_Globals/WA_Globals.php');?>
<?php require_once('Connections/localhost.php'); ?>
<?php require_once("WA_ValidationToolkit/WAVT_Scripts_PHP.php"); ?>
<?php require_once("WA_ValidationToolkit/WAVT_ValidatedForm_PHP.php"); ?>
<?php
//WA eCart Include
require_once("WA_eCart/WA_Store_Cart_PHP.php");
?>
<?php
$WA_Store_Cart->GetContent();
?>
<?php
//WA Database Search Include
require_once("WADbSearch/HelperPHP.php");
?>
<?php
//WA Database Search (Copyright 2005, WebAssist.com)
//Recordset: WADAProducts;
//Searchpage: ;
//Form: ;
$WADbSearch1_DefaultWhere = "";
if (!session_id()) session_start();
if ((((isset($_GET["Search"]))?$_GET["Search"]:"") != "")) {
$WADbSearch1 = new FilterDef;
$WADbSearch1->initializeQueryBuilder("MYSQL","1");
//keyword array declarations
$KeyArr1 = array("ProductSKU", "CategoryName", "ProductName", "ProductCartDesc", "ProductShortDesc", "ProductLongDesc");
//comparison list additions
$WADbSearch1->keywordComparison($KeyArr1,"".((isset($_GET["S_ProductName"]))?$_GET["S_ProductName"]:"") ."","AND","Includes",",%20","%20","%22","%22",0);
$WADbSearch1->addComparison("ProductCategoryID","".((isset($_GET["CategoryID"]))?$_GET["CategoryID"]:"") ."","AND","=",1);
//save the query in a session variable
if (1 == 1) {
$_SESSION["WADbSearch1_Products_Results"]=$WADbSearch1->whereClause;
}
}
else {
$WADbSearch1 = new FilterDef;
$WADbSearch1->initializeQueryBuilder("MYSQL","1");
//get the filter definition from a session variable
if (1 == 1) {
if (isset($_SESSION["WADbSearch1_Products_Results"]) && $_SESSION["WADbSearch1_Products_Results"] != "") {
$WADbSearch1->whereClause = $_SESSION["WADbSearch1_Products_Results"];
}
else {
$WADbSearch1->whereClause = $WADbSearch1_DefaultWhere;
}
}
else {
$WADbSearch1->whereClause = $WADbSearch1_DefaultWhere;
}
}
$WADbSearch1->whereClause = str_replace("\\''", "''", $WADbSearch1->whereClause);
$WADbSearch1whereClause = '';
?>
<?php
$currentPage = $_SERVER["PHP_SELF"];
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
?>
<?php
$maxRows_WADAProducts = 5;
$pageNum_WADAProducts = 0;
if (isset($_GET['pageNum_WADAProducts'])) {
$pageNum_WADAProducts = $_GET['pageNum_WADAProducts'];
}
$startRow_WADAProducts = $pageNum_WADAProducts * $maxRows_WADAProducts;
mysql_select_db($database_localhost, $localhost);
$query_WADAProducts = "SELECT *, (ProductStock - (SELECT Coalesce(Sum(DetailQuantity),0) FROM orderdetails INNER JOIN orders ON OrderID = DetailOrderID WHERE DetailProductID = ProductID AND OrderDate > ProductUpdateDate)) AS NumLeft, (SELECT MAX(ProductPrice) FROM products as prod2 INNER JOIN productoptions ON productoptions.ProductID = prod2.ProductID WHERE productoptions.GroupingID = products.ProductID AND prod2.ProductLive <> 0) AS TopPrice, (SELECT MIN(ProductPrice) FROM products as prod2 INNER JOIN productoptions ON productoptions.ProductID = prod2.ProductID WHERE productoptions.GroupingID = products.ProductID AND prod2.ProductLive <> 0) AS BottomPrice FROM products LEFT OUTER JOIN productcategories ON ProductCategoryID = CategoryID WHERE ProductLive <> 0 AND COALESCE((SELECT GroupingID FROM productoptions WHERE productoptions.ProductID = products.ProductID LIMIT 1), ProductID) = ProductID";
setQueryBuilderSource($query_WADAProducts,$WADbSearch2,false);
setQueryBuilderSource($query_WADAProducts,$WADbSearch1,false);
$query_WADAProducts.= preg_replace("/ AND /", " HAVING ", $WADbSearch2->whereClause, 1);
$query_limit_WADAProducts = sprintf("%s LIMIT %d, %d", $query_WADAProducts, $startRow_WADAProducts, $maxRows_WADAProducts);
$WADAProducts = mysql_query($query_limit_WADAProducts, $localhost) or die(mysql_error());
$row_WADAProducts = mysql_fetch_assoc($WADAProducts);
if (isset($_GET['totalRows_WADAProducts'])) {
$totalRows_WADAProducts = $_GET['totalRows_WADAProducts'];
} else {
$all_WADAProducts = mysql_query($query_WADAProducts);
$totalRows_WADAProducts = mysql_num_rows($all_WADAProducts);
}
$totalPages_WADAProducts = ceil($totalRows_WADAProducts/$maxRows_WADAProducts)-1;
?>
<?php
$queryString_WADAProducts = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_WADAProducts") == false &&
stristr($param, "totalRows_WADAProducts") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_WADAProducts = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_WADAProducts = sprintf("&totalRows_WADAProducts=%d%s", $totalRows_WADAProducts, $queryString_WADAProducts);
?>
<?php
// WA eCart Redirect
if ($WA_Store_Cart->redirStr != "") {
header("Location: ".$WA_Store_Cart->redirStr);
}
?>