Solved

PHP Modification

Posted on 2011-09-16
14
220 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
Comment Utility
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
Comment Utility
Here is ps4_products and ps4_categories
Thank you soooo much
000094.png
000093.png
0
 
LVL 14

Expert Comment

by:EMB01
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 14

Expert Comment

by:EMB01
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
did not work
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
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…
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 create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now