PHP Modification

We need to see you database structure to know where the 'category name' attribute is located i.e. what entity it's in, etc.   Please provide that so we can help.

Here is ps4_products and ps4_categories
Thank you soooo much
000094.png
000093.png

Try this, please:

SELECT *, coalesce((select ps4_productoptions.GroupingID
FROM ps4_productoptions
WHERE ps4_productoptions.ProductID = ps4_products.ProductID limit 1),ps4_products.ProductID) AS GroupingID FROM ps4_products WHERE ProductLive = 1 AND (ProductStartDate IS NULL OR ProductStartDate <= CURDATE()) AND (ProductEndDate IS NULL OR ProductEndDate >= CURDATE())

LEFT JOIN ps4_categories ON ps4_productoptions.ProductCategoryID = ps4_categories.CategoryID

GROUP BY GroupingID
ORDER BY ProductName

No points for this, just a couple of comments.

You might enjoy this book:
http://www.sitepoint.com/books/phpmysql4/

Try to avoid SELECT * if you know the names of the columns you really want to SELECT.  Your queries will always be faster.

Best regards, ~Ray

here is the error I received
[19-Sep-2011 14:14:07] PHP Warning:  mysql_free_result(): 106 is not a valid MySQL result resource in /home/ps4/public_html/webassist/plugins/catalog/results_grid.php on line 297
here is the bottom tag
<?php
mysql_free_result($WADAProducts);
if(isset($all_WADAProducts)) mysql_free_result($all_WADAProducts);
if(isset($MaxMinPrice)) mysql_free_result($MaxMinPrice); line 297
if(isset($DefaultProd)) mysql_free_result($DefaultProd);
?>
<?php
$maxRows_WADAProducts = 9;
$pageNum_WADAProducts = 0;
if (isset($_GET['pageNum_WADAProducts'])) {
  $pageNum_WADAProducts = $_GET['pageNum_WADAProducts'];
}
$startRow_WADAProducts = $pageNum_WADAProducts * $maxRows_WADAProducts;

mysql_select_db($database_PowerStoreConnection, $PowerStoreConnection);
$query_WADAProducts = "SELECT *, coalesce((select ps4_productoptions.GroupingID FROM ps4_productoptions WHERE ps4_productoptions.ProductID = ps4_products.ProductID limit 1),ps4_products.ProductID) AS GroupingID FROM ps4_products WHERE ProductLive = 1 AND (ProductStartDate IS NULL OR ProductStartDate <= CURDATE()) AND (ProductEndDate IS NULL OR ProductEndDate >= CURDATE())  LEFT JOIN ps4_categories ON ps4_productoptions.ProductCategoryID = ps4_categories.CategoryID GROUP BY GroupingID ORDER BY ProductName ";
setQueryBuilderSource($query_WADAProducts,$WADbSearch1,false);
$query_limit_WADAProducts = sprintf("%s LIMIT %d, %d", $query_WADAProducts, $startRow_WADAProducts, $maxRows_WADAProducts);
$WADAProducts = mysql_query($query_limit_WADAProducts, $PowerStoreConnection) 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;
?>
Thank you

Not sure what the problem is, but try commenting out that line like so and run and again:

<?php
mysql_free_result($WADAProducts);
if(isset($all_WADAProducts)) mysql_free_result($all_WADAProducts);
//if(isset($MaxMinPrice)) mysql_free_result($MaxMinPrice); line 297
if(isset($DefaultProd)) mysql_free_result($DefaultProd);
?>
<?php
$maxRows_WADAProducts = 9;
$pageNum_WADAProducts = 0;
if (isset($_GET['pageNum_WADAProducts'])) {
  $pageNum_WADAProducts = $_GET['pageNum_WADAProducts'];
}
$startRow_WADAProducts = $pageNum_WADAProducts * $maxRows_WADAProducts;

