2. PHPRunner General questions
  3. total
This topic is locked

total

 		5/16/2005 12:59:20 PM
PHPRunner General questions
adamdidthis author

I have a list of prices in my table is it possible to create a total field that will add them up?
admin 5/18/2005

Hi,
proceed to Fields order and totalstab in PHPRunner and set Totals type as TOTAL for fields you need.
adamdidthis author 5/24/2005

Ok,
I did this but it doesnt do what i need.
The total filed needs to be a total of the 15 cost fields that i have in the same form
admin 5/25/2005

Hi,
you need to use Calculated Field.

  • proceed to SQL query tab in PHPRunner and check off the Edit SQL query manually box.
  • add your query. For example:
    Select [ID],

    [Field1],

    [Field2],

    Field1 + Field2 as Total

    from [YourTable]


  • build project.

    In Total field you'll get the sum of two fields.
adamdidthis author 5/26/2005

Hi Here is the query i added to the exisiting one, is this correct as i keep getting an error saying some thing colums?

select `ordernum`,

`date`,

`refnum`,

`delivedate`,

`suppname`,

`suppadd1`,

`suppadd2`,

`suppcounty`,

`suppcontry`,

`supppost`,

`suppphone`,

`suppfax`,

`suppweb`,

`suppemail`,

`deliverto`,

`chargeto`,

`quant1`,

`descript1`,

`cost1`,

`quant2`,

`descript2`,

`cost2`,

`delivery`,

`vat`,

`total`,

`quant3`,

`descript3`,

`cost3`,

`quant4`,

`descript4`,

`cost4`,

`quant5`,

`descript5`,

`cost5`,

`quant6`,

`descript6`,

`cost6`,

`quant7`,

`descript7`,

`cost7`,

`quant8`,

`descript8`,

`cost8`,

`quant9`,

`descript9`,

`cost9`,

`quant10`,

`descript10`,

`cost10`,

`quant11`,

`descript11`,

`cost11`,

`quant12`,

`descript12`,

`cost12`,

`qunat13`,

`descript13`,

`cost13`,

`quant14`,

`descript14`,

`cost14`,

`quant15`,

`descript15`,

`cost15`,

`Auth`

From `order`
select total

cost1

cost2

cost3

cost4

cost5

cost6

cost7

cost8

cost9

cost10

cost11

cost12

cost13

cost14

cost15

cost1 + cost2 + cost3 + Cost4 + Cost5 + Cost6 + Cost7 + Cost8 + Cost9 + Cost10 + Cost11 + Cost12 + Cost13 + Cost14 + Cost15 as total

from order
admin 5/26/2005

The correct SQL query should be as follows:

select total,

cost1,

cost2,

cost3,

cost4,

cost5,

cost6,

cost7,

cost8,

cost9,

cost10,

cost11,

cost12,

cost13,

cost14,

cost15,

cost1 + cost2 + cost3 + Cost4 + Cost5 + Cost6 + Cost7 + Cost8 + Cost9 + Cost10 + Cost11 + Cost12 + Cost13 + Cost14 + Cost15 as mytotal

from order
adamdidthis author 5/26/2005

The table is called order, and it contains all of the fields in the code above. If i change the query wont it not build the pages correct? dont I need to run a query to build the pages with alll of the fields and then one to add up the total???
admin 5/26/2005

Hi,
proceed to SQL query tab in PHPRunner and check off the Edit SQL query manually box.

Delete everything in query window and enter this code snippet:

select `ordernum`,

`date`,

`refnum`,

`delivedate`,

`suppname`,

`suppadd1`,

`suppadd2`,

`suppcounty`,

`suppcontry`,

`supppost`,

`suppphone`,

`suppfax`,

`suppweb`,

`suppemail`,

`deliverto`,

`chargeto`,

`quant1`,

`descript1`,

`cost1`,

`quant2`,

`descript2`,

`cost2`,

`delivery`,

`vat`,

`total`,

`quant3`,

`descript3`,

