Solved

PHP Modification

Posted on 2011-09-16
14
265 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
[X]
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
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 110

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

739 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