Output Growth between Numeric Datafields in database

Question

enter image description here

I would like to output the growth percentage between age 20 and age 19. I should be able to say "Height Growth = 3" (By taking the height 178 - 175) or that there was a "Height Growth = .017%" (178/175).

My code looks like this

    <table>
    <thead><tr><td>Age</td><td>Height</td><td>Height Growth %</td><td>Weight</td></tr>    </thead>
    <tbody>
<?php foreach ($healthdata->result_array() as $row) {
       echo "<tr><td>".$row['age']."</td><td>".$row['height']."</td><td>%</td>    <td>".$row['weight']."</td></tr>"; } ?> 
    </tbody></table>

My model looks like this

function display_healthdata()
{
   $healthdata = $this->db->query("select * from healthdata where id = '1' order by age desc;");

   return $healthdata;
}

Essentially I don't know what kind of SQL statement I can do or what code I need to write to be able to make these calculations. I also thought that it may not be possible to do this calculation on the database or in PHP and that I may need to do it on the client side instead of the server side.

Please let me know what other information I can/should add for you to be more helpful.

PROBLEM SOLVED So that people finding this code are helped... The code I used for this issue was the following:

SELECT t1.*,   (t1.height - IFNULL(t2.height, 0)) AS growth
FROM healthdata AS t1
LEFT JOIN healthdata AS t2
ON (t2.age = (t1.age - 1))
where t1.id = '1'
group by age

In my opinion Heght Growth is 1.7%, not 0.017% :) – Karolis Jul 11 '11 at 09:52 — Karolis, Jul 11 '11 at 09:52

score 2 · Accepted Answer · edited May 23 '17 at 12:07

2

You can do it with SQL joins... I'm too tired to actually give a proper answer, but here's a link to a problem that seems similar to yours. =D

How to get difference between two rows for a column field?

EDIT

I'll take a crack at it. NOT guaranteed to not blow up your house; as stated I'm very tired ;)

SELECT t1.*,  (t1.height - IFNULL(t2.height, 0)) AS growth
FROM healthdata AS t1
LEFT JOIN healthdata AS t2
ON (t2.age = (t1.age - 1))

edited May 23 '17 at 12:07

Community

1
1

answered Jul 11 '11 at 09:42

blitzmann

7,319
5
23
29

You rock! That really helped! – Pascal Wagner Jul 11 '11 at 10:11

score 0 · Answer 2 · answered Jul 11 '11 at 09:32

For this you need to insert some logic into your presentation. And I strongly discourage to do it. Anyway

<?php 

     $cache=array();
     foreach ($healthdata->result_array() as $row) {
       echo "<tr>...</tr>"; 

       if ($row['age'] == 19)
         $cache['19'] = $row['height'];
       else if ($row['age'] == 20)
         $cache['20'] = $row['height'];        
     }

     $growth = $cache['20']-$cache['19'];
     echo '<tr><td colspan="4">The growth is '.$growth.'</td></tr>';
?>

Beware here there are 2-3 bad pratice you shouldn't do.

Output Growth between Numeric Datafields in database

2 Answers2