Math done to database query.

[synking]

The title may not fit but only thing i could think of.

 

I am pulling data from MSSQL database table.... I need everything from that table but i need to do some basic math funcitons to the resulting data.

 

So the quesiton is should i do the math for this in the SQL query if so how do i pull all fields and do the math in an effcient way.

 

Or should i let php do the math for me after i get all the data i need. 

 

If it is better to use php how can i do this in a while loop and get the math result and not a printed equation.

 

Just looking for a few pointers never had to do math before with php

[Rifts]

pull the records and have php do your math.

[Psycho]

From my perspective it would depend on how elaborate the calculations need to be and what you plan to do with them. If I was doing some simple calculations I'd do it in the query. But, if I was calculating tax, shipping, etc. I'd probably do that in PHP.

[synking]

Yeah all it is .... are a few multiplication of records with static numbers to find out how much of something is used per month and to convert gallons into lbs....

 

the problem i have is that i honestly don't know the proper way to do it where it is efficient and accurate

[Psycho]

Again, it all depends on what you need. There is no one-size-fits-all solution. Just off the top of my head, I would probably do this in the query. Primarily based on the requirement to calculate an amount "per month" would be very easy in a query by utilizing a GROUP BY clause.

[synking]

ok yeah sorry....

 

if this is a query issue whould i post it there....

 

but i will say here i have about 12 records i need to pull 4 of those records i have to multiply the data by 22 for how many working days there are in an average month, then i have to multiply those same fields by 6.98 to convert them from gallons to lbs.  I have tried this in sql query but i don't know enough to get it right i never end up with the same data or results at all i get errors.

 

I have searched but i can't find it.

[Psycho]

The number of records should be irrelevant. Please post the table structure and try to explain the logic accordingly. Why are you using some arbitrary number such as 22 when you should be able to easily calculate the average per month using real data?

[synking]

Well the database was setup by someone about 10 years ago.  This is used for keeping track of chemical emissions used in paint booths.  The cleaning one is the one i am working on now.

 

It is a table that has data input from a paint tech they keep track of just the amount of cleaner they use and when.  Then once a month they will use this script to pull the data.

 

They need to know how much is used per month.. Since the average month has 22 working days i use 22.  They also need to have the result changed from gallons to lbs for complience with the chimical company they buy the supplies from.  So i have to multiple the used gallon amount by 6.98 to get the pound equivalent.

 

The structure is 25 columns of data that keeps track of the different chimicals But they are not always populated. It is calculated when information is input the total amount of the chimicals per gallon is used then when they pull the report i need to do the math for the per months and lbs converstions.

 

Right now i am doing a table display with a while loop that writes the data into a variable and then prints it out later in the script.

 

I am using a generic select all statement from that database.

 

let me know if that makes sense.

[Psycho]

You still haven't given the exact table details (at least the relevant fields). But, you say that " . . . they keep track of just the amount of cleaner they use and when." So, I would assume that the records have a date on them. So, you do not need to use an arbitrary 22 to get results per month as you can get ACTUAL per month values using the query with a GROUP BY using the month.

[synking]

Ok so i have been messing around with the group by but can't seem to make it work

 

The statement i am using is here.

 

"Select
    Manufacturer,
    Description,
    VOCContent,
    Disbursed,
    Recovered,
    Month,
    K001,
    K002,
    UsePerDay,
    TotalVocEmissions,
    TotalK001,
    TotalK002
FROM 
    SolventUsage
Where
    Month > '$month'
and
    Year > '$year'
Group BY
    month, year";
  

 

When it executes i get errors about columns not being contained as aggregate or in the group by.

 

all the columns only contain numbers except for description month and manufacturer.

 

here is an output of the table if i take away the group by.

 

Array (
    [Manufacturer] => SUPERIOR
    [Description] => S-0170 B BLEND
    [VOCContent] => 100
    [Disbursed] => 55
    [Recovered] => 0
    [Month] => October
    [K001] => 0.67
    [K002] => 1.83
    [usePerDay] => 2.5
    [TotalVocEmissions] => 17.45
    [TotalK001] => 4.68
    [TotalK002] => 12.77
    [Year] => 2001
)

 

I thouhgt that error only happens if it expects something to be done with the fields..  Not sure where to go.

[Psycho]

I thouhgt that error only happens if it expects something to be done with the fields..  Not sure where to go.

 

Right, you have a GROUP BY, but you aren't telling it what to DO with the fields being grouped. You will want to do something such as a SUM() on whatever values you want the totals of for the period. Also, I have no idea why you apparently have separate fields for Month and Year. By doing that you cannot use many of the MySQL functions that would make your life easier.

[synking]

Well i did not develop this database... IT is an MSSQL database that was designed for use as a VB6 application.  That application no longer works and that is why i am trying to do this.

 

They had everything imported to a crystal report that displayed the data and did all the formulas.

 

So i am not sure why they have it like that and i am actually once i can migrating it to mysql and going to create a better structure..

 

So to clarify on what i am doing though... So should i do the sum and multiply when i specify the fields i want to grab or after in the group by.

 

Thanks for the help so far.