`cost3`,

`quant4`,

`descript4`,

`cost4`,

`quant5`,

`descript5`,

`cost5`,

`quant6`,

`descript6`,

`cost6`,

`quant7`,

`descript7`,

`cost7`,

`quant8`,

`descript8`,

`cost8`,

`quant9`,

`descript9`,

`cost9`,

`quant10`,

`descript10`,

`cost10`,

`quant11`,

`descript11`,

`cost11`,

`quant12`,

`descript12`,

`cost12`,

`qunat13`,

`descript13`,

`cost13`,

`quant14`,

`descript14`,

`cost14`,

`quant15`,

`descript15`,

`cost15`,

`Auth`,

cost1 + cost2 + cost3 + Cost4 + Cost5 + Cost6 + Cost7 + Cost8 + Cost9 + Cost10 + Cost11 + Cost12 + Cost13 + Cost14 + Cost15 as mytotal

From `order`
adamdidthis author 5/26/2005

Hello again,
I also need it to work out the VAT, and have added the following but am geting the column error again

select `ordernum`,

`date`,

`refnum`,

`delivedate`,

`suppname`,

`suppadd1`,

`suppadd2`,

`suppcounty`,

`suppcontry`,

`supppost`,

`suppphone`,

`suppfax`,

`suppweb`,

`suppemail`,

`deliverto`,

`chargeto`,

`quant1`,

`descript1`,

`cost1`,

`quant2`,

`descript2`,

`cost2`,

`delivery`,

`vat`,

`total`,

`quant3`,

`descript3`,

`cost3`,

`quant4`,

`descript4`,

`cost4`,

`quant5`,

`descript5`,

`cost5`,

`quant6`,

`descript6`,

`cost6`,

`quant7`,

`descript7`,

`cost7`,

`quant8`,

`descript8`,

`cost8`,

`quant9`,

`descript9`,

`cost9`,

`quant10`,

`descript10`,

`cost10`,

`quant11`,

`descript11`,

`cost11`,

`quant12`,

`descript12`,

`cost12`,

`qunat13`,

`descript13`,

`cost13`,

`quant14`,

`descript14`,

`cost14`,

`quant15`,

`descript15`,

`cost15`,

`Auth`,

cost1 + cost2 + cost3 + Cost4 + Cost5 + Cost6 + Cost7 + Cost8 + Cost9 + Cost10 + Cost11 + Cost12 + Cost13 + Cost14 + Cost15 + delivery as mysubtotal,

mysubtotal * 0.175 as mytotal

From `order`
adamdidthis author 5/26/2005

In the mean time i have tried the query that will just return the total, that produced an SQL error saying it couldn't find the field mytotal.
Do I need to add the field mytotal to the order table, or do i have to do something else? Sorry I am very new at this and am not much cop at PHP.
Also how do i get the result to show on the forms?
admin 5/26/2005

Hi,
you do not need to add calculated field to database table.
Your SQL query doesn't work because you cannot use calculated field mysubtotal to calculate VAT. Here is the correct query:

select `ordernum`,

`date`,

`refnum`,

`delivedate`,

`suppname`,

`suppadd1`,

`suppadd2`,

`suppcounty`,

`suppcontry`,

`supppost`,

`suppphone`,

`suppfax`,

`suppweb`,

`suppemail`,

`deliverto`,

`chargeto`,

`quant1`,

`descript1`,

`cost1`,

`quant2`,

`descript2`,

`cost2`,

`delivery`,

`vat`,

`total`,

`quant3`,

`descript3`,

`cost3`,

`quant4`,

`descript4`,

`cost4`,

`quant5`,

`descript5`,

`cost5`,

`quant6`,

`descript6`,

`cost6`,

`quant7`,

`descript7`,

`cost7`,

`quant8`,

`descript8`,

`cost8`,

`quant9`,

`descript9`,

`cost9`,

`quant10`,

`descript10`,

`cost10`,

`quant11`,

`descript11`,

`cost11`,

`quant12`,

