Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PHP Modification

Posted on 2011-09-16
14
Medium Priority
?
284 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 111

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 375 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
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 look for a specific file type in a local or remote server directory using PHP.

670 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