calculate difference between database-rows on output in an easy way?

calculate difference between database-rows on output in an easy way? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: calculate difference between database-rows on output in an easy way? (/showthread.php?tid=27277)

calculate difference between database-rows on output in an easy way? - El Forum - 02-05-2010

[eluser]Richard Schumann[/eluser]
hi, i am richard and realy new to oop - i am addicted and wanna learn like the hell but for this problem what i even dont know how to call it in progammers language - i cant find a nice solution - may someone wanna give me some breakthrough to this ?

let me say i have a table ID(autoinc), VALUE(some numbers like 100,99,77 ...)

CONTROLLER

Code:
$this->db->from('table');

$data['query'] = $this->db->get();

VIEW

Code:
&lt;?php foreach ( $query->result() as $row ): ?&gt;

&lt;?=$row->value?&gt;

&lt;?php endforeach; ?&gt;

how would you best calculate and output the difference from one row to another to have a result like this :

99 +1
98 +28
70 -5
75 -35
40 -

- how to know / trigger next row result in foreach-loop to calc that direct in view ?
- or : how to prepare the array to add another field with the difference in controller?

thanks for your help

calculate difference between database-rows on output in an easy way? - El Forum - 02-06-2010

[eluser]Shay Falador[/eluser]
Well, the database library lets you fetch the results as an array.
So, just walk over it backwards and do the math:

Code:
$result = $query->result();

for($i = count($result) - 1; $i > 0; $i--) {

    $results[$i]['diff'] = $results[$i]['value'] - $results[$i - 1]['value'];

}

I think this one would work!

calculate difference between database-rows on output in an easy way? - El Forum - 02-06-2010

[eluser]Richard Schumann[/eluser]
to bad for me - i did not figure out where to put ? before the for each in view ? or in the controller ?
basicly its logic what u type ...

right now in my controller i have it like this :

Code:
$this->db->from('weight');

$this->db->        where('user_id', $user_id);

$this->db->        order_by("timestamp", "desc");

$data['query'] = $this->db->get();

so as u can see i have just this $data['query'] what i print in view with a foreach end :-(

calculate difference between database-rows on output in an easy way? - El Forum - 02-06-2010

[eluser]Richard Schumann[/eluser]
why you can use it like that

Code:
$result[$i]['diff'] = $result[$i]['diff'] - $result[$i - 1]['diff'];

its not working for me
Fatal error: Cannot use object of type stdClass as array

but i can use it like that

Code:
for($i = count($result) - 1; $i > 0; $i--) 

            {

            $result[$i]->diff = $result[$i]->value - $result[$i - 1]->value;

            }

strange ?

calculate difference between database-rows on output in an easy way? - El Forum - 02-06-2010

[eluser]Shay Falador[/eluser]
No that's not strange at all.
->results() returns an array of stdClass objects while ->results_array() returns an array of arrays.
The code I wrote was incorrect (in this part).

calculate difference between database-rows on output in an easy way? - El Forum - 02-06-2010

[eluser]Richard Schumann[/eluser]
ok i got it !
what was realy helpfull was a print_array debug function to realy see whats going on.

and your tip with result() and result_array() was realy helpfull.

the problem was also to dont have it in a new "result" - instead i wanna use $data .

i had some problems with FIRST or LAST empty array value and because i use often while and not for (dont ask me why)
this is now my solution what i will design a bit like make a + for positive values and make it green... and negative becomes red...

thanks for helping dude

CONTROLLER

Code:
$this->db-> from('weight');

$this->db-> where('user_id', $user_id);

$this->db-> order_by("timestamp", "desc");

$data['query'] = $this->db->get();

$data['result'] = $data['query']->result_array();

$rows = count($data['result']);    $i = 0;

while ($i < $rows-1 )

    {

    $data['result'][$i]['diff']  =  $data['result'][$i]['value'] - $data['result'][$i+1]['value'];   

    $i++;

    }

$data['result'][$i]['diff'] = "";  //fill last array to prevent an error by triggering a not existing arrayvalue...

VIEW

Code:
&lt;?php foreach ( $result as $row ): ?&gt;

<tr>

     <td width="100">&lt;?=mdate("%d.%m.%Y",$row['timestamp'])?&gt;</td>

     <td width="100" class="r">&lt;?=$row['value']?&gt;</td>

     <td width="100" class="r">&lt;?=$row['diff']?&gt;</td>

     <td width="60"  class="r">&lt;?=anchor('weight/edit/'.$row['id'],'edit');?&gt;</td>

</tr>

&lt;?php endforeach; ?&gt;

calculate difference between database-rows on output in an easy way? - El Forum - 02-06-2010

[eluser]Shay Falador[/eluser]
That seems well =]
I would use for instead of while, just for readability but that's not necessary..

Good luck!

calculate difference between database-rows on output in an easy way? - El Forum - 02-06-2010

[eluser]Richard Schumann[/eluser]
yeah thanks. that looks nice now with colors