2. PHPRunner General questions
  3. error on edit form
This topic is locked

error on edit form

 		1/27/2011 23:33:57
PHPRunner General questions
I
 iyan.dk author

hi,
i have problem with "edit form" with my query

(its working well for list/add, inline add/edit)
the error when klik edit buton :
Error description : Invalid use of group function

Error file : /opt/lampp/htdocs/avira/include/dbconnection.php

SQL query : SELECT project.id, project.CUSTOMERS as Customers, project.RESELLER as Reseller, project.LEAD_SOURCE as Lead Source, project.TYPE_OF_BUSINESS as Type of Buss., project.SECTOR as Sector, project.STATUS as Status, project.Product, project.Qty, project.Amount as Amount, MAX(progress.probability) as Prob (%), project.user, project.quarter, MIN(progress.by_user) as Update By FROM project LEFT OUTER JOIN progress ON project.id = progress.id_company WHERE (MIN(progress.by_user)<'28.01.2011 09:37-jaka' or MIN(progress.by_user) IS NULL ) GROUP BY project.id,project.CUSTOMERS,project.RESELLER,project.LEAD_SOURCE,project.TYPE_OF_BUSINESS,project.SECTOR,project.STATUS,project.Product,project.Qty,project.Amount,project.user,project.quarter ORDER BY 14 DESC limit 1
------------------------------------------
here is my query from SQL Designer :
select

project.id,

project.customers as customers,

project.reseller as reseller,

project.lead_source as lead source,

project.type_of_business as type of buss.,

project.sector as sector,

project.status as status,

project.product,

project.qty,

project.amount as amount,

max(progress.probability) as prob (%),

max(progress.by_user) as update by,

project.user,

project.quarter

from project

left outer join progress on project.id = progress.id_company

group by project.id, project.customers, project.reseller, project.lead_source, project.type_of_business, project.sector, project.status, project.product, project.qty, project.amount, project.user, project.quarter
sample data for table progress.by_user = "12.01.2011 23:01-michael"

I used "MAX(progress.by_user) to query latest value and appear it on project listing, look this is the problem?
regards
romaldus 1/28/2011



hi,
i have problem with "edit form" with my query

(its working well for list/add, inline add/edit)
the error when klik edit buton :
Error description : Invalid use of group function

Error file : /opt/lampp/htdocs/avira/include/dbconnection.php

SQL query : SELECT project.id, project.CUSTOMERS as Customers, project.RESELLER as Reseller, project.LEAD_SOURCE as Lead Source, project.TYPE_OF_BUSINESS as Type of Buss., project.SECTOR as Sector, project.STATUS as Status, project.Product, project.Qty, project.Amount as Amount, MAX(progress.probability) as Prob (%), project.user, project.quarter, MIN(progress.by_user) as Update By FROM project LEFT OUTER JOIN progress ON project.id = progress.id_company WHERE (MIN(progress.by_user)<'28.01.2011 09:37-jaka' or MIN(progress.by_user) IS NULL ) GROUP BY project.id,project.CUSTOMERS,project.RESELLER,project.LEAD_SOURCE,project.TYPE_OF_BUSINESS,project.SECTOR,project.STATUS,project.Product,project.Qty,project.Amount,project.user,project.quarter ORDER BY 14 DESC limit 1
------------------------------------------
here is my query from SQL Designer :
select

project.id,

project.customers as customers,

project.reseller as reseller,

project.lead_source as lead source,

project.type_of_business as type of buss.,

project.sector as sector,

project.status as status,

project.product,

project.qty,

project.amount as amount,

max(progress.probability) as prob (%),

max(progress.by_user) as update by,

project.user,

project.quarter

from project

left outer join progress on project.id = progress.id_company

group by project.id, project.customers, project.reseller, project.lead_source, project.type_of_business, project.sector, project.status, project.product, project.qty, project.amount, project.user, project.quarter
sample data for table progress.by_user = "12.01.2011 23:01-michael"

I used "MAX(progress.by_user) to query latest value and appear it on project listing, look this is the problem?
regards


Your query:

group by project.id, project.customers, project.reseller, project.lead_source, project.type_of_business, project.sector, project.status, project.product, project.qty, project.amount, project.`user`, project.quarter


Try this:

group by project.id
I
 		iyan.dk author 1/28/2011

hi Romaldus
thanks for asnwer, still same error : Invalid use of group function
SQL Query Error :

SELECT project.id, project.CUSTOMERS as Customers, project.RESELLER as Reseller, project.LEAD_SOURCE as Lead Source, project.TYPE_OF_BUSINESS as Type of Buss., project.SECTOR as Sector, project.STATUS as Status, project.Product, project.Qty, project.Amount as Amount, MAX(progress.probability) as Prob (%), MAX(progress.by_user) as Progress By, project.user as Add/Upd By, project.quarter FROM project LEFT OUTER JOIN progress ON project.id = progress.id_company WHERE (MAX(progress.by_user)<'28.01.2011 16:18-satrio' or MAX(progress.by_user) IS NULL ) GROUP BY project.id ORDER BY 12 DESC limit 1
[quote name='romaldus' date='28 January 2011 - 12:26 AM' timestamp='1296199615' post='56115']
Sergey Kornilov admin 1/28/2011

IYAN,
just in case - do you use 'Next'/'Prev' buttons on the Edit page?

This feature is not compatible with some complex queries. You may want to turn it off.
I
 		iyan.dk author 1/28/2011

Hi Sergey
i dont need Next/Prev on edit page, how I can turn off it? because error appear since when click edit button
I have try, Edit in popup work well

for this case, I need 2 edit method, edit form (left button) and inline edit, but not successfull till now,
said, I have 2 table relation :

master table : project

detail table : progress
my need is, I want get "latest" record from detail table and show on master table list, Im using MAX(field) but this field isnt numeric,

thats why error when click edit icon or inline add/edit, it work fine with "add form"
I know this error because detail table field (progress.by_user) not a numeric, it contain data eg : 12.01.2011 13:11-michael

look my error : WHERE (MAX(progress.by_user)<'28.01.2011 09:37-jaka'

I have same query with this method, but numeric value, working well
for sample, to get latest data from detail table, said I have 3 record :

12.01.2011 13:11-michael

14.01.2011 08:37-michael

23.01.2011 11:43-suzy
using MAX(Field) will result "23.01.2011 11:43-suzy"

this latest record will show on master list as "Update by" Column
I also try to use subquery, but still work only for LIST
or you have tips for this query?

this EDIT just need edit master table, no need update for detail,

any way using custom query ? maybe use Before SQL Query ? so edit form will use this custom query



IYAN,
just in case - do you use 'Next'/'Prev' buttons on the Edit page?

This feature is not compatible with some complex queries. You may want to turn it off.
Sergey Kornilov admin 1/28/2011

You can turn off Next/Prev buttons on 'Miscellaneous' screen in PHPRunner. That should fix the issue.
I
 		iyan.dk author 1/29/2011

Hi Sergey
thanks for point me for this, edit is working well now, but inline edit still error,

any solution ?



You can turn off Next/Prev buttons on 'Miscellaneous' screen in PHPRunner. That should fix the issue.