`descript12`,

`cost12`,

`qunat13`,

`descript13`,

`cost13`,

`quant14`,

`descript14`,

`cost14`,

`quant15`,

`descript15`,

`cost15`,

`Auth`,

cost1 + cost2 + cost3 + Cost4 + Cost5 + Cost6 + Cost7 + Cost8 + Cost9 + Cost10 + Cost11 + Cost12 + Cost13 + Cost14 + Cost15 + delivery as mysubtotal,

(cost1 + cost2 + cost3 + Cost4 + Cost5 + Cost6 + Cost7 + Cost8 + Cost9 + Cost10 + Cost11 + Cost12 + Cost13 + Cost14 + Cost15 + delivery) * 0.175 as mytotal

From `order`
adamdidthis author 5/26/2005

would this produce the same results?

select `ordernum`,

`date`,

`refnum`,

`delivedate`,

`suppname`,

`suppadd1`,

`suppadd2`,

`suppcounty`,

`suppcontry`,

`supppost`,

`suppphone`,

`suppfax`,

`suppweb`,

`suppemail`,

`deliverto`,

`chargeto`,

`quant1`,

`descript1`,

`cost1`,

`quant2`,

`descript2`,

`cost2`,

`delivery`,

`vat`,

`total`,

`quant3`,

`descript3`,

`cost3`,

`quant4`,

`descript4`,

`cost4`,

`quant5`,

`descript5`,

`cost5`,

`quant6`,

`descript6`,

`cost6`,

`quant7`,

`descript7`,

`cost7`,

`quant8`,

`descript8`,

`cost8`,

`quant9`,

`descript9`,

`cost9`,

`quant10`,

`descript10`,

`cost10`,

`quant11`,

`descript11`,

`cost11`,

`quant12`,

`descript12`,

`cost12`,

`qunat13`,

`descript13`,

`cost13`,

`quant14`,

`descript14`,

`cost14`,

`quant15`,

`descript15`,

`cost15`,

`Auth`,

(cost1 + cost2 + cost3 + Cost4 + Cost5 + Cost6 + Cost7 + Cost8 + Cost9 + Cost10 + Cost11 + Cost12 + Cost13 + Cost14 + Cost15 + delivery) * 0.175 as myvat,

((cost1 + cost2 + cost3 + Cost4 + Cost5 + Cost6 + Cost7 + Cost8 + Cost9 + Cost10 + Cost11 + Cost12 + Cost13 + Cost14 + Cost15 + delivery) + ((cost1 + cost2 + cost3 + Cost4 + Cost5 + Cost6 + Cost7 + Cost8 + Cost9 + Cost10 + Cost11 + Cost12 + Cost13 + Cost14 + Cost15 + delivery) * 0.175)) as mytotal

From `order`


So to display my reults I just point the total and vat fields to mytotal and mvat in the functions field?
admin 5/26/2005

Yes, your query will work.
Also you can simplify a little bit:

select ,

(cost1 + cost2 + cost3 + Cost4 + Cost5 + Cost6 + Cost7 + Cost8 + Cost9 + Cost10 + Cost11 + Cost12 + Cost13 + Cost14 + Cost15 + delivery) 0.175 as myvat,

(cost1 + cost2 + cost3 + Cost4 + Cost5 + Cost6 + Cost7 + Cost8 + Cost9 + Cost10 + Cost11 + Cost12 + Cost13 + Cost14 + Cost15 + delivery) * 1.175 as mytotal

From `order`
adamdidthis author 5/26/2005

ok will give that a go, but how do I get it to display the results, I expect i wil have to include the mytotal and myvat fields on the forms? or do i get the origianl total and vat fields to point at them?
adamdidthis author 5/27/2005

Ok,
Have used the above query and it has created to fields called mytotal and myvat. If I put these fields in the forms I get an error saying it can't find the column information for it.
Do I have to make it so the original total and vat fields ahow the results for mytotal and my vat respectivley? If so how do I go about this?
admin 5/30/2005

