Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

PHP Modification

Posted on 2011-09-16
14
251 Views
Last Modified: 2012-05-12
I would like to have the category name appear on this page.  
However I need to add a condition to the select statement to include the category name.  I am a newbie to php....
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())
GROUP BY GroupingID
ORDER BY ProductName
0
Comment
Question by:glozinski
  • 7
  • 6
14 Comments
 
LVL 14

Expert Comment

by:EMB01
ID: 36551040
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.
0
 

Author Comment

by:glozinski
ID: 36551342
Here is ps4_products and ps4_categories
Thank you soooo much
000094.png
000093.png
0
 
LVL 14

Expert Comment

by:EMB01
ID: 36551420
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
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36551972
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
0
 

Author Comment

by:glozinski
ID: 36563802
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
0
 
LVL 14

Expert Comment

by:EMB01
ID: 36566718
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;
?>

Open in new window

0
 

Author Comment

by:glozinski
ID: 36568959
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....
0
 
LVL 14

Expert Comment

by:EMB01
ID: 36569054
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.
0
 

Author Comment

by:glozinski
ID: 36569161
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...
0
 

Author Comment

by:glozinski
ID: 36569186
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
0
 
LVL 14

Expert Comment

by:EMB01
ID: 36569372
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
0
 

Author Comment

by:glozinski
ID: 36569920
this is the error i now get
Unknown column 'ps4_productoptions.GroupingID' in 'field list'
0
 
LVL 14

Accepted Solution

by:
EMB01 earned 125 total points
ID: 36570737
Just remove it for now so we can get this working, 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())


ORDER BY ProductName

You may have to change your query to this:

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 prod.GroupingID
ORDER BY prod.ProductName
0
 

Author Closing Comment

by:glozinski
ID: 36918744
did not work
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question