2. PHPRunner General questions
  3. Trouble With $Strwhereclause
This topic is locked

Trouble With $Strwhereclause

 		2/25/2013 12:10:44 PM
PHPRunner General questions
T
 Tim author

Hey everyone,
I'm working with PHPRunner on the Report Page: Before SQL query. I'm putting in a case statement that looks something like this:


switch ($_GET["pg"]) {

case "ICB":

$strWhereClause = whereAdd($strWhereClause,"Products.ProdGrpCode='ICB'");

break;

case "CCB";

$strWhereClause = whereAdd($strWhereClause,"Products.ProdGrpCode='CCB'");

break;

case "EB":

$strWhereClause = whereAdd($strWhereClause,"Products.ProdGrpCode='EB'");

break;

case "PS":

$strWhereClause = whereAdd($strWhereClause,"Products.ProdGrpCode='PS' OR ProdGrp.ProdGrpCode='PS2'");

break;

...

break;


I'm sure that my $_GET is working; I've tested it elsewhere on the page. When I run this, though, I get the following error:



SELECT original.*, `EngProductGroup` as `grp0`, `ProdEngName` as `grp1` FROM (SELECT `ProdGrp`.`ThaiProductGroup`, `ProdGrp`.`EngProductGroup`, `Products`.`ProdEngName`, `Products`.`Picture`, `Products`.`ThaiModel`, `Products`.`EngModel`, `Products`.`ProdWt`, `Products`.`PriceRetail`, `Products`.`PriceWholeSale`, `Products`.`Status`, `Products`.`ProdThaiName`, **`Products`.`ProdGrpCode`**, `ProdGrp`.`ProdGrpCode` as `ProdGrpCode1` FROM Products INNER JOIN ProdGrp ON Products.ProdGrpCode = ProdGrp.ProdGrpCode **WHERE (Status='O') and (Products.ProdGrpCode='CCB'**) ) original**WHERE (Status='O') and (Products.ProdGrpCode='CCB'**) ORDER BY `EngProductGroup` ASC , `ProdEngName` ASC




MySQL said:
#1054 - Unknown column 'Products.ProdGrpCode' in 'where clause'


As you can see there, Products.ProdGrpCode is clearly in my query and is a column in the table. I'm not sure why there are two where statements generated; my base query doesn't have any, and I'm sure I'm not adding it on twice. Does anyone have any insight into this?
Thanks.

Tim
Sergey Kornilov admin 2/25/2013

There is a possibility that Products.ProdGrpCode is only accessible inside a subquery while WHERE clause is applied to the main query.
I recommend to test your queries first applying all possible WHERE clauses before using it in PHPRunner. Another suggestion is to re-write the SQL query avoiding the use of subquery.
T
 		Tim author 2/25/2013

Sorrry, I'm not sure I was clear. My subquery (ie, the query I have on the 'QUERY' page') is



SELECT

ProdGrp.ThaiProductGroup,

ProdGrp.EngProductGroup,

Products.ProdEngName,

Products.Picture,

Products.ThaiModel,

Products.EngModel,

Products.ProdWt,

Products.PriceRetail,

Products.PriceWholeSale,

Products.Status,

Products.ProdThaiName,

Products.ProdGrpCode,

ProdGrp.ProdGrpCode AS ProdGrpCode1

FROM Products

INNER JOIN ProdGrp ON Products.ProdGrpCode = ProdGrp.ProdGrpCode


The Query I would like to run is



SELECT

ProdGrp.ThaiProductGroup,

ProdGrp.EngProductGroup,

Products.ProdEngName,

Products.Picture,

Products.ThaiModel,

Products.EngModel,

Products.ProdWt,

Products.PriceRetail,

Products.PriceWholeSale,

Products.Status,

Products.ProdThaiName,

Products.ProdGrpCode,

ProdGrp.ProdGrpCode AS ProdGrpCode1

FROM Products

INNER JOIN ProdGrp ON Products.ProdGrpCode = ProdGrp.ProdGrpCode

WHERE Status='O' AND Products.ProdGrpCode='PS';


so I would think something like



$strWhereClause = whereAdd($strWhereClause,"Status='O' AND Products.ProdGrpCode='ICB'");


would do it... but that doesn't work, giving me the error written above. Why is it generating two where clauses?
T
 		Tim author 2/25/2013

If I run

$strWhereClause = "Products.ProdGrpCode='ICB'";


then the following query is generated:



SELECT original.*, `EngProductGroup` as `grp0`, `ProdEngName` as `grp1` FROM (SELECT `ProdGrp`.`ThaiProductGroup`, `ProdGrp`.`EngProductGroup`, `Products`.`ProdEngName`, `Products`.`Picture`, `Products`.`ThaiModel`, `Products`.`EngModel`, `Products`.`ProdWt`, `Products`.`PriceRetail`, `Products`.`PriceWholeSale`, `Products`.`Status`, `Products`.`ProdThaiName`, `Products`.`ProdGrpCode`, `ProdGrp`.`ProdGrpCode` as `ProdGrpCode1` FROM Products INNER JOIN ProdGrp ON Products.ProdGrpCode = ProdGrp.ProdGrpCode WHERE Products.ProdGrpCode='ICB' ) original WHERE Products.ProdGrpCode='ICB' ORDER BY `EngProductGroup` ASC , `ProdEngName` ASC


If I could generate the exact same query without the repetitive WHERE Products.ProdGrpCode='ICB' after the word 'original', I'd be fine. I'm not sure why that's being tagged on, and it's screwing things up.
Sergey Kornilov admin 2/26/2013

I guess we need to take a look at your application to understand what is happening. If you have a valid support contract post your application to Demo Account and open a ticket at http://support.xlinesoft.com sending your Demo Account URL. 'Demo Account' button can be found on the last screen in the program.