Hi,
these Calculated fields calculate "on the fly" and don't exist in database. You can display them on the List/View pages or on the Edit page as Read-only type field.
To avoid errors download new PHPRunner 2.0 update here
adamdidthis author 5/30/2005

Ok Tried what you said above and I get the following error:

Technical information

Error type 256

Error description Unknown column 'mytotal' in 'field list'

URL intranet/departments/finance/database/order_edit.phpeditid=2 

Error file C:\Inetpub\wwwroot\newintranet\departments\Finance\Database\include\dbconnection.php

Error line 26

SQL query select `mytotal`, `myvat`, `ordernum`, `date`, `refnum`, `delivedate`, `suppname`, `suppadd1`, `suppadd2`, `suppcounty`, `suppcontry`, `supppost`, `suppphone`, `suppfax`, `suppweb`, `suppemail`, `deliverto`, `chargeto`, `quant1`, `descript1`, `cost1`, `quant2`, `descript2`, `cost2`, `quant3`, `descript3`, `cost3`, `quant4`, `descript4`, `cost4`, `quant5`, `descript5`, `cost5`, `quant6`, `descript6`, `cost6`, `quant7`, `descript7`, `cost7`, `quant8`, `descript8`, `cost8`, `quant9`, `descript9`, `cost9`, `quant10`, `descript10`, `cost10`, `quant11`, `descript11`, `cost11`, `quant12`, `descript12`, `cost12`, `qunat13`, `descript13`, `cost13`, `quant14`, `descript14`, `cost14`, `quant15`, `descript15`, `cost15`, `delivery`, `vat`, `total` from `order` where `ordernum`=2


Neither the mytotal or myvat fileds are in the database and I am using a registered 1version of phprunner 2.0
admin 5/31/2005

Hi,
sorry it's my fault.
Calculated fields work on the List page only now.

In the next PHPRunner update they will work on all other pages.
We plan to release it next week.
500249 6/6/2005

Hi,
Do you know what day you are releasing? And will it be a free upgrade?
adamdidthis author 6/7/2005

As above, any news?
admin 6/14/2005

Hi,
you can download new release of PHPRunner here.
adamdidthis author 6/29/2005

ok I bet you thought this topic was over...but no I have made some changes.
Should this query work?

select *,

(quant1 * unit1) as mycost1,

(quant2 * unit2) as mycost2,

(quant3 * unit3) as mycost3,

(quant4 * unit4) as mycost4,

(quant5 * unit5) as mycost5,

(quant6 * unit6) as mycost6,

(quant7 * unit7) as mycost7,

(quant8 * unit8) as mycost8,

(quant9 * unit9) as mycost9

(quant10 * unit10) as mycost10,

((quant1 * unit1) + (quant2 * unit2) + (quant3 * unit3) + (quant4 * unit4) + (quant5 * unit5) + (quant6 * unit6) + (quant7 * unit7) + (quant8 * unit8) + (quant9 * unit9) + (quant10 * unit10) + delivery) * 0.175 as mvyat,

((quant1 * unit1) + (quant2 * unit2) + (quant3 * unit3) + (quant4 * unit4) + (quant5 * unit5) + (quant6 * unit6) + (quant7 * unit7) + (quant8 * unit8) + (quant9 * unit9) + (quant10 * unit10) + delivery) * 1.175 as mytotal

From `orderform`


It says it cant find the column info.
admin 6/30/2005

Hi,
you forgot to insert comma after this line:
(quant9 * unit9) as mycost9
adamdidthis author 7/6/2005

hurrrrrahh
It has worked an the database is now done.... wel almost.
i have one more thing to do, I need to create an authorisation limit. This means that when an order is signed if the person's limit is £500 they can not approve an order higer than this.
To sign an order the user has to go to a certain part of that databse, log in and the edit an order. They then choose thier name from a drop down list. This is linked to another table and uses the lookup function to recieve its data. The limit column is in this table, is it possible to use the where part of the lookup to do this?