2. PHPRunner General questions
  3. Problem with complex SQL query
This topic is locked

Problem with complex SQL query

 		7/1/2005 8:00:50 AM
PHPRunner General questions
J
 johnww author

I have entered an "Advanced users SQL query" for one of the tables in phprunner V2.
The normal display of this table is correct. However, when I try to Search, I have problems.
The SQL query is:
SELECT

Companyitem_custom.accountingid as AccountingID,

Company__item.display_name as CompanyName,

AddressPostalitem.street as AddressStreet,

AddressPostalitem.city as AddressCity,

AddressPostalitem.state as AddressState,

AddressPostalitem.postcode as AddressPostcode,

AddressPostalitem.country as AddressCountry

FROM (Companyitem_custom INNER JOIN Companyitem ON Companyitem_custom.custom_id = Companyitem.id) LEFT JOIN AddressPostalitem ON Company__item.contact_id = AddressPostalitem.contact_id WHERE ((((AddressPostalitem.primary_record)=1 AND (Company__item_custom.accountingid)>"")))
As you can see, I have added an "as" statement to each field that I am SELECTing. However, I get the following error. For some reason, phprunner is doing something so that the column in the "as" statement is not recognized.
----- Error displayed ---------

PHP error happened

Technical information

Error type 256

Error description Unknown column 'AddressState' in 'where clause'

URL crm.perfectproof.net/dongle/Companyitem_custom_list.phpa=search&value=1&SearchField=4&SearchOption=Equals&SearchFor=NY

Error file /var/www/html/dongle/include/dbconnection.php

Error line 26

SQL query SELECT Companyitem_custom.accountingid as AccountingID, Company__item.display_name as CompanyName, AddressPostalitem.street as AddressStreet, AddressPostalitem.city as AddressCity, AddressPostalitem.state as AddressState, AddressPostalitem.postcode as AddressPostcode, AddressPostalitem.country as AddressCountry FROM (Companyitem_custom INNER JOIN Companyitem ON Companyitem_custom.custom_id = Companyitem.id) LEFT JOIN AddressPostalitem ON Company__item.contact_id = AddressPostalitem.contact_id WHERE ((((AddressPostal__item.primary_record)=1 AND (Company__item_custom.accountingid)>""))) and (`AddressState`=upper('NY'))
J
 		johnww author 7/2/2005

I know the problem is including the column_alias 'AddressState' in a WHERE clause.
When I change the final part of the WHERE clause (the part added by the search terms in phprunner) into a HAVING clause, the query works perfectly:
SELECT Companyitem_custom.accountingid as AccountingID, Company__item.display_name as CompanyName, AddressPostalitem.street as AddressStreet, AddressPostalitem.city as AddressCity, AddressPostalitem.state as AddressState, AddressPostalitem.postcode as AddressPostcode, AddressPostalitem.country as AddressCountry FROM (Companyitem_custom INNER JOIN Companyitem ON Companyitem_custom.custom_id = Companyitem.id) LEFT JOIN AddressPostalitem ON Company__item.contact_id = AddressPostalitem.contact_id WHERE ((((AddressPostal__item.primary_record)=1 AND (Company__item_custom.accountingid)>""))) HAVING (`AddressState`=upper('NY'))
Is there a way to modify the phprunner code so that it begins to use HAVING instead of WHERE?
J
 		johnww author 7/4/2005

I solved the problem. The table in question was called: Company__item_custom
I manually edited the file: include/ Company__item_custom_functions.php
In function AddWhere:
I replaced all (4) instances of " where " with " having "
Everything works fine now.