2. PHPRunner General questions
  3. AddWhere messing up the SQL query
This topic is locked

AddWhere messing up the SQL query

 		5/13/2007 5:41:19 PM
PHPRunner General questions
D
 dani author

Hello All
I'm using a calculated field in a subquery, and the AddWhere function sees the WHERE in the subquery and messes up the syntax. (adds a "(" before the existing WHERE, and adds the final WHERE clause with an AND instead of a WHERE).
So far I've been able to fix it manually, but that would be overwritten by any rebuild.
Is there a way to "isolate" the query so AddWhere doesn't gets confused by a WHERE in a subquery?
Thanks
Dani
J
 		Jane 5/14/2007

Dani,
could you send me URL where I can see this issue? Also I need a step-by-step instruction on reproducing this error.
D
 		dani author 5/14/2007

Lets see...
The manually edited query is:

select `section`,

`item`,

`color`,

`size`,

`ident`,

`description`,

(SELECT sum(increase)-sum(decrease)

FROM (select * from stockmoves where stockmoves.location = "TO") as stockmoves2

WHERE localstock.ident = stockmoves2.product

) AS `TO`,

(SELECT sum(increase)-sum(decrease)

FROM (select * from stockmoves where stockmoves.location = "FL") as stockmoves2

WHERE localstock.ident = stockmoves2.product

) AS `FL`,

`notes`

From `localstock`


and I get the following error message when I click on VIEW:
PHP error happened

Technical information

Error type

256

Error description

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 'AS `FL`, `notes` From `localstock`) and (`ident`='15201+blue+large')' at line 1

URL

www.xxx.com/localstock/localstock_view.php?editid1=15201%2Bblue%2Blarge

Error file

/home/xxx/public_html/localstock/include/dbconnection.php

SQL query

select `section`, `item`, `color`, `size`, `ident`, `description`,

(SELECT sum(increase)-sum(decrease) FROM

(select from stockmoves where stockmoves.location = "TO") as stockmoves2

WHERE localstock.ident = stockmoves2.product ) AS `TO`,

(SELECT sum(increase)-sum(decrease) FROM

(select from stockmoves where stockmoves.location = "FL") as stockmoves2

WHERE**localstock.ident = stockmoves2.product ) AS `FL`, `

notes` From `localstock` [b][color=#FF0000]) and (`ident`='15201+blue+large')
When the AddWhere function gets called, it finds a WHERE already there (in bold) and adds the code marked in red.
Instead I need to get this:
WHERE localstock.ident = stockmoves2.product ) AS `FL`, `

notes` From `localstock`
The way I fixed it was modifying the definition of the AddWhere function, but I would prefer a solution that could stand a rebuild.
Table localstock:

section item color size ident description notes

star 15201 red small 15201+red+small Dress

star 15201 blue large 15201+blue+large Dress

star 15201 red medium 15201+red+medium Dress

star 15201 red large 15201+red+large Dress
Tablestockmoves:**

seq product location date increase decrease notes

4 15201+red+small TO 2007:May:13-15:34 3 0

5 15201+blue+large FL 2007:May:13-15:42 0 5

6 15201+blue+large TO 2007:May:13-16:20 5 0

7 15201+red+small FL 2007:May:13-16:20 0 2 Â
Let me know if you need more info to understand/reproduce the problem.
Thanks
Dani
Alexey admin 5/14/2007

Hi,
I see what you saying.

We'll add more intelligent SQL query analyzer in the next version of PHPRunner.

To get your query working add a dummy WHERE expression to it.

I.e.

select

...

From `localstock` where 1=1
D
 		dani author 5/15/2007

It did the trick.
Thanks!
Dani

Hi,

I see what you saying.

We'll add more intelligent SQL query analyzer in the next version of PHPRunner.

To get your query working add a dummy WHERE expression to it.

I.e.