mysql_select_db($database_PowerStoreConnection, $PowerStoreConnection);
$query_WADAProducts = "SELECT *, coalesce((select ps4_productoptions.GroupingID FROM ps4_productoptions WHERE ps4_productoptions.ProductID = ps4_products.ProductID limit 1),ps4_products.ProductID) AS GroupingID FROM ps4_products WHERE ProductLive = 1 AND (ProductStartDate IS NULL OR ProductStartDate <= CURDATE()) AND (ProductEndDate IS NULL OR ProductEndDate >= CURDATE())  LEFT JOIN ps4_categories ON ps4_productoptions.ProductCategoryID = ps4_categories.CategoryID GROUP BY GroupingID ORDER BY ProductName ";
setQueryBuilderSource($query_WADAProducts,$WADbSearch1,false);
$query_limit_WADAProducts = sprintf("%s LIMIT %d, %d", $query_WADAProducts, $startRow_WADAProducts, $maxRows_WADAProducts);
$WADAProducts = mysql_query($query_limit_WADAProducts, $PowerStoreConnection) 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;
?>

Select allOpen in new window

inserted this:
<?php
mysql_free_result($WADAProducts);
if(isset($all_WADAProducts)) mysql_free_result($all_WADAProducts);
//if(isset($MaxMinPrice)) mysql_free_result($MaxMinPrice); line 297
if(isset($DefaultProd)) mysql_free_result($DefaultProd);
?>
uploaded to http://65.254.54.66/~ps4/products_results.php?pageNum_WADAProducts=20&totalRows_WADAProducts=225&Search=0 I will leave for your to review if you wish

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 'LEFT JOIN ps4_categories ON ps4_productoptions.ProductCategoryID = ps4_categorie' at line 1
code from line 1-7
<?php require_once('../../../Connections/PowerStoreConnection.php'); ?>
<?php require_once("../../../WA_ValidationToolkit/WAVT_Scripts_PHP.php"); ?>
<?php require_once("../../../WA_ValidationToolkit/WAVT_ValidatedForm_PHP.php"); ?>
<?php
//WA Database Search Include
require_once("../../../WADbSearch/HelperPHP.php");
?>
sorrry....

Change your sql to this please:

SELECT *, coalesce((select ps4_productoptions.GroupingID
FROM ps4_productoptions prod
LEFT JOIN ps4_categories cat ON cat.CategoryID = prod.ProductCategoryID
WHERE ps4_productoptions.ProductID = ps4_products.ProductID limit 1),ps4_products.ProductID) AS GroupingID FROM ps4_products WHERE ProductLive = 1 AND (ProductStartDate IS NULL OR ProductStartDate <= CURDATE()) AND (ProductEndDate IS NULL OR ProductEndDate >= CURDATE())



GROUP BY GroupingID
ORDER BY ProductName

It may be a problem with using the asterisk (like Ray said) when trying to access two tables.

okay still getting same error
however when uploading using cute ftp error page says this
[20-Sep-2011 13:47:53] PHP Warning:  mysql_free_result(): 108 is not a valid MySQL result resource in /home/ps4/public_html/webassist/plugins/catalog/results_grid.php on line 297
so here is this code
   <?php if ($pageNum_WADAProducts > 0) { // Show if not first page ?>
            <a href="<?php printf("%s?pageNum_WADAProducts=%d%s", $currentPage, max(0, $pageNum_WADAProducts - 1), $queryString_WADAProducts); ?>">Prev</a>
wow...

ignore the cute ftp that corrected itself however, still having this error
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 'LEFT JOIN ps4_categories ON ps4_productoptions.ProductCategoryID = ps4_categorie' at line 1

Make sure to change the code to what I provided.  Because, 'LEFT JOIN ps4_categories ON ps4_productoptions.ProductCategoryID = ps4_categorie' at line 1' shouldn't exist, it should be replaced with:


LEFT JOIN ps4_categories cat ON cat.CategoryID = prod.ProductCategoryID

So, your whole query should be:

SELECT *, coalesce((select ps4_productoptions.GroupingID
FROM ps4_productoptions prod
LEFT JOIN ps4_categories cat ON cat.CategoryID = prod.ProductCategoryID
WHERE ps4_productoptions.ProductID = ps4_products.ProductID limit 1),ps4_products.ProductID) AS GroupingID FROM ps4_products WHERE ProductLive = 1 AND (ProductStartDate IS NULL OR ProductStartDate <= CURDATE()) AND (ProductEndDate IS NULL OR ProductEndDate >= CURDATE())



GROUP BY GroupingID
ORDER BY ProductName

this is the error i now get
Unknown column 'ps4_productoptions.GroupingID' in 'field list'

did not work