Page 1 of 1

array_sum Qustion

Posted: Thu Jun 10, 2010 4:56 am
by koolsamule
Hi Chaps,

I'm after a bit of help with summing up some arrays, to build a Quote for a Project.

- Project_ID
- Job_ID (repeated for each Project_ID)
- Task_ID (repeated for each Job_ID)

Each Task_ID has it's own cost, so what I need to do is sum up the Tasks, to give a total for Job and likewise I need to sum up the Job totals to give a Project total.

Hopefully this will give you some idea as to what I have in my database:
Project_1

Job_1 (fk_proj_id=1)

Task_1 (fk_job_id=1)
Task_1_cost = £500

Task_2 (fk_job_id=1)
Task_2_cost = £500

Job 2 (fk_proj_id=1000)

Task_2 (fk_job_id=2)
Task_2_cost = £900
The result I'm after is:
Job_1 = £1000
Job_2 = £900
Project_1 = £1900

Re: array_sum Qustion

Posted: Thu Jun 10, 2010 10:17 am
by AbraCadaver
It's going to be messy if you have actually stored that pound sign in the db. If not, then you are better off using the SQL sum() function:

[text]SELECT sum(cost) from table_name WHERE job_id = 1[/text]

Re: array_sum Qustion

Posted: Thu Jun 10, 2010 10:27 am
by koolsamule
hi, yeah I haven't added the £ symbol to the database. . . .the task_costs are calculated in PHP, due to the vast complexity of the working's out.

So on the PHP page, I've got a loop that looks (in a very simple example) like:

Code: Select all

<table border="0" cellspacing="0" cellpadding="0">
  <tr>
    <th>// Project ID</th>
    <td></td>
  </tr>
<?php do { ?>
  <tr>
    <th>//Job ID</th>
    <td></td>
  </tr>
  <?php do { ?>
  <tr>
    <th>// Task ID</th>
    <td>// Task Cost ($task_cost)</td>
  </tr>
<?php } while ($row_rsTasks = mysql_fetch_assoc($rsTasks)); ?>
  <tr>
    <th></th>
    <td>// Job Total</td>
  </tr>
<?php } while ($row_rsJobs = mysql_fetch_assoc($rsJobs)); ?>
  <tr>
    <th></th>
    <td>// Project Total</td>
  </tr>
</table>
Each task has a calculated cost, what I'm after is a job_total which is a sum of the tasks for that job, then a project total which is a sum of the job_totals for that project . .. .if that makes sense?

Re: array_sum Qustion

Posted: Thu Jun 10, 2010 10:36 am
by AbraCadaver
array_sum() won't help here. It would be simplest to do something like this:

Code: Select all

<?php
$job_cost = 0;
$proj_cost = 0;
do { ?>
  <tr>
    <th>//Job ID</th>
    <td></td>
  </tr>
  <?php do { ?>
  <tr>
    <th>// Task ID</th>
    <td>// Task Cost ($task_cost)</td>
  </tr>
<?php
   $job_cost += $task_cost;
 } while ($row_rsTasks = mysql_fetch_assoc($rsTasks)); ?>
  <tr>
    <th></th>
    <td>// Job Total</td>
  </tr>
<?php
   $proj_cost += $job_cost;
 } while ($row_rsJobs = mysql_fetch_assoc($rsJobs)); ?>

Re: array_sum Qustion

Posted: Fri Jun 11, 2010 3:26 am
by koolsamule
Hi dude,

Thanks for the code, that is a lot simpler, however it doesn't add up correctly.

Simple example of what's happening:

Code: Select all

<table>
<?php 	
$job_cost = 0;
$proj_cost = 0;
?>
  <tr>
    <th>//Job 1</th>
    <td></td>
  </tr>
  <tr>
    <th>// Task 1</th>
    <td><?php $task_cost = 50; echo $task_cost; ?></td>
  </tr>
<?php $job_cost += $task_cost; ?>
  <tr>
    <th>// Task 2</th>
    <td>
      <?php $task_cost = 50; echo $task_cost; ?>
    </td>
  </tr>
<?php $job_cost += $task_cost; ?>
  <tr>
    <th>// Job 1 Total</th>
    <td><?php echo $job_cost;?></td>
  </tr>
    <tr>
    <th>//Job 2</th>
    <td></td>
  </tr>
  <tr>
    <th>// Task 1</th>
    <td><?php $task_cost = 50; echo $task_cost; ?></td>
  </tr>
<?php $job_cost += $task_cost; ?>
  <tr>
    <th>// Task 2</th>
    <td>
      <?php $task_cost = 100; echo $task_cost; ?>
    </td>
  </tr>
<?php $job_cost += $task_cost; ?>
  <tr>
    <th>// Job 2 Total</th>
    <td><?php echo $job_cost;?></td>
  </tr>
<?php $proj_cost += $job_cost; ?>
     <tr>
    <th>// Project Total</th>
    <td><?php echo $proj_cost;?></td>
  </tr>
  </table>
Job 1
- Task 1 = £50
- Task 2 = £50
Job 1 Total = £100

Job 2
- Task 3 = £50
- Task 4 = £100
Job 2 Total = £250

Project Total = £250
So it is adding Job 1 total to Job 2 total (which is the wrong bit), then summing up both totals (which is what it should do) to give the Project total.

Re: array_sum Qustion

Posted: Fri Jun 11, 2010 3:48 am
by koolsamule

Code: Select all

<?php 
  $proj_cost += $job_cost;
  $job_cost = 0;
  } while ($row_rsJob = mysql_fetch_assoc($rsJob)); ?>