guest
|
| Posted: 08/18/2002, 9:27 PM |
|
I am using php, mysql, IIS on WinNT. I created a new grid/edit page using
the new page wizard. The search page allows for search on two fields and
passes as parameter to grid page. The grid page uses a custom sql as
follows:
select Name, Address, City, StateOrProvince, PostalCode, Phone,
code_description,
month(policydate) as mth, monthname(policydate) as
mthname,dayofmonth(policydate) as dom,
policy_type
from contacts as a
left outer JOIN contactphone AS c ON a.contactID = c.phoneContactID,
contactaddress as b, contactpolicy as d, codes as e
where a.contactID = b.addressContactID
and a.contactID = d.policyContactID
and d.policy_type = e.codeID
and (c.phone_type = 149 or c.phone_type IS NULL)
order by mth, dom
The url generated is as follows -
grid.php?FormName=Search&FormAction=search&s_policy_type=&s_month=8
I get this error if I enter a month (sql works fine without) - Database
error: Invalid SQL: SELECT Name, Address, City, StateOrProvince, PostalCode,
Phone, code_description, month(policydate) as mth, monthname(policydate) as
mthname,dayofmonth(policydate) as dom, policy_type FROM contacts as a left
outer JOIN contactphone AS c ON a.contactID = c.phoneContactID,
contactaddress as b, contactpolicy as d, codes as e WHERE a.contactID =
b.addressContactID and a.contactID = d.policyContactID and d.policy_type =
e.codeID and (c.phone_type = 149 or c.phone_type IS NULL) AND (mth=8) ORDER
BY mth, dom
MySQL Error: 1054 (Unknown column 'mth' in 'where clause')
Session halted.
I realize this is a mysql error since it can't handle the "mth" field. Just
want to see if anyone has a workaround for this kind of problem.
|
|
|
 |
Chris Seymour
|
| Posted: 08/28/2002, 10:37 AM |
|
Try this:
SELECT Name, Address, City, StateOrProvince, PostalCode,
Phone, code_description, month(policydate) as mth, monthname(policydate)
as
mthname,dayofmonth(policydate) as dom, policy_type
FROM contacts as a left
outer JOIN contactphone AS c ON a.contactID = c.phoneContactID,
contactaddress as b, contactpolicy as d, codes as e WHERE a.contactID =
b.addressContactID and a.contactID = d.policyContactID and d.policy_type =
e.codeID and (c.phone_type = 149 or c.phone_type IS NULL) AND (month
(policydate)=8) ORDER
BY 8,10
Please let me know if it works.
Thanks.
|
|
|
|
guest
|
| Posted: 08/28/2002, 5:31 PM |
|
Thanks for the suggestion. The portion of the where clause that is checking
for month is being passed by the search form as s_month (in the URL) and
built dynamically by codecharge in the source.. In the input definition on
the grid form I specify that s_month should compare to field mth. It is
MySQL that is not able to handle the alias of month(policydate) as mth. I
think my solution may have to be a start date and end date to work. Just
wanted to see if someone could come up with a workaround that doesn't make
MySQL choke.
"Chris Seymour" <cseymour@seyware.com> wrote in message
news:9278823E5cseymourseywarecom@66.180.229.147...
> Try this:
>
> SELECT Name, Address, City, StateOrProvince, PostalCode,
> Phone, code_description, month(policydate) as mth, monthname(policydate)
> as
> mthname,dayofmonth(policydate) as dom, policy_type
> FROM contacts as a left
> outer JOIN contactphone AS c ON a.contactID = c.phoneContactID,
> contactaddress as b, contactpolicy as d, codes as e WHERE a.contactID =
> b.addressContactID and a.contactID = d.policyContactID and d.policy_type =
> e.codeID and (c.phone_type = 149 or c.phone_type IS NULL) AND (month
> (policydate)=8) ORDER
> BY 8,10
>
> Please let me know if it works.
>
> Thanks.
|
|
|
|
guest
|
| Posted: 08/29/2002, 11:24 AM |
|
Here's how I solved the problem.
I added this code in the before event for the form:
$sWhere = str_replace('mth','month(policydate)',$sWhere);
I replace the alias field with what I know works. (BTW - after researching,
I found that the alias fields cannot be used in a Where clause per ANSI SQL
standard, only in having or order by clause). This will be useful for anyone
seeking to display records by month irregardless of year (i.e. policy
renewing next month, birthdays next month or last month, etc.)
<dsafar@cool-offers.com> wrote in message
news:ajps3h$9oo$1@news.codecharge.com...
> I am using php, mysql, IIS on WinNT. I created a new grid/edit page using
> the new page wizard. The search page allows for search on two fields and
> passes as parameter to grid page. The grid page uses a custom sql as
> follows:
>
> select Name, Address, City, StateOrProvince, PostalCode, Phone,
> code_description,
> month(policydate) as mth, monthname(policydate) as
> mthname,dayofmonth(policydate) as dom,
> policy_type
> from contacts as a
> left outer JOIN contactphone AS c ON a.contactID = c.phoneContactID,
> contactaddress as b, contactpolicy as d, codes as e
> where a.contactID = b.addressContactID
> and a.contactID = d.policyContactID
> and d.policy_type = e.codeID
> and (c.phone_type = 149 or c.phone_type IS NULL)
> order by mth, dom
>
> The url generated is as follows -
> grid.php?FormName=Search&FormAction=search&s_policy_type=&s_month=8
>
> I get this error if I enter a month (sql works fine without) - Database
> error: Invalid SQL: SELECT Name, Address, City, StateOrProvince,
PostalCode,
> Phone, code_description, month(policydate) as mth, monthname(policydate)
as
> mthname,dayofmonth(policydate) as dom, policy_type FROM contacts as a left
> outer JOIN contactphone AS c ON a.contactID = c.phoneContactID,
> contactaddress as b, contactpolicy as d, codes as e WHERE a.contactID =
> b.addressContactID and a.contactID = d.policyContactID and d.policy_type =
> e.codeID and (c.phone_type = 149 or c.phone_type IS NULL) AND (mth=8)
ORDER
> BY mth, dom
> MySQL Error: 1054 (Unknown column 'mth' in 'where clause')
> Session halted.
>
> I realize this is a mysql error since it can't handle the "mth" field.
Just
> want to see if anyone has a workaround for this kind of problem.
>
>
|
|
|
